[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『コンボボックスで3つのリストを連携』（みず）
お世話になります。
使いたいコードがネットにありましたので
自身のEXCELの表に当てはめて
検証していましたら
必要な値が返ってきません。
内容は
コンボボックスで3つのリストを連携させるのですが
ComboBox2（Ｂ列の値）の値によって
ComboBox3に反映したりしなかったりします。
UserForm1にはコンボボックスが3つあるだけです。
サンプルのＢ列の値 "商品1005"を選択すれば
"バーミリオン"が返ってくるのですが
"商品1005"を"1005"（数字のみ）にすると
"1005"は選択できるのですが
その時、ComboBox3は空白になります。
自身のEXCEL表はＢ列に該当するのが数字なので
数字でも反映するように変更したいのです。
MyvalをLongにしたりVariantにしたり
一旦、変数に格納したりするのですが
うまく反映してくれません。
ご教授お願い致します。
以下プログラム
Sub Sample3()
Dim CheckDic As Object
Dim MaxRow As Long
Dim i As Long
Dim n As Long
Dim Myval As String
Dim CtrlInt As Long
With UserForm1
'データを最終行取得する
MaxRow = Cells(Rows.Count, 2).End(xlUp).Row
CtrlInt = 1 'コンボボックス名の末尾の番号
For n = 6 To 8 '項目数3列をループ
Set CheckDic = CreateObject("Scripting.Dictionary") '列ごとにDictionaryを初期化
.Controls("ComboBox" & CtrlInt).Clear 'リストをリセット
For i = 2 To MaxRow '3行目から最終行までループ
Myval = Cells(i, n).Value '該当文字列を格納
If Not CheckDic.exists(Myval) Then 'Dictionaryに登録して重複判定
CheckDic.Add Myval, "" 'Dictionaryに登録
'コンボボックスにリストの文字列を登録
.Controls("ComboBox" & CtrlInt).AddItem Myval
End If
Next i
CtrlInt = CtrlInt + 1 'コンボボックス名の末尾の番号を加算
Set CheckDic = Nothing
Next n
.Show vbModeless 'ユーザーフォームを表示
End With
Set CheckDic = Nothing ' 最後に必ず Nothing を設定する
End Sub
Sub Sample5()
'//2つ目のリストを作成するコード
'//1つ目のリストの条件に一致するリストの作成
Dim CheckDic As Object
Dim MaxRow As Long
Dim i As Long
Dim n As Long
Dim Myval As String
Dim CtrlInt As Long
With UserForm1
'データを最終行取得する
MaxRow = Cells(Rows.Count, 2).End(xlUp).Row
CtrlInt = 2 'コンボボックス名の末尾の番号
For n = 7 To 8 '項目数3列をループ
Set CheckDic = CreateObject("Scripting.Dictionary") '列ごとにDictionaryを初期化
.Controls("ComboBox" & CtrlInt).Clear 'リストをクリア
For i = 2 To MaxRow '3行目から最終行までループ
If .ComboBox1.Value = Cells(i, 6) Then 'コンボボックス1と一致するか判定
Myval = Cells(i, n).Value '該当文字列を格納
If Not CheckDic.exists(Myval) Then 'Dictionaryに登録して重複判定
CheckDic.Add Myval, "" 'Dictionaryに登録
'コンボボックスにリストの文字列を登録
.Controls("ComboBox" & CtrlInt).AddItem Myval
End If
End If
Next i
CtrlInt = CtrlInt + 1 'コンボボックス名の末尾の番号を加算
Set CheckDic = Nothing
Next n
End With
End Sub
Sub Sample6()
'//3つ目のリストを作成するコード
'//1つ目と、2つ目のリストの条件に一致するリストを作成するコード
'下記コードは1つ目が未選択で、2つ目のみが選択された場合は、3つ目の条件に一致しなくなります。
'この現象を回避するには、1つ目のコンボボックスが空白の場合の分岐処理を組み込む必要があります。
Dim CheckDic As Object
Dim MaxRow As Long
Dim i As Long
Dim n As Long
Dim Myval As String
With UserForm1
'データを最終行取得する
MaxRow = Cells(Rows.Count, 2).End(xlUp).Row
Set CheckDic = CreateObject("Scripting.Dictionary") '列ごとにDictionaryを初期化
.ComboBox3.Clear 'リストをクリア
For i = 2 To MaxRow '3行目から最終行までループ
If .ComboBox1.Value = Cells(i, 6) And _
.ComboBox2.Value = Cells(i, 6) Then 'コンボボックス1、2と一致するか判定
Myval = Cells(i, 8).Value '該当文字列を格納
If Not CheckDic.exists(Myval) Then 'Dictionaryに登録して重複判定
CheckDic.Add Myval, "" 'Dictionaryに登録
'コンボボックスにリストの文字列を登録
.ComboBox3.AddItem Myval
End If
End If
Next i
Set CheckDic = Nothing
End With
End Sub
Private Sub ComboBox1_Change()
Call Sample5 End Sub
Private Sub ComboBox2_Change()
Call Sample6 End Sub
データは
一番左のA列は空
Ｂ列 Ｃ列 Ｄ列
Aメーカー 商品1005 バーミリオン
Aメーカー 商品1006 スカーレット
Bメーカー 商品1007 キャロットオレンジ
Bメーカー 商品1008 チャイニーズレッド
Cメーカー 商品1017 バーントシェンナ
Cメーカー 商品1018 アンバーローズ
Dメーカー 商品1019 ベージュローゼ
Dメーカー 商品1020 サーモンピンク
こちらを参考にさせてもらいました
https://officedic.com/excel-vba-howto-combobox-3lists/
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
◆１
＞"商品1005"を"1005"（数字のみ）にすると
ってことなので、数【値】ではなく、数【字】として扱っているということで合ってますか？
（文字列として扱いたいのかの確認）
◆２
表がずれてみづらいのでちょっと修正
(繰り返しになりますが、C列は文字列なんですよね？)
【データ】シートのレイアウト _____B________C_________D________________ 1 製造会社 番号 商品名 2 Aメーカー 1005 バーミリオン 3 Aメーカー 1006 スカーレット 4 Bメーカー 1007 キャロットオレンジ 5 Bメーカー 1008 チャイニーズレッド 6 Cメーカー 1017 バーントシェンナ 7 Cメーカー 1018 アンバーローズ 8 Dメーカー 1019 ベージュローゼ 9 Dメーカー 1020 サーモンピンク
◆３
＞UserForm1にはコンボボックスが3つあるだけです。
とのことですが、
ComboBox1 → 製造会社 ComboBox2 → 番号 ComboBox3 → 商品名
という感じでしょうか？
この場合、商品名（ComboBox3）から製造会社と番号を逆引きすることもあり得るのですか？
無いなら、ComboBox3はラベルで十分なのでは？
（もこな２） 2020/03/04(水) 08:24
気になったのでSub Sample3()だけ
> For n = 6 To 8 '項目数3列をループ から下記の配置で行っています。
F列 G列 H列
Aメーカー 商品1005 バーミリオン Aメーカー 商品1006 スカーレット Bメーカー 商品1007 キャロットオレンジ Bメーカー 商品1008 チャイニーズレッド
Sample3()
Dim CheckDic As Object
Dim MaxRow As Long
Dim i As Long
Dim n As Long
Set CheckDic = CreateObject("Scripting.Dictionary")
With UserForm1
MaxRow = Cells(Rows.Count, 2).End(xlUp).Row 'データを最終行取得する
For n = 6 To 8 '項目数3列をループ
For i = 2 To MaxRow '2行目から最終行までループ
CheckDic(Cells(i, n).Value) = Empty
Next i
'コンボボックスにリストの文字列を登録
.Controls("ComboBox" & n - 5).List = Application.Transpose(CheckDic.keys)
CheckDic.RemoveAll 'CheckDicを初期化
Next n
Set CheckDic = Nothing 'CheckDicを開放
.Show vbModeless 'ユーザーフォームを表示
End With
End Sub
（ピンク） 2020/03/04(水) 08:52
参考に
データーは参照先にあるデータ
(Sheet1)
B列 C列 D列
1 メーカー 商品 カラー
2 Aメーカー 商品1001 コーラルレッド
3 Aメーカー 商品1002 ポピーレッド
4 Aメーカー 商品1003 レッド
5 Aメーカー 商品1004 トマトレッド
6 Aメーカー 商品1005 バーミリオン
7 Aメーカー 商品1006 スカーレット
8 Bメーカー 商品1007 キャロットオレンジ
(標準モジュール)
Sub Sample1()
UserForm1.Show vbModeless
End Sub
(ユーザーフォームモジュール)
Option Explicit
Dim MaxRow As Long
Dim i As Long
Dim myWS As Worksheet
Private Sub UserForm_Initialize()
Dim CheckDic As Object, n As Long
Set myWS = Worksheets("Sheet1") 'データが有るシート(B列～D列)
Set CheckDic = CreateObject("Scripting.Dictionary")
MaxRow = myWS.Cells(Rows.Count, 2).End(xlUp).Row 'データを最終行取得する(Sheet1にデータが有るとしている)
For i = 2 To MaxRow '2行目から最終行までループ
CheckDic(myWS.Cells(i, "B").Value) = Empty
Next i
Me.ComboBox1.List = Application.Transpose(CheckDic.keys) 'ComboBox1 のみリストの文字列を登録
Set CheckDic = Nothing 'CheckDicを初期化
End Sub
Private Sub ComboBox1_Change()
Me.ComboBox2.Clear
Me.ComboBox3.Clear
For i = 2 To MaxRow
If Me.ComboBox1.Value = myWS.Cells(i, "B").Value Then
Me.ComboBox2.AddItem myWS.Cells(i, "C").Value
End If
Next i
End Sub
Private Sub ComboBox2_Change()
Me.ComboBox3.Clear
For i = 2 To MaxRow
If Me.ComboBox1.Value = myWS.Cells(i, "B").Value And _
Me.ComboBox2.Value = myWS.Cells(i, "C").Value Then
Me.ComboBox3.AddItem myWS.Cells(i, "D").Value
End If
Next i
End Sub
（ピンク） 2020/03/04(水) 14:43
コード、改行多すぎて見にくくなってました。
体裁を整えず投稿してしまい
すみません。
ネットのサンプルでは
Ｂ列 Ｃ列 Ｄ列
Aメーカー 商品1005 バーミリオン
ですが、
実際、自分のデータに置き換えると
F列 G列 Ｈ列
製番 追番 材番
9D01752RT2 402 材6 PL38-154x150 SS400
9P03652LN 316 材1 PL12-190x12.5 S50C
9P03652LN 316 材2 PL2.3-65x240 SPCC
となります。
追番のデータが表示されないので
サンプルではどうなるかと思い
"商品1005"の商品を削除してみたら
自分と同じくＢ列の表示に"1005"が表示されませんでした。
自分のデータのセルの書式設定は"標準"です。
ComboBox1 → 製番 ComboBox2 → 追番 ComboBox3 → 材番 3つの条件が揃った行の特定列のセルの値を テキストボックスに表示させる予定です。 （入荷の有無など）
ピンク様のコードで
数字のみでもリストに表示される様になりましたが
ComboBox2が数字のみの選択なら
ComboBox3が表示しません。
以下コード再掲します。
Sub Sample5()
'//2つ目のリストを作成するコード
'//1つ目のリストの条件に一致するリストの作成
Dim CheckDic As Object
Dim MaxRow As Long
Dim i As Long
Dim n As Long
Dim Myval As String
Dim CtrlInt As Long
With UserForm1
MaxRow = Cells(Rows.Count, 2).End(xlUp).Row 'データを最終行取得する
CtrlInt = 2 'コンボボックス名の末尾の番号
For n = 7 To 8 '項目数3列をループ
Set CheckDic = CreateObject("Scripting.Dictionary") '列ごとにDictionaryを初期化
.Controls("ComboBox" & CtrlInt).Clear 'リストをクリア
For i = 2 To MaxRow '3行目から最終行までループ
If .ComboBox1.Value = Cells(i, 6) Then 'コンボボックス1と一致するか判定
Myval = Cells(i, n).Value '該当文字列を格納
If Not CheckDic.exists(Myval) Then 'Dictionaryに登録して重複判定
CheckDic.Add Myval, "" 'Dictionaryに登録
'コンボボックスにリストの文字列を登録
.Controls("ComboBox" & CtrlInt).AddItem Myval
End If
End If
Next i
CtrlInt = CtrlInt + 1 'コンボボックス名の末尾の番号を加算
Set CheckDic = Nothing
Next n
End With
End Sub
Sub Sample6()
'//3つ目のリストを作成するコード
'//1つ目と、2つ目のリストの条件に一致するリストを作成するコード
'下記コードは1つ目が未選択で、2つ目のみが選択された場合は、3つ目の条件に一致しなくなります。
'この現象を回避するには、1つ目のコンボボックスが空白の場合の分岐処理を組み込む必要があります。
Dim CheckDic As Object
Dim MaxRow As Long
Dim i As Long
Dim n As Long
Dim Myval As String
With UserForm1
MaxRow = Cells(Rows.Count, 2).End(xlUp).Row 'データを最終行取得する
Set CheckDic = CreateObject("Scripting.Dictionary") '列ごとにDictionaryを初期化
.ComboBox3.Clear 'リストをクリア
For i = 2 To MaxRow '3行目から最終行までループ
If .ComboBox1.Value = Cells(i, 6) And _
.ComboBox2.Value = Cells(i, 7) Then 'コンボボックス1、2と一致するか判定
Myval = Cells(i, 8).Value '該当文字列を格納
If Not CheckDic.exists(Myval) Then 'Dictionaryに登録して重複判定
CheckDic.Add Myval, "" 'Dictionaryに登録
'コンボボックスにリストの文字列を登録
.ComboBox3.AddItem Myval
End If
End If
Next i
Set CheckDic = Nothing
End With
End Sub
（NP） 2020/03/04(水) 22:34
上の投稿、ニックネーム間違えてました。
すみません
ピンク様のコードで
以下の様に書き換えたら
ComboBox3にリストがでました。
でもなぜなのかがわからないです。
(ユーザーフォームモジュール)
Private Sub ComboBox2_Change()
Me.ComboBox3.Clear
Dim cbText1 As String
Dim cbText2 As String
For i = 2 To MaxRow
cbText1 = Cells(i, "B")
cbText2 = Cells(i, "C")
If Me.ComboBox1.Value = cbText1 And _
Me.ComboBox2.Value = cbText2 Then
Me.ComboBox3.AddItem myWS.Cells(i, "D").Value
End If
Next i
End Sub
（みず） 2020/03/04(水) 22:57
【ユーザーフォームモジュールに記述】
Option Explicit
Dim リスト範囲 As Range
'======================================
Private Sub UserForm_Initialize()
Dim i As Long
Dim objDIC As Object
Set objDIC = CreateObject("Scripting.Dictionary")
With Worksheets("データ").Range("B1").CurrentRegion
Set リスト範囲 = .Offset(1).Resize(.Rows.Count - 1)
End With
On Error Resume Next
For i = 1 To リスト範囲.Rows.Count
objDIC.Add リスト範囲.Cells(i, 1).Value, ""
Next i
On Error GoTo 0
製造会社.List = objDIC.keys
End Sub
'======================================
Private Sub 製造会社_Change()
Dim i As Long
番号.Clear
商品名.Clear
For i = 1 To リスト範囲.Rows.Count
If リスト範囲.Cells(i, 1).Value = 製造会社.Text Then
番号.AddItem リスト範囲.Cells(i, 2).Value
End If
Next i
End Sub
'======================================
Private Sub 番号_Change()
Dim i As Variant
If 番号.Value = "" Then Exit Sub
商品名.Clear
i = Application.Match(番号.Text * 1, リスト範囲.Columns(2), 0)
If IsError(i) Then
商品名.Value = "該当商品なし"
Else
商品名.AddItem リスト範囲(i, 3).Value
商品名.Value = リスト範囲(i, 3).Value
End If
End Sub
'======================================
ポイントは↓です
＞自分のデータのセルの書式設定は"標準"です。
ってことは、1005 とセルに入力した段階で、Excel君は数値だね！と認識しています。
でも、コンボボックスからTextプロパティはいわずもがな、Valueプロパティで取り出した場合でも"文字列"になっちゃってるから、見つからない（リストに追加されない）という状況になっていると思われます。（ざっと流し読みしかしませんでしたので間違ったらごめんなさい）
私の想像どおりであれば、ちゃんとステップ実行していれば、「アレ?!AddItem通ってないぞ」と気づけたと思うので、まだ試していなければ、ご自身で確かめてみてください。
（無視されちゃいましたが、しつこく文字列か数値か聞いていたのはそのためです）
（ちなみに、"1005"と書くと文字列を意味しちゃいますますので、質問するときは気を付けたほうがよいかもです。）
（もこな２） 2020/03/05(木) 00:30
私のコードでいうと
i = Application.Match(番号.Text * 1, リスト範囲.Columns(2), 0) ↓ i = Application.Match(番号.Text , リスト範囲.Columns(2), 0)
とすると、【文字列】の「番号.Text」が見つからず、"該当商品なし"が表示されます。
興味があれば試してみてください。
（もこな２） 2020/03/05(木) 00:38
ステップ実行してなかったです。
文字列の件すみません。（無視してはなかったのですが・・・）
数【値】、数【字】どちらで扱えばいいのかわからず
現状は「標準」でした。
数【値】、数【字】 この辺が問題になってるのは
何となく思っていて
書式設定で文字列に変えたりと色々やってましたが
改善できずでした。
頂いたコードをステップ実行しながら
確認中です。
また報告させて頂きます。
＞ComboBox3はラベルで十分なのでは？
逆引きはしませんラベルでも良いかもしれません。
他に転用する時
選択しをもう一つ増やさないといけないので
ComboBox3をリストボックスに変えようと思います。
その時、3つ目の情報と4つ目の情報を
リストボックスを2列にして表示させたいです。
今、仕掛り中で2列表示まで出来ましたが
表示の行数が増やせません。
また、別タイトルで投稿するかもしれませんが
目に留まったらよろしくお願い致します
（みず） 2020/03/05(木) 15:14
＞その時、3つ目の情報と4つ目の情報をリストボックスを2列にして表示させたいです。
＞今、仕掛り中で2列表示まで出来ましたが表示の行数が増やせません。
たぶん仕様だとおもいます。（自信なし）
https://dekiru.net/article/15392/
わたしの環境（Office365/Windows10）でも、↓のようにすると【番号】は選択時は２列だが、選択後は１列目のみ表示になります。
'======================================
Private Sub 製造会社_Change()
Dim i As Long
番号.Clear
商品名.Clear
番号.ColumnCount = 2
For i = 1 To リスト範囲.Rows.Count
If リスト範囲.Cells(i, 1).Value = 製造会社.Text Then
With 番号
.AddItem
.List(.ListCount - 1, 0) = リスト範囲.Cells(i, 2).Value
.List(.ListCount - 1, 1) = リスト範囲.Cells(i, 3).Value
End With
End If
Next i
End Sub
'======================================
（もこな２ ） 2020/03/05(木) 16:05
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.