リストボックス、コンボボックス、テキストボックスを関連させるには?

Question 45.2 Previous Next
VBAに興味を持ち始めて約1年、諸先輩方の本やホームページを見ながら、休憩時間に少しずつ勉強し、楽しんでいます。しかしながら、まったく無知の状態からはじめたので、諸先輩方のコードをコピーしては、定数や変数など書き換えたり組替えたりして使用しています。
今、入力作業の簡略可をはかるために、入力用のフォームを作成しているのですが、どうもうまくいきません、出来ましたら、ご教授願います。
準備
 ・DB
 ・フォーム内 - リストボックス1
 ・フォーム内 - コンボボックス1、テキストボックス1
 ・シートA~C
作業の流れ
 ・リストボックスに A~C(シートと同名)を挿入
 ・リストボックスの値、例えば Aを選択すると、コンボボックス1にシートAの A列の値を挿入(シートの列は変動する)
 ・コンボボックス1内の値(シートAのA列の値)を選択すると、同行シートAの B列の値をテキストボックス1に挿入

このような、流れでフォームを作成し、値をDBに入力しようと考えました。すごく難しいことをしようとしいるのでしょうか? よろしくお願い致します。 ちなみに、エクセル2000を使用しています。
Answer   Copyright (C) 2001.6.13 永井善王
ややこしいかも知れませんが、むつかしいこととは思いません。
このようなシステムを考える場合には、作業の流れをはっきりさせて、適当に区切って構築していけば必ずできます。その点、あなたは流れを押さえてみえるので、それに沿って、もう少し区切りながら解説を進めます。

1. リストボックスに A~C を挿入する方法

リストボックス 左図のようなリストボックスを表示したいのでしょうか。
A、B、Cと3行表示するだけですから、AddItemメソッドを使ってリストボックスの一覧に行を追加します。
コードの書き方は色々ありますが、簡単な方法を示しておきます。
Private Sub UserForm_Initialize()
    ListBox1.AddItem ("A")
    ListBox1.AddItem ("B")
    ListBox1.AddItem ("C")
End Sub
このイベントプロシージャは、ユーザーフォームが表示されたときに自動的に実行されます。ユーザーフォームのコード画面で作成します。標準モジュールに作成するのでは、ありません。

2. リストボックスで選択された値を取得する方法

ユーザーがリストボックス上でクリックするとイベントが発生するので、選択された値を取得するためのイベントプロシージャを次のように作成します。
Option Explicit
Dim シート名 As String
----------------------------------------
Private Sub ListBox1_Click()
    シート名 = ListBox1.Value
End Sub
変数「シート名」を Dimステートメントにより宣言しています。これは、その値を他のプロシージャでも使いたいからです。
その上の行の Option Explicitステートメントは、モジュール内のすべての変数を明示的に宣言するという意味です。よって、この後に出てくる変数は、必ず宣言しなければなりません。
3. シートの値をコンボボックスのリストに表示する方法

シートA わかりやすくするために、左図を シートA ということにしましょう。このシートの A列の値を、コンボボックスに表示してみます。
マクロを作成するために必要な技術は 2つです。1つ目は、A列の範囲(最下行)を取得する方法、2つ目は、取得した範囲の値をコンボボックスに設定する方法です。

(1) 最下行を取得する方法は、
印刷範囲をR1C1形式の文字列変数で設定するには?」 を見て研究してください。
(2) コンボボックスのリストのソースを設定するには、RowSourceプロパティを用います。
マクロコードは次のとおりで、実行すると右図のように、コンボボックスのリストに値が反映されます。

コンボボックスのリスト
Private Sub シートの値をコンボボックスに表示する()
    Worksheets(シート名).Activate
    Range("A1").CurrentRegion.Select
    With Selection
        行数 = .Rows.Count
        範囲 = .Cells(1, 1).Address & ":" & .Cells(行数, 1).Address
    End With
    ComboBox1.RowSource = シート名 & "!" & 範囲
End Sub
なお、質問文中に (シートのは変動します) とあって気がかりですが、の誤りと解釈して進めました。

4. コンボボックスで選択された行に対応するシートのセルの値をテキストボックスに表示する方法

ユーザーフォーム ユーザーがコンボボックスのリスト上でクリックすると、Clickイベントが発生します。
次のようにイベントプロシージャを作成します。
Private Sub ComboBox1_Click()
    行 = ComboBox1.ListIndex + 1
    TextBox1.Text = Worksheets(シート名).Cells(行, 2)
End Sub
2行目のコードは、コンボボックスのリストの何行目がクリックされたかを知るために、ListIndexプロパティの値を取得しています。これで取得できる行は 0オリジンです。ワークシートの行は 1オリジンですから、1加えておきます。
3行目は、ワークシート B列の、対応する行のセルの値を TextBox1の Textプロパティに設定しています。このコードが実行されると右上図のように、テキストボックスに値が表示されます。

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

Excel VBA Macro