Excel VBAでツールバーにマクロを登録したい

質問

自作のツールバーをブックに添付し、
スタートアップにマクロを書いたブックを保存し(起動して非表示にしてあります)、
そのマクロをツールバーの各ボタンに登録しようとしています。

下記のようなコードを書いたのですが、ツールバーに作成したボタンにはマクロが登録できるのですが、
サブメニュー内のボタンには登録できません。

Dim MyWB, NBar
MyWB = "stampB.xls"
NBar = "stampB1.0"

For i = 1 To Application.CommandBars(NBar).Controls.Count
　'ボタンのキャプションと同じ名前のマクロを登録
　With Application.CommandBars(NBar).Controls.Item(i)
　　.OnAction = MyWB & "!" & .Caption
　End With
Next i

これをどのように改良したら、サブメニュー内のボタンにもマクロを登録できるようになりますでしょうか？
ご存知の方どうぞよろしくお願いします。

回答No.4

Wendy02

#1 のWendy02です。

#2/3さんのコードをみて、たぶん、ツールバーの構造は、そういうことなんだろうなって思いました。私のは無視してください。ただ、私は、元のツールバーのマクロで、そのような作り方はしませんから、まったく想像付きませんでした。

.OnAction = MyWB & "!" & .Caption

既に、何らかの方法で、名前とコントロールは置いておいて、それに、OnAction を後付けしているわけですね。マクロでボタン付けするなら、最初から、マクロで作ればよいのに、と思います。それとも、その都度変るものなのかもしれませんが。

回答No.3

bonaron

#2です。

Dim MyWB As String
Dim NBar As String
Dim i As Integer

全部書けば良かった　(^^;)

回答No.2

bonaron

再起呼び出しをしています。

For i = 1 To Application.CommandBars(NBar).Controls.Count
'ボタンのキャプションと同じ名前のマクロを登録
SetAction Application.CommandBars(NBar).Controls.Item(i), MyWB
Next i

Private Function SetAction(Ctl As CommandBarControl, Book As String)
Const ButtonOnly As Boolean = True
'PopUpにもマクロがある場合にはこれを「False」に
Dim i As Integer
On Error Resume Next
With Ctl
If .Type = msoControlButton Then
.OnAction = Book & "!" & .Caption
ElseIf .Type = msoControlPopup Then
If Not ButtonOnly Then
.OnAction = Book & "!" & .Caption
End If
For i = 1 To Ctl.Controls.Count
SetAction Ctl.Controls.Item(i), Book
Next
End If
End With
End Function

回答No.1

Wendy02

こんにちは。

ツールバーの構造がどうなっているか、そのコードでは読み取れませんが、Add メソッドを使わない限りは、出来ないと違いますか？

＞　　.OnAction = MyWB & "!" & .Caption

それに、これは、理屈に合いません。
.OnAction = MyWB & "!" & "マクロ名"
.Caption ="マクロ名"

となるので、例えば、ボタンにした場合は、以下のようになるのではないでしょうか？コントロールの数があれば、その数だけ作ることになるかと思います。

Set CB = Application.CommandBars(NBar)
　With CB.Controls.Add(Type:=msoControlButton)
　　.OnAction = MyWB & "!" & "マクロ名"
　　.Caption = "マクロ名"
　　.Visible = True
　End With