VBA 動的コンボボックスの作成

お疲れ様です。
Takkoです。

VBAである列にデータが入力されたら動的にコンボボックスに
そのデータを配列として収めたいです。
今のところ
「列にデータ挿入した」をトリガーとしてコンボボックスに配列を挿入する方法がわかっておらず、仮作成の段階になっています。

また、列データを取得する動作はできているのですが、
それをコンボボックスのオブジェクトへの挿入ができていません。

フォーム上のオブジェクトは
=EMBED("Forms.ComboBox.1","")
となっていて、

実装は下記です。

VBA
1
2Private Sub WorkSheet_Change(ByVal Target As Range) 'WorkSheet変更時のイベントを拾いません。
3    If Target.Row >= 6 And Target.Column = 3 Then
4        Call UserForm_Initialize
5    End If
6End Sub
7
8Private Sub UserForm_Initialize()
9    For i = 6 To rowsCount
10        ComboBox1.AddItem Cells(i, 3).Value 'Activexで作成したコントロールですが、オブジェクトが必要ですと表示されます。
11    Next i
12End Sub

実データとしては、C6セルからデータ入力され、
C6～CXXXXまでの行にデータが入っています。

知りたいことを纏めますと、下記二点です。

1．
「セルにデータを入れた」というアクションをトリガーとして、
プロシージャを呼び出し、UserForm_Initialize()を呼び出す方法。

2．
コンボボックスのオブジェクトに選択枝を入れ込む方法

以上、よろしくお願いします。

行動規範の内容に同意します

回答2件

もし、コンボボックスが該当列データのリスト、というのであれば、
名前の定義を使えばプログラムで処理しなくても対応できますね。

名前の定義、動的、で検索すればサンプルが出てくると思います。
（参照範囲に例えば=Offset(A1,0,0,Counta(A:A))

参考まで。

投稿2017/10/13 05:02

ExcelVBAer

総合スコア1175

ベストアンサー

まず確認したい点が２点あります。

確認１：
＞フォーム上のオブジェクトは =EMBED("Forms.ComboBox.1","") となっていて
とのことですが、コンボボックスを貼り付けたのはユーザーフォームですか？
=EMBED("Forms.ComboBox.1","")という記述があることから、シート上に貼り付けたのではないかと思うのですが、どうでしょうか？

確認２：
コンボボックスに値を追加する関数名がUserForm_Initialize()となっていますが、これはユーザーフォームの初期化イベントでしょうか？

確認１とも関連しますが、シート上に貼り付けたコンボボックスを操作するのならそのシートの関数内でしかオブジェクトを見つけられません。
※もしユーザーフォームの初期化時にシート上のコンボボックスを制御したいのなら、Sheet1.ComboBox1のようにシートを明示する必要があります。
コード中のコメントにオブジェクトが認識できていないような記載がされているのは、このためではないかと思います。

例えばSheet１に設置したコンボボックスを制御するのなら、Sheet1シートの関数として実装しましょう。
設置したシート外から制御する場合は、対象シートを明示しましょう。

①「セルにデータを入れた」というアクションをトリガー

続いて知りたいこととして記載いただいた２点について。
セル値を変更を受けてイベント処理する方法は、提示いただいたコードのWorksheet_Change関数で動作するはずです。

この記述をしていて、セル変更時に処理されていないのであれば、
(1)コードの実装箇所が対象のシートでない。
(2)コンボボックスコントロールを貼り付けた時にデザインモードのままになっている
といったあたりが可能性として考えられます。

②コンボボックスのオブジェクトに選択枝を入れ込む方法

コンボボックスへの値の追加は、コンボボックスを設置したシートの関数として記述しているのならComboBox1.AddItem Cells(i, 3).Valueという記述で可能です。
他シートや標準モジュール、ユーザーフォームから操作する場合は、前述のとおり設置したシートも明示する必要があります。

ただ記述いただいたコードでは値の追加しかしていないため、セルの値を変更するたびに選択候補が増殖していってしまいます。
選択候補を追加する前にコンボボックスをクリアする必要があると思います。

サンプル

以下は上記を踏まえたサンプルコードです。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row >= 6 And Target.Column = 3 Then
        Call MakeCombo()
    End If
End Sub

Private Sub MakeCombo()
    Dim iRow As Integer
    Dim rowsCount As Integer
    
    rowsCount = 100         '暫定で100行としています。
    
    'コンボボックスの初期化
    Me.ComboBox1.Clear
    
    For iRow = 6 To rowsCount
        'セルの値が空でなければコンボに追加（これ以外にも重複チェックなどもあった方がいいかもしれません。）
        If Cells(iRow, 3).Value <> "" Then
            ComboBox1.AddItem Cells(iRow, 3).Value
        End If
    Next
End Sub

関数名を変えた程度でほとんど提示いただいた内容と同じですが、全てコンボボックスを設置したシートの関数として記述する、というところが注意点です。

参考になれば幸いです。

投稿2017/10/12 09:55