質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.51%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

3回答

1882閲覧

コンボボックスのリストの動的配列

omotti

総合スコア14

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2018/05/14 04:01

編集2018/05/14 05:24

前提・実現したいこと

ユーザーフォームのコンボボックスのリスト用の一覧があります。
A列が市町村、B列がそのグループです。
シート名は「一覧」です。
グループは、市町村がそのままの場合もあります。

現状ではarray関数を使い、グループ名をそのまま入力していますが
増えた場合に、一覧に追記していくだけで対応できるコードを教えてください。

追記の方法は、できれば、最終行の下に入力するのではなく、
例えば東京グループが増えた場合、「町田」の下に挿入する形をとりたいです。

ご教授宜しくお願い致します。
(動的配列でなければすみません。良く分かっておりません。)

イメージ説明

該当のソースコード

Private Sub myComboBox_Change() Call 作成(myForm.myComboBox.Text) End Sub Private Sub UserForm_Initialize() Dim myDic As Object myForm.myComboBox.List = _ Array("東京", "横浜", "川崎", "相模原", "千葉") End Sub 標準モジュールに、リストから選んだグループをもとに マクロを組んでいます。

補足情報(FW/ツールのバージョンなど)

http://www.saka-en.com/office/vba-find-cells-excel/
こちらを参考に、FINDメソッドを使いB列とA列を連携させています。
コードはほぼこちらと同じです。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

m.ts10806

2018/05/14 04:58

B列のコンボを選んだら対応するリストがA列から引っ張ってこられる みたいな連携があるのでしょうか?B列の項目が増えても動的にコンボ出したいということですよね?
omotti

2018/05/14 05:25

補足を追記いたしました。宜しくお願い致します。その通りです。B列が増えた場合に、自動で対応できるようにしたいです。
guest

回答3

0

ベストアンサー

仕様

下記のような仕様とします。

シート「一覧」にA列に市町村名、B列に地域グループ名が入力されている。

ユーザーフォーム上にコンボボックスが2つ配置してある
cbArea
cbCity

cbArea のリストは、「一覧」の地域グループ名から重複を排除したもの
cbCity のリストは、cbAreaで選択された地域グループ内の市町村名

いろいろな方法があると思いますが、Dictionary を使ったサンプルコード

ユーザーフォームのモジュール

VBA

1Option Explicit 2 3Private dicList As Object 4 5Private Sub UserForm_Initialize() 6 Dim aryList() As Variant 7 Dim i As Long, stArea As String 8 9 'Dictionary生成 10 Set dicList = CreateObject("Scripting.Dictionary") 11 12 'シート「一覧」のリストを配列に格納 13 aryList = Worksheets("一覧").Range("A1").CurrentRegion.Value 14 15 'dicListの Keyに 地域グループ、Item に市町村名をカンマ区切りで格納 16 For i = 1 To UBound(aryList) 17 stArea = aryList(i, 2) 18 If dicList.Exists(stArea) Then 19 dicList(stArea) = dicList(stArea) & "," & aryList(i, 1) 20 Else 21 dicList(stArea) = aryList(i, 1) 22 End If 23 Next 24 25 'keys(地域グループの配列)をコンボボックスcbAreaのListに設定 26 Me.cbArea.List = dicList.keys 27End Sub 28 29Private Sub cbArea_Change() 30 Me.cbCity.Text = "" 31 'dicListより選択した地域に対応するItem(カンマ区切りの市町村リスト)を 32 '取得して、Splitで配列にして cbCity.List に設定 33 Me.cbCity.List = Split(dicList(Me.cbArea.Text), ",") 34End Sub 35 36Private Sub UserForm_Terminate() 37 Set dicList = Nothing 38End Sub

投稿2018/05/14 14:01

編集2018/05/14 14:18
hatena19

総合スコア33601

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

omotti

2018/05/23 05:18

ご連絡遅くなり申し訳ございません。 無事、作ることができました。 誠にありがとうございます。
guest

0

自分は、名前の定義で、動的に範囲が変わるように数式を組む方法をとっています。

詳細は自分で調べてください(OFFSET + MATCH + COUNTA 等)

投稿2018/05/14 04:09

ExcelVBAer

総合スコア1175

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

omotti

2018/05/23 05:19

ご回答ありがとうございます。 参考にさせて頂きます。
guest

0

思ったよりコード肥大化してしまったので、もう少しすっきり書けると思うのですが、
特定範囲の変化を監視して、そのたびにコンボボックスをクリアして最新化するのと、
連動させた情報を取得してそこだけのコンボにするにはBOX1で持っておいた値から引っ張ってくれば良いかなと。

名前を分かりやすくするために下記でネーミングしました。
コンボ1:AreaGroupCombo
コンボ2:CityCombo

起動時にコンボボックス初期化を呼び出す
ThisWorkbook

vba

1Private Sub Workbook_Open() 2 Call Comb_Update 3End Sub

あとは処理をグダグダと・・・

vba

1Dim AreaGroupComboList As Object 2' コンボボックス初期化 3Sub Comb_Update() 4 Dim cnt As Integer 5 Dim break As Boolean 6 Dim Target As String 7 8 ' コンボ初期化 9 With AreaGroupCombo 10 .Clear 11 End With 12 With CityCombo 13 .Clear 14 End With 15 16 Set AreaGroupComboList = CreateObject("Scripting.Dictionary") 17 18 cnt = 1 19 break = False 20 21 Do While break = False 22 If Range("B" & cnt).Value = "" Then 23 break = True 24 Else 25 Target = Range("B" & cnt).Value 26 ' 同じグループ名が既にある場合はコンボに追加しない 27 If AreaGroupComboList.Exists(Target) = False Then 28 AreaGroupComboList.Add Target, Target 29 AreaGroupCombo.AddItem Target 30 End If 31 End If 32 cnt = cnt + 1 33 Loop 34End Sub 35 36' グループ変更時 37Private Sub AreaGroupCombo_Change() 38 Dim cnt As Integer 39 Dim break As Boolean 40 Dim AreaName As String 41 42 ' 市コンボ初期化 43 With CityCombo 44 .Clear 45 End With 46 47 If AreaGroupCombo.ListIndex <> -1 Then 48 ' 現在選択されている項目名を取得する 49 AreaName = AreaGroupCombo.List(AreaGroupCombo.ListIndex) 50 51 cnt = 1 52 break = False 53 54 ' A列よりB列値に対応する情報のみ市コンボに追加 55 Do While break = False 56 If Range("A" & cnt).Value = "" Then 57 break = True 58 Else 59 If Range("B" & cnt).Value = AreaName Then 60 CityCombo.AddItem Range("A" & cnt).Value 61 End If 62 End If 63 cnt = cnt + 1 64 Loop 65 End If 66End Sub 67' リストの変化を監視 68Private Sub Worksheet_SelectionChange(ByVal Target As Range) 69 If Intersect(Target, Range("A999:B999")) Is Nothing Then ' 適当に999とか入れているのでここは要件次第で調整してください 70 Call Comb_Update 71 Else 72 Exit Sub 73 End If 74End Sub 75

これで、どこに入れてもその時の最新の状態でコンボボックスができあがるはず。
Dictionaryは個人的に使いやすいためなので、「同じ情報があればスルー」みたいなのができる配列要素であれば何でも構いません。

投稿2018/05/14 06:43

編集2018/05/14 06:50
m.ts10806

総合スコア80731

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

omotti

2018/05/23 05:19

ご回答ありがとうございます。 参考にさせて頂きます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.51%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問