あるセルの選択内容によって次ぎのセルをクリックした時に表示させるリストボックスの中身を変更したい。

質問

EXCEL VBAについての質問です。
初心者です。宜しくお願い致します。
使用環境はWindows 2000 service pack4
Excel 2000.

＝質問＝
セルA1をクリックするとあるリストボックスを表示させること
の応用です。
セルA1をクリックするとリストボックスが表示させること自体
は以下の文で作動します。（userform1の中にリストボックス
を設定してある。）

Private Sub Worksheet_selectionChange(ByVal Target As
Range)

If Target.Address = "$A:$1" Then

UserForm1.Show
Cancel = True
End if
End sub

この応用として、セルA1で選択された項目（値）によってセルA2
をクリックした時に表示させるリストボックス（ユーザーフォ
ーム）を変更したい。
例えばセルA1の選択が"ABC"でセルA2をクリックするとリスト
ボックス２（ユーザーフォーム２）が、選択が"EFG"の場合は
リストボックス３（ユーザーフォーム３）が表示されるように
したい。
セルA1の選択肢は最大４つでセルB1用に４つのユーザーフォー
ムを準備することは問題ありません。
また、セルA1の選択項目を一旦どこかのセルに飛ばして数値化
しても問題なし。

最初、excelのIndirectとIf関数を使って、作業用シートでA1
を参照し、その項目（値）によって参照するリストを分けよう
と思っていたのですが、セルA1、A2の幅が広ければよかったの
ですが、１つのセル幅を小さく設定しなければならず、その場
合indirect関数で開いたリストは１つの項目に対し2～3文字し
か表示できない状態になりました。
セルA1、A2の幅が小さくてもindirectによって開かれるリスト
の幅が変更できればいいのですが、それは出来ないようです。
Worksheet_selectionChangeにIF文を加えてみたりしたのです
が上手くいきません・・・

海外で仕事をしているので参考文献やVBAに詳しい人に聞く事
ができません。
ご尽力のほど、宜しくお願い致します。

回答No.1

watabe007 · ベストアンサー

>この応用として、セルA1で選択された項目（値）によってセルA2をクリックした時に表示させるリストボックス（ユーザーフォーム）を変更したい。
UserForm1のListBoxのListIndexもしくはリストの値でUserForm2のListBoxの表示データを決めてやれば良いのでは

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　　With Target
　　　　If .Address = "$A$1" Then
　　　　　　With UserForm1
　　　　　　　　.ListBox1.List = Array("abc", "def", "ghi", "jkl")
　　　　　　　　.Show (0)
　　　　　　End With
　　　　ElseIf .Address = "$A$2" Then
　　　　　　With UserForm2
　　　　　　　　Select Case UserForm1.ListBox1.ListIndex
　　　　　　　　　　Case 0
　　　　　　　　　　　　.ListBox1.List = Array("a", "b", "c", "d")
　　　　　　　　　　Case 1
　　　　　　　　　　　　.ListBox1.List = Array("あ", "い", "う", "え")
　　　　　　　　　　Case 2
　　　　　　　　　　　　.ListBox1.List = Array("ア", "イ", "ウ", "エ")
　　　　　　　　　　Case 3
　　　　　　　　　　　　.ListBox1.List = Array("1", "2", "3", "4")
　　　　　　　　End Select
　　　　　　　　.Show (0)
　　　　　　End With
　　　　End If
　　End With
End Sub

お礼

早速の回答ありがとうございます。
Caseの使い方がよくわからなかったので例を記していただき非常に助かります。
アドバイスのプログラムだとA1セルをクリックした際に表示されるリストボックスの選択肢を青く反転したまま、セルA2をクリックしなければリストボックスが空になってしまいますね。
Select Case UserForm1.ListBox1.ListIndexをSelect Case Range("A1")に変更すればセルA1を選択、確定後セルA2をクリックするとセルA1の内容によってリストボックスが変更できるようになったので一応、解決はしました。
ご協力ありがとうございました。
あとは、実際の使用ではセルA1に相当する選択肢が４０個あるのでSelect Case Range("A1")にするとセルの数分同じプログラムを書き込まなくてはなりません。
もうちょっとスマートにできればいいなと思い試行錯誤してみます。
以上、お礼申し上げます。

