none
動的に追加したコンボボックスのChangeイベント RRS feed

  • 質問

  • Excel2013で開発を行っています。

    シート上にあるボタンをクリックするとDB(sqlserver)に接続し

    数行分のデータを取得し、シートに展開する処理を行っています。

    データ展開時にActiveXのコンボボックスをシート上にデータ行分追加しています。(ComboBox1、ComboBox2・・・)

    追加されたコンボボックスの値を変更した場合、選択値に応じた値を隣のセルに表示したいのですが

    動的に追加された複数のコンボボックスのChangeイベントを一つにまとめるやり方が分かりません。

    ユーザーフォーム上のコンボボックスなら書いてあったのですが…

    どなたかアドバイスをお願いします。

    2017年4月10日 1:25

回答

  • ActiveX コントロールの動的追加は、セキュリティ上の制限を受けそうですが、そこは既にクリアされているようですね。

    > 動的に追加された複数のコンボボックスのChangeイベントを一つにまとめるやり方が分かりません。

    ご存知とは思いますが、イベントの受信は「WithEvents」な変数で行えます。

    'Private WithEvents ComboBox1 As MSForms.ComboBox
    
    Dim oo As Excel.OLEObject
    Set oo = (動的に追加したComboBox)
    
    ’これで「Private Sub ComboBox1_Change()」が通知されるようになる
    Set ComboBox1 = oo.Object
        
    ComboBox1.Clear
    ComboBox1.AddItem "AAA"
    ComboBox1.AddItem "BBB"
    ComboBox1.AddItem "CCC"

    しかし、WithEvents な変数は配列にできません。そのため、複数のコントロールをまとめるのであれば、あらかじめ多めに WithEvents 変数を用意しておき、それを用いるといった手段になってしまいます。

    コレを避けるには、WithEvents 変数 1 つを備えた Class モジュールを用意しておき、その Class のインスタンスを複数保持することで代用するといった手法が利用できます。

    '==== Module1 ====
    Option Explicit
    Public Sub ComboBox_Change(ByVal combo As ComboBox)
        'Dim sheet As Excel.Worksheet
        'Set sheet = combo.Parent
        
        Dim parentCell As Excel.Range
        Set parentCell = combo.TopLeftCell
        
        '右隣のセルを取得
        Dim rightCell As Excel.Range
        Set rightCell = parentCell.Offset(0, 1)
        
        If combo.ListIndex < 0 Then
            rightCell.Value = Empty
        Else
            rightCell.Value = combo.List(combo.ListIndex)
        End If
    End Sub
    
    
    '==== Class1 ====
    Option Explicit
    Private WithEvents Target As MSFORMS.ComboBox
    Public Sub SetCtrl(ByVal ctrl As MSFORMS.ComboBox)
        Set Target = ctrl
    End Sub
    Private Sub Target_Change()
        Call Module1.ComboBox_Change(Target)
    End Sub
    
    
    '==== Worksheet ====
    Option Explicit
    Private ComboboxEvents As VBA.Collection
    
    Public Sub SetEvents()
        Set ComboboxEvents = New VBA.Collection
        Dim o As Excel.OLEObject
        For Each o In Me.OLEObjects
            If TypeOf o.Object Is MSFORMS.ComboBox Then
                Dim e As Class1
                Set e = New Class1
                e.SetCtrl o.Object
                ComboboxEvents.Add e
            End If
        Next
    End Sub
    • 回答としてマーク Dreizehn 2017年4月12日 8:57
    • 回答としてマークされていない Dreizehn 2017年4月12日 8:57
    • 回答としてマーク Dreizehn 2017年4月12日 8:58
    2017年4月10日 7:45

すべての返信

  • ActiveX コントロールの動的追加は、セキュリティ上の制限を受けそうですが、そこは既にクリアされているようですね。

    > 動的に追加された複数のコンボボックスのChangeイベントを一つにまとめるやり方が分かりません。

    ご存知とは思いますが、イベントの受信は「WithEvents」な変数で行えます。

    'Private WithEvents ComboBox1 As MSForms.ComboBox
    
    Dim oo As Excel.OLEObject
    Set oo = (動的に追加したComboBox)
    
    ’これで「Private Sub ComboBox1_Change()」が通知されるようになる
    Set ComboBox1 = oo.Object
        
    ComboBox1.Clear
    ComboBox1.AddItem "AAA"
    ComboBox1.AddItem "BBB"
    ComboBox1.AddItem "CCC"

    しかし、WithEvents な変数は配列にできません。そのため、複数のコントロールをまとめるのであれば、あらかじめ多めに WithEvents 変数を用意しておき、それを用いるといった手段になってしまいます。

    コレを避けるには、WithEvents 変数 1 つを備えた Class モジュールを用意しておき、その Class のインスタンスを複数保持することで代用するといった手法が利用できます。

    '==== Module1 ====
    Option Explicit
    Public Sub ComboBox_Change(ByVal combo As ComboBox)
        'Dim sheet As Excel.Worksheet
        'Set sheet = combo.Parent
        
        Dim parentCell As Excel.Range
        Set parentCell = combo.TopLeftCell
        
        '右隣のセルを取得
        Dim rightCell As Excel.Range
        Set rightCell = parentCell.Offset(0, 1)
        
        If combo.ListIndex < 0 Then
            rightCell.Value = Empty
        Else
            rightCell.Value = combo.List(combo.ListIndex)
        End If
    End Sub
    
    
    '==== Class1 ====
    Option Explicit
    Private WithEvents Target As MSFORMS.ComboBox
    Public Sub SetCtrl(ByVal ctrl As MSFORMS.ComboBox)
        Set Target = ctrl
    End Sub
    Private Sub Target_Change()
        Call Module1.ComboBox_Change(Target)
    End Sub
    
    
    '==== Worksheet ====
    Option Explicit
    Private ComboboxEvents As VBA.Collection
    
    Public Sub SetEvents()
        Set ComboboxEvents = New VBA.Collection
        Dim o As Excel.OLEObject
        For Each o In Me.OLEObjects
            If TypeOf o.Object Is MSFORMS.ComboBox Then
                Dim e As Class1
                Set e = New Class1
                e.SetCtrl o.Object
                ComboboxEvents.Add e
            End If
        Next
    End Sub
    • 回答としてマーク Dreizehn 2017年4月12日 8:57
    • 回答としてマークされていない Dreizehn 2017年4月12日 8:57
    • 回答としてマーク Dreizehn 2017年4月12日 8:58
    2017年4月10日 7:45
  • 魔界の仮面弁士様、回答ありがとうございます。

    あらかじめ多めに WithEvents 変数を用意しておき、それを用いるといった手段になってしまいます。

    やはりそうなりますか。

    追加するコントロールの数の上限等を確認し、提示頂いたソースも確認してみます。

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

    2017年4月12日 8:58