索引で指定されたシートのセル範囲を選択するには?

Question 41.3 Previous Next
詳細は こちら ブックの中にメニューワークシートや実績表等の入力可能なワークシートを数ページ作成し、まず、メニューページから索引・目次ページにジャンプさせ、目的のワークシートの範囲指定した個所にジャンプさせ、入力したいのですが、現在「061」マクロを応用させたいとおもっています。よろしくご指導ください。
ちなみに、索引・目次一覧表には約40団体程度の登録があります。そして、ワークシート1枚の中には、登録40団体の入力表が規則的に配列してあります。もちろん集計作業にも使用したいと考えています。
よろしくご指導お願いいたします。
Answer   Copyright (C) 2001.5.12 永井善王
500連発・第2弾・061番は「選択されたセル範囲が表示されるようにウィンドウをスクロールする」と題するサンプルマクロです。[実行]ボタンをクリックすると、ワークシート上のセル範囲を選択して、その範囲の左上端セルをウィンドウの左上端に位置付けします。Excel97以上では、マウスで選択された範囲を検出して位置付けします。
後日情報
500連発第2弾は完売になりました。 が、その後に発売された下記の本にも、同様のマクロが掲載されています。
 ・ Excel VBA そのまま使える実用マクロ500連発 の105番
 ・
Excel VBA 実用マクロ 800連発 [中・上級編] の172番
早速ご質問文から環境を再現してみます。メニューページには、いくつかのボタンがあるのでしょうが、ここでは省略します。
左下のA図が 「索引・目次一覧表には約40団体程度の登録されたシート」 のイメージです。
右下のB図が 「ワークシート1枚の中には、登録40団体の入力表が規則的に配列してあるシート」 のイメージです。
 A図:索引シート      B図:入力表シート
索引シート 入力シート 次にユーザーの操作を再現してみます。

索引ページで「目的のワークシートの範囲指定した個所にジャンプ」とありますが、目的の団体をどのようにして選択するのかが不明です。
「このシートには40団体ほど登録されている」ということなので、オプションボタンかコマンドボタンが40個ほど、準備されているのかも知れません。

しかし、「061番のマクロを応用したいと思っている」とのことですので、団体名のセル(A図のオレンジ色のセル)のどれかをマウスで選択すると、入力表の対応する団体のセル範囲(B図の空色のセル)を選択する方法で考えてみましょう。

冒頭で述べましたが 061番のマクロには、マウスで選択された範囲を検出してウィンドウに位置付ける機能があります。そのコードは次のとおりで、ワークシートのコード画面に記述しています。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With ActiveWindow
        .ScrollRow = Target.Row
        .ScrollColumn = Target.Column
    End With
End Sub
このマクロは、ワークシート上でセル選択範囲を変更したときに発生する SelectionChangeイベントによって実行されます。イベントマクロと呼ばれますが、Excel97以上で利用可能です。
1行目のコードで変数 Targetに新しい選択範囲が取得され、3、4行目で変数 Targetの示すセル範囲の最初の領域の先頭行(Row)と最初の列番号(Column)を、ScrollRowプロパティの上端行または左端列として設定しています。
さて、これをどのように修正すればよいでしょうか。

マクロの流れを考えてみましょう
1.ユーザーがマウスで、索引シートのオレンジ色のセルのどれかを選択するとイベントが発生するので
2.イベントマクロで、選択されたセルの行番号と列番号を取得する
3.取得した値を利用して、同じ行の、1つ多い列番号のセルに入っている値を取得する(入力範囲を表わす文字列)
4.入力表シートをアクティブにしてから、取得した値を使って入力表シートのセル範囲を選択する

修正後のマクロは、次のようになります。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
10  列 = Target.Column                          '選択されたセルの列番号を取得する
20  If 列 = 1 Then                              'もし列番号が1(A列)なら
30      行 = Target.Row                         '選択されたセルの行番号を取得する
40      選択範囲 = Cells(行, 列 + 1)      '選択されたセルの右のセルの値(セル範囲)を取得
50      Worksheets("入力表").Activate           '入力表シートをアクティブにする
60      Worksheets("入力表").Range(選択範囲).Select '入力表シートのセル範囲を選択する
70  Else                                        '(列番号が)それ以外なら
80      MsgBox "団体名の列で選択してください", vbExclamation, "すぐマク" 'メッセージ表示
90  End If
End Sub
上記のマクロの20行目と70から90行目のコードは必ずしも必要ではありませんが、ユーザーが団体名の列以外でマウスをクリックした場合でも、マクロエラーにならないように対策しました。無効の行でクリックした場合の対策も、同様にして行っておいた方が良いでしょう。
60行目のコードも重要です。アクティブシートでセル範囲を選択するコードは、通常
Range("A1:B1").Select のように書くことができます。50行目で入力表シートをアクティブにしていますから、60行目にこのルールを使えると錯覚するかも知れませんが、実行中のマクロが索引シートのコード画面にあるため、Worksheets("入力表").Range(選択範囲).Select とシート名も指定しています。

サンプルブックのダウンロードは ここをクリック
 (YNxv966_Target.xls.xls 49KB)
※ 一旦、ブックをハードディスクに保存し、後で改めて開いてから実行してください。

Excel VBA Macro