セルに入力された値に対応するコンボボックスを表示したいが?

Question 32.1 Previous Next
はじめまして。Excel VBAマクロ 500連発”の本を見ながらただいまマクロの勉強中をしています。実は最近、エクセルで行き詰まったことがあって、VBAの作成をはじめたのですがどうしてもうまくいきません。 もし、お手すきのときがあるようでしたら教えていただけると助かります。
詳細は こちら マクロ番号:233 コンボボックスでセルに直接入力する

・やりたいことは、「あるセルの"値に対応した”コンボボックスを作ろう」としています。
『A1』のセルは空白(文字を入力)、
『B1』のセルはコンボボックス、
『C1』のセルは『B1』の値に対応したコード(数値)を表示する欄という形になっていて、
『A1』セルに“A”と入力した後、『B1』セルにあるコンボボックスをクリックすると“A-1”という種類のコンボボックスが表示される。
『A2』セルに“B”と入力した後、『B2』セルにあるコンボボックスをクリックすると“B-1”という種類のコンボボックスが表示される。
   ・
   ・
と言う内容のことをやりたくていろいろやってみたのですが、どうやったらいいのかわかりませんでした。
ひょっとしたらアクセスの機能をエクセルに取り込まなければ出来ないような機能なのでしょうか? 知識がないのに、高度なことをやろうとして、お手数な質問をしてしまい申し訳ありませんが、お知恵を貸していただけると助かります。
環境は
・エクセル2000。(office2000 Developer Edition)
・PC : IBM ThinkPad 390E
・エクセルの知識は、ある程度関数が使える程度です。最近VBAを覚え始めました。お忙しいところ申し訳ありませんが、よろしくお願いいたします。
Answer   Copyright (C) 2000.10.5 永井善王
コンボボックスは、ワークシートに作成したり、Excel95以上ではダイアログシートに、Excel97以上ではユーザーフォームに作成することができます。
作成ツールとしては下図の2つがあり、どちらも [表示]メニュー[ツールバー]とクリックして選択・表示します。
 1. フォーム ツールボックス 2. コントロール ツールボックス(Excel97以上)
 
あなたは Excel2000をお使いですから、どちらのコンボボックスも使えます。

ユーザーフォームにコントロールツールボックスのコンボボックスを作成するケースの解説なら、市販本にも多く見かけます。 しかし、ワークシートに作成するケースは、どちらのコンボボックスを使うかにかかわらず稀有で、かつ、マクロの組み方が解説されている本は希少のようです。

マクロ番号:233のサンプルは、フォームツールボックスのコンボボックスをダイアログシートに作成しているので、質問のようにワークシートに作成する場合には、お手本にしにくいでしょう。
よって以下の回答では、ワークシートに両方のコンボボックスを作成してみました。ですが、欲張って混乱してもいけないので、説明は "コントロールツールボックスのコンボボックス"(注) 中心で進めます。
(注) これらは ActiveXコントロール と呼ばれます。

さあ、気合を入れましょう! 結構ややこしいですから、1つ1つ区切りながら理解して、マスターしましょう。
私は、よくわからないことが多かったので、1週間がかりでコツコツ作りました。
(いまだ終了時にマクロエラーが出る場合があります)

質問文の解釈について、最初にお断りしておきます。
1) 「A1セルに入力された値で、コンボボックスに表示するリストの内容を変えたい。A2も同様」と解釈して進めます。つまり、A1セルに A 入力なら コンボボックスに A-1のリストを表示し、B 入力なら B-1のリストを表示すると。
2) C1セルに表示する『B1』の値に対応したコード(数値)は、コンボボックスのリスト用に予め準備されたワークシートから取得することにして進めます。

A. ワークシート(Sheet1)を作る
質問文からワークシートを再現してみました。B列は、らしくするために勝手に想像しました。

B1セルにあるのが“A-1”という種類のコンボボックスでコントロールツールボックスで作りました。

フォームツールボックスのコンボボックスのサンプルを B2セルに作ってみました。ドロップダウンリストが開いている状態です。これは参考までに見てみるだけです。
※ Sheet1は、この段階では作らずに、先にB.を作った後で、C.の段階で作ってください。