回答No.5

Be_DaMa

>>プログラムではセルA1の選択肢の設定がないので・・・

選択肢が最大でも４つということなので、どこか空いているセルに
選択肢を入力しておきます。
（例 A10に選択肢1・A11に選択肢2・A13に選択肢4・A14に選択肢4)

次にA1を選択して、「データ」→「入力規則」で表示される画面の
「入力値の種類」を”リスト”に、「元の値」をに選択肢を入力した
セル範囲を指定します。
（この例では、"=$A$10:$A$13"）

これで、A1をクリックするとコンボボックスになり、選択肢がリスト表示されます。
幅が狭いということなので、この方法では選択した値が見づらいですね。。。

A1で選択肢をクリックすると、UserForm1が開き、選んだ選択肢の
リストボックスが表示されるように、Worksheet_SelectionChangeではなくWorksheet_Changeにしていました。

あまりお役に立てなくてすみません＞＜

お礼

いえいえ、とても参考になりました。
小生も最初はIndirect関数を使ってセルの入力制限を行っていました。
プログラムの使用者より設計変更が入り、セルA1、A2の書式を変更するはめになり、セル幅が異常に狭くしなくてはならなくなり、VBAのユーザーフォームによるリストボッスクを利用したいと考えました。
質問に対し迅速な回答ありがとうございました。

回答No.4

watabe007

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　　'選択されたセルが40列目を超えたらExit
　　If Target.Column > 40 Then Exit Sub
　　'選択されたセルが1行目なら
　　If Target.Row = 1 Then
　　　　With UserForm1
　　　　　　.ListBox1.List = Array("ABC", "DEF", "HIJ")
　　　　　　.Show (0)
　　　　End With
　　'選択されたセルが2行目なら
　　ElseIf Target.Row = 2 Then
　　　　With UserForm2
　　　　　　Select Case Target.Offset(-1).Value
　　　　　　　　Case "ABC"
　　　　　　　　　　.ListBox1.List = Array("あ", "い", "う", "え", "お")
　　　　　　　　Case "DEF"
　　　　　　　　　　.ListBox1.List = Array("1", "2", "3")
　　　　　　　　Case "HIJ"
　　　　　　　　　　.ListBox1.List = Array("aa", "bb", "cc", "dd")
　　　　　　End Select
　　　　　　.Show (0)
　　　　End With
　　End If
End Sub

お礼

質問(２）に対しては自力で解決できました。

質問（１）についてもアドバイスのおかげでVBAの行数は多くなるものの行いたいことはできています。
（ただ、さらに応用を利かせた行いことがあったので改めて別の質問として問合せを行うことにします。）

度重なる質問をしてしまい、申し訳ありませんでした。
以上、お礼を申し上げます。

補足

追加のアドバイスはプログラムを簡潔にでき、非常に助かりました。ありがとうございます。
質問ついでで誠に申し訳ないのですが、もしご存知であればあと2点だけお教え願います。

質問（１）
＞'選択されたセルが40列目を超えたらExit
　　If Target.Column > 40 Then Exit Sub
　　'選択されたセルが1行目なら←1列目ならが正解ですね？
　　If Target.Row = 1 Then
　　　　With UserForm1
　　　　　　.ListBox1.List = Array("ABC", "DEF", "HIJ")
　　　　　　.Show (0)
　　　　End With
　　'選択されたセルが2行目なら←2列目ならが正解ですね？
　　ElseIf Target.Row = 2 Then・・・・・

この場合、セルA1（A2)から始まってB1(B2)、C1(C2)、D1(D2)と順にクリックすればリストボックス１、(２)が表示されることは確認しました。
都合上、選択はセルD11から始まり、さらにD11はD11:E11と2列を結合しています。
従って、リストボックス1を表示させるセルは実際にはD11:E11、F11:G11、I11:J11となっています。
これがCD11:CE11まで続きます。
このような場合開始するセル（D11:E11)と次にクリックするセル、終了となるセル（CD11:CE11)を設定することは可能でしょうか？
最初から記載すべきでしたが、リストボックスの表示方法について少しでも分かりやすく説明するために例としてセルA1(A2)としておりました。

質問(２）
リストボックス１（Array("ABC", "DEF", "HIJ")）を表示させるユーザーフォームに「入力」と「クリア」ボタンを設置しUSERFORM1に以下のプログラムを記載しています。
Private Sub CommandButton1_Click()
With Worksheets("Sheet1")
ActiveCell = UserForm1.ListBox1.Value

End With
Unload Me
End Sub

Private Sub CommandButton2_Click()
With Worksheets("Sheet1")
ActiveCell = Clear
End With
Unload Me
End Sub

「クリア」ボタン（CommandButton2）を押した際に1行下のリストボックス2で選択した内容も同時にクリアしたいのですが、その場合ActiveCell = Clearの他にどのようなプログラムを書き足せば良いでしょうか？

重ね重ねの質問で申し訳ありません。
解決方法をご存知であればご教授願います。
以上、宜しくお願い致します。

回答No.3

watabe007

>実際の使用ではセルA1に相当する選択肢が４０個あるのでSelect Case Range("A1")にするとセル・・・
それなら別シート(Sheet2)にリストデーターを書いておけば良いでしょう
Ａ列にリスト１行目に対応するデータ、Ｂ列にリスト２行目に対応・・・

　　　　ElseIf .Address = "$A$2" Then
　　　　　　With UserForm2
　　　　　　　　n = Range("A1").Value + 1
　　　　　　　　'リストが選択されていないとExit
　　　　　　　　If n = 0 Then Exit Sub
　　　　　　　　With Worksheets("Sheet2")
　　　　　　　　　　Set myRang = .Range(.Cells(1, n), .Cells(Rows.Count, n).End(xlUp))
　　　　　　　　End With
　　　　　　　　.ListBox1.List = myRang.Value
　　　　　　　　.Show (0)
　　　　　　End With
　　　　End If
　　End With
End Sub

お礼

アドバイスいただいたことでやりたいことができるようになりました。
例文を記していただくなど丁寧に対応していただき、誠にありがとうございました。
非常に助かりました。
以上、お礼申し上げます。

補足

追加の回答ありがとうございます。
確かにこのプログラムならリストを別シートに分けておくことが可能で後でリストの中身を変更するときなど便利ですね。
ただ、小生の説明が悪かったのでやりたいことと少し違っています。
セルA1をクリックすると"ABC"、"DEF"、"HIJ"と選択肢が表示されいずれかを選択。（ユーザーフォーム1に「入力」ボタンを設定。）
次ぎにセルA2をクリックするとセルA1の選択内容によってユーザーフォーム2が以下のように変化する。
"ABC"を選ぶと"あ"、"い"、"う"、"え"、"お"、
"DEF"を選ぶと"1"、"2"、"3"
"HIJ"を選ぶと"aa"、"bb"、"cc"、"dd"
これは前回アドバイスいただいたプログラムで作成できました。
セルA1、A2はセットみたいなものですが、このセルがB1（B2)、C1(C2)、D1(D2)と４０個あるという意味です。
従って、アドバイスを元に作成したプログラムが以下のようになります。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Address = "A1" Then
With UserForm1
.ListBox1.List = Array("ABC", "DEF","HIJ")
.Show (0)
End With
ElseIf .Address = "A2" Then
With UserForm2
Select Case Range("A1")
Case "ABC"
.ListBox1.List = Array("あ", "い","
う", "え", "お")
Case "DEF"
.ListBox1.List = Array("1,"2","3")
Case "HIJ"
.ListBox1.List = Array("aa", "bb","cc", "dd")

End Select
.Show (0)
End With
End If
End With

With Target
If .Address = "B1" Then
　　　　'"A1"から"B1"に変更。
With UserForm1
.ListBox1.List = Array("ABC", "DEF","HIJ")
.Show (0)
End With
ElseIf .Address = "B2" Then
　　　　'"A2"から"B2"に変更。
With UserForm2
Select Case Range("B1")
'"A1"から"B1"に変更。
Case "ABC"
.ListBox1.List = Array("あ", "い","
う", "え", "お")
Case "DEF"
.ListBox1.List = Array("1,"2","3")
Case "HIJ"
.ListBox1.List = Array("aa", "bb","cc", "dd")

End Select
.Show (0)
End With
End If
End With
以下、With TargetからEnd Withまでを残り38回繰返し記入しセル番号を変更する。

以上のプログラムをスマートにしたいと思っています。
度重なる質問で恐縮ですが、よきアドバイスがあれば回答願います。
一応、上記プログラムでやりたいことはできているので無理にお時間を割いていただかなくても結構です。
宜しくお願い致します。

回答No.2

Be_DaMa

こんな方法はどうでしょうか…
セルA1の選択肢を空いているセルに入力しておいて、
データの入力規則で、「入力値の種類」を"リスト"に、
「元の値」を選択肢を入力したセル範囲に設定します。
セルA1に表示されるリスト（選択肢）から選んだ値によって、
UserForm1のリストボックスに表示させる項目を設定します。
（セルA2はクリックしません）

※Changeイベントにしました
Private Sub Worksheet_Change(ByVal Target As Range)
　If Target.Address = "$A$1" Then
　　Select Case Target.Text
　　　Case 選択肢1
　　　　UserForm1.ListBox1.List = Array("a", "b", "c", "d")
　　　Case 選択肢2
　　　　UserForm1.ListBox1.List = Array("1", "2", "3", "4")
　　　Case 選択肢3
　　　　UserForm1.ListBox1.List = Array("あ", "い", "う", "え")
　　　Case 選択肢4
　　　　UserForm1.ListBox1.List = Array("イ", "ロ", "ハ", "二")
　　End Select
　　UserForm1.Show
　End If
End Sub

お礼

おかげさまでCASE文の使用方法がよくわかりました。
一応、行いたいことができるようになりました。
（しかし、一つ解決するともっと便利にしたいと思ってしまいますね。）

Activecellの一つしたのクリアについては自力で解決できました。

ご協力感謝致します。

ありがとうございました。

補足

回答ありがとうございます。
アドバイスいただいたプログラムではセルA1の選択肢の設定がないのでそれを付け加えると以下のようになるのではないかと思います。
（セルA1も幅が短いためリストボックスによる選択にせざるを得ない。）
Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
With Target
If .Address = "A1" Then
With UserForm1
.ListBox1.List = Array("選択肢1", "選択肢2", "選択肢3")
.Show (0)
End With
End If

If Target.Address = "$A$1" Then
Select Case Target.Text
Case "選択肢1"
UserForm2.ListBox1.List = Array("a", "b", "c","d")
Case "選択肢2"
UserForm2.ListBox1.List = Array("1", "2", "3","4")
Case "選択肢3"
UserForm2.ListBox1.List = Array("あ", "い", "う","え")
End Select
UserForm2.Show
End If

End With
End Sub

これを走らせるとユーザーフォーム1、２が重なって表示されます。
ユーザーフォーム2が最前面に来てしまい、フォームをドラッグして横に避けてやらなければなりません。
それでもユーザーフォーム１による選択肢１～３を選んだらすぐにユーザーフォーム２が表示されるのは便利です。（セルA2をクリックする必要がない。）
他の回答者の方へのお礼にも記しましたが、セルA1の選択肢による内容、セルA2の選択肢による内容を表示させ、それがB1、C1、D1と４０列続きます。
従って、セルA2をクリックしないことは大変助かります。
よきアドバイスがあれば回答のほど宜しくお願い致します。
ちなみにユーザーフォーム1,2には「入力」ボタンと「クリア」を設置して以下のプログラムを書いています。

Private Sub CommandButton3_Click()
With Worksheets("Sheet1")
ActiveCell = Clear

End With
Unload Me
End Sub

Private Sub CommandButton2_Click()
With Worksheets("Sheet1")
ActiveCell = UserForm2.ListBox1.Value

End With
Unload Me
End Sub

入力ボタン（もくはクリアボタン）をクリックするとActivecell（選択肢を表示させるためクリックしたセル）に選択した内容が入力もしくはクリアされるようになっています。
セルA2をクリックしないということはユーザーフォーム２でも同じプログラムを書いた際、Activecellの一つ下のセルに入力しなさい。という命令文を書かなくてはならないと思いますが、それについてもどのように記すべきかご存知であればご教授願います。
以上、宜しくお願い致します。