ツールバーでコンボボックスを使う

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

↑こんな感じです。新しいツールバーを作るところまでは一緒です。コンボ ボックスを配置するコードは、

Sub AddComboBox() ''ツールバー[検索ツールバー]にコンボ ボックスを作る
    With CommandBars("検索ツールバー").Controls.Add(Type:=msoControlComboBox)
        .Caption = "ComboBox"
        .OnAction = "SheetSearch"
    End With
End Sub

となります。引数 Type に指定する定数が違うところに留意してください。

配置したコンボ ボックスは、UserForm 上で利用できるコンボ ボックスと同じように扱えます。データを追加するときは、

Sub AddDate()
    Dim i As Long
    For i = 1 To 10
        CommandBars("検索ツールバー").Controls("ComboBox").AddItem "データ " & i
    Next i
    CommandBars("検索ツールバー").Controls("ComboBox").ListIndex = 3
End Sub

のようにします。ListIndex を設定することもできます。

コンボ ボックスのテキスト入力部で Enter キーを押したときと、リスト ボックス部でデータを選択したときに、OnAction プロパティに登録したプロシージャが実行されます。テキスト部に入力された文字列は Text プロパティで、リスト ボックス部で選択したデータは

List(ListIndex) で取得できます。要するに、UserForm 上で使うコンボ ボックスと同じです。

ここではサンプルとして、テキスト部に入力したデータもしくはリスト部で選択したデータをアクティブシートで検索するプロシージャを紹介します。新しく検索したデータはリスト部の先頭に追加されます。

Sub SheetSearch() ''検索するプロシージャ
    Dim FoundCell As Variant
    Set FoundCell = Cells.Find(What:=CommandBars("検索ツールバー").Controls("ComboBox").Text)
    If Not FoundCell Is Nothing Then FoundCell.Activate
    Call AddDate
End Sub
Sub AddDate()     ''履歴を登録するプロシージャ
    Dim i As Long
    With CommandBars("検索ツールバー").Controls("ComboBox")
        ''登録データ1件もがなかったら、登録しておしまい
        If .ListCount = 0 Then
            .AddItem .Text
            Exit Sub
        End If
        ''既存リストに新規データがあったら...
        For i = 1 To .ListCount
            If .Text = .List(i) Then
                .RemoveItem (i)
                .AddItem .Text, 1
                Exit Sub
            End If
        Next i
        ''既存リストに新規データがなかったら...
        .AddItem .Text, 1
    End With
End Sub