B. ワークシート(Sheet2)を作る
コンボボックスのリストに表示するための元データを、下図のとおり作成してみました。A3:B5セルのデータで“A-1”、C3:D5セルのデータで“B-1”という種類のコンボボックスを表示し、選択結果としては B3:B5 または D3:D5セルの値を取得することにします。

B1セルはコンボボックスで選択された結果を格納する「リンクセル」で、下記マクロコードの25行目で指定しています。
D1セルも同様で36行目で指定しています。

C. コンボボックスを作る
1. ワークシート(Sheet1)を表示してから、コントロール ツールボックスを表示する
2. ツールボックスのコンボボックスをクリックすると、カーソルの形が + に変わるので、B1セルに矩形を描く
3. 同様にして B2セルにもコンボボックスを作る(以下の解説では 2つ目のコンボボックスは省略します)

D. マクロを作る
1. B1セルのコンボボックスを右クリックし、表示されたショートカットメニューの[コードの表示]をクリックする
2. コード画面が表示されたら、次のとおり入力するか、コピー貼り付けする
※ 標準モジュールやユーザーフォームのコード画面で作るのではありません。ComboBox1のコード画面で作ります。
Private Sub ComboBox1_GotFocus()       'コンボボックス1がフォーカスを得たときに実行する
10  If Cells(1, 1) = "A" Or Cells(1, 1) = "a" Then 'A1セルの値が Aか aなら
20      ComboBox1.ColumnCount = 2               'リストの列は2とする
21      ComboBox1.TextColumn = 1                'リストには1列目を表示する
22      ComboBox1.ColumnWidths = "150 pt" & ";" & "0"
                                                '1列目:150ポイント、2列目:表示しない
23      ComboBox1.BoundColumn = 2               '選択されたら2列目の値をセットする
24      ComboBox1.ListFillRange = "Sheet2!$A$3:$B$5" 'リストの元データ
25      ComboBox1.LinkedCell = "Sheet2!$B$1"    '選択結果を格納するセル
30  ElseIf Cells(1, 1) = "B" Or Cells(1, 1) = "b" Then 'A1セルの値が Bか bなら
31      ComboBox1.ColumnCount = 2
32      ComboBox1.TextColumn = 1
33      ComboBox1.ColumnWidths = "150 pt" & ";" & "0"
34      ComboBox1.BoundColumn = 2
35      ComboBox1.ListFillRange = "Sheet2!$C$3:$D$5"
36      ComboBox1.LinkedCell = "Sheet2!$D$1"
40  Else                                        'それ以外なら
50      MsgBox "AかB以外はエラー", vbOKOnly, "すぐに役立つエクセルVBAマクロ集"
52      Cells(1, 3) = ""                        'C3セルをクリアする
60  End If
End Sub

Private Sub ComboBox1_Change()              'コンボボックス1の値が変ったときに実行する
70  If Cells(1, 1) = "A" Or Cells(1, 1) = "a" Then
72      Cells(1, 3) = Worksheets("Sheet2").Cells(1, 2) '選択結果を C1セルへ写す
80  Else
82      Cells(1, 3) = Worksheets("Sheet2").Cells(1, 4)
90  End If
End Sub
このマクロは、B1セルのコンボボックス用です。B2セルのコンボボックス用のマクロは、見習って作ってください。

E. ワークシート画面に戻す
1. ツールバーのアイコンをクリックして Excel画面に戻す ・・ 右図

F. 動作確認する
1. Sheet1の A1セルに 「a」または「b」と入力する
2. B1セルのコンボボックスのドロップダウン矢印をクリックする
3. 表示されたリストから、任意の項目をクリックして選択する
4. C1セルにコードが表示されたことを確認する

サンプルブックのダウンロードは ここをクリック  (YNxv98316_ComboBox.xls 57KB)
   ※ 一旦、ブックをハードディスクに保存し、後で改めて開いてから実行してください。
関連ページ: 
コンボボックスの選択結果からまとめた情報を縦方向のセルに表示したいが

Excel VBA Macro