Office TANAKA - Excel VBA Tips[ツールバーでテキストボックスを使う]

↑こんな感じのツールバーです。もちろんテキストボックスには自由に文字列を入力・編集できます。
ちなみに、同じようなやり方で、ツールバーにコンボボックスを配置することもできます。
興味のある方は「ツールバーでコンボボックスを使う」をご覧ください。

また「メニューバーに独自のメニューを追加する」と同じ方法で、ツールバーにメニューを追加することも可能です。

まず新しいツールバーを作ります。VBA を使って自動的に作ることもできますが、これは手作業で作った方が簡単です。ここでは[検索ツールバー]という名前のツールバーを例にして解説します。

【手作業で新しいツールバーを作る】

1. [ツール] - [ユーザー設定]を実行する。
2. [ユーザー設定] ダイアログボックスの [ツールバー] タブで [新規作成] ボタンをクリックする。
3. [ツールバー名] ボックスに「検索ツールバー」と入力して [OK] ボタンをクリックする。

蛇足ですが...

【VBAで新しいツールバーを作る】

Sub newb()
    CommandBars.Add "検索ツールバー"
End Sub

これで「検索ツールバー」という新しいツールバーで作成されます。ただし、すでに同名のツールバーが存在するとエラーになりますので、下のようにチェックした方がいいでしょうね。

Sub newb()
    Dim tb As Variant
    For Each tb In CommandBars
        If tb.Name = "検索ツールバー" Then
            MsgBox "すでに同名のツールバーがあります。", 16
            Exit Sub
        End If
    Next tb
    CommandBars.Add "検索ツールバー"
End Sub

さて、ツールバーにボタンなどのコントロールを配置するときは、[ユーザー設定] ダイアログボックスなどからドラッグ & ドロップします。しかし、どこにもドロップできるテキストボックスはありません。ツールバー上に配置するテキストボックスは、VBA のコードで作成してやらなければなりません。次のようにします。

Sub AddTextBox() ''ツールバー[検索ツールバー]にテキスト ボックスを作る
    With CommandBars("検索ツールバー").Controls.Add(Type:=msoControlEdit)
        .Caption = "EditBox"
        .TooltipText = "検索語を入力してEnterキーを押してください"
        .OnAction = "SheetSearch"
    End With
End Sub

Caption プロパティはテキストボックスを識別するための名前です。
TooltipText プロパティに設定した文字列は、テキストボックスにマウスポインタを併せたときに表示されます。
テキストボックス上で Enter キーが押されたときに実行されるプロシージャを OnAction プロパティに設定します。ここでは、テキストボックスに入力された文字列をアクティブシート上で検索するプロシージャを作ってみました。

Sub SheetSearch()
    Dim FoundCell As Variant
    Set FoundCell = Cells.Find(What:=CommandBars("検索ツールバー").Controls("EditBox").Text)
    If Not FoundCell Is Nothing Then FoundCell.Activate
End Sub

Find メソッドの引数は、お好きなように設定してください。

他にも、ワークシートの SelectionChange イベントと組み合わせて

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    CommandBars("検索ツールバー").Controls("EditBox").Text = _
                  Format(WorksheetFunction.Sum(Target.Value), "#,##円")
End Sub

↓なんてのも、おもしろいですね。