Solved: Use VBA to assign assign code/a macro to a custom button added to a toolbar?it

[vanhunk]

Hi All,
I added a custom button to the "Worksheet Menu Bar", using the code below. Is it possible to also assign code/a macro to the custom button using VBA?

Sub VNK()
Application.CommandBars("Worksheet Menu Bar").Controls.Add Type:= _
msoControlButton, ID:=2950, Before:=36
End Sub

Tnx in advance

[GTO]

The Before:=36 fouled for me, I imagine that you are in 2003 or 2002; disregarding that - without any args:

Option Explicit

Sub VNK()
Dim CbarCtrl As CommandBarControl

Set CbarCtrl = _
Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlButton, _
ID:=2950, _
Temporary:=True)
With CbarCtrl
.OnAction = "MyMacro"
End With
End Sub

Sub MyMacro()
MsgBox "Assigned Procedure"
End Sub

Hope that helps,

Mark

[vanhunk]

Thank you GTO, exactly what I was looking for.

Regards
:beerchug:

[snb]

or:

Sub M_snb()
Application.CommandBars("Worksheet Menu Bar").Controls.Add(1, 2950, , , True).OnAction = "YourMacro"
End Sub

[vanhunk]

Great stuff snb!

It works like a charm, if you remember to include the ""s!

:bow:

[GTO]

Hi Vanhunk,

You are welcome of course. Just a note, though you probably have already covered this... I would want to include coding in the wb's de/activate events to dis/able the control. Just a late thought on my part.

Mark

[snb]

Just another one:

If you use one of the builtin commands (i.c. 2950), it's not obvious to refer to an 'own' macro.

[vanhunk]

Hi Vanhunk,

You are welcome of course. Just a note, though you probably have already covered this... I would want to include coding in the wb's de/activate events to dis/able the control. Just a late thought on my part.

Mark

Hi Mark,

How would you do that?

Thanks

[vanhunk]

Just another one:

If you use one of the builtin commands (i.c. 2950), it's not obvious to refer to an 'own' macro.

Hi snb, you've lost me. What do you mean?

[GTO]

Hi Van,

By simple example, this should create the control when the workbook is opened or activated, and the control will be deleted whenever the workbook is closed or deactivated.

In ThisWorkbook Module:
Option Explicit

Private CbarCtrl As CommandBarControl

Private Sub Workbook_Activate()

On Error Resume Next
CbarCtrl.Delete
On Error GoTo 0

Set CbarCtrl = _
Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlButton, _
ID:=2950, _
Temporary:=True)

With CbarCtrl
.OnAction = "MyMacro"
End With

End Sub

Private Sub Workbook_Deactivate()

On Error Resume Next
CbarCtrl.Delete
On Error GoTo 0

End Sub


Mark

[vanhunk]

Hi Mark,

Thanks, it does exactly what it is supposed to do. However, I guess what I am looking for is how to name the button so as to delete a specific button.

I deleted three buttons and recorded the code:
Application.CommandBars("Worksheet Menu Bar").Controls(38).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(37).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(36).Delete
Instead of having "Controls(38)", I would like to have it named something like "MyButton1" and then use this name to delete it with.

I hope you can help me out on this one.

Again thanks a lot for the help so far :thumb

[GTO]

Hi Van.

Apologies, but I am not following. The button's Object name or Code name if-you-will, becomes 'CbarCtrl ' upon the Set statement executing. This then allows intellisense to be used. If you mean using a .Name property like Worksheets("MySheet"), where "MySheet" is the current value of the worksheet's .Name property, I do not believe that commandbar controls have this property. I would note that I forget a lot, and am currently at work where we use the 2010 version of Excel so I cannot utterly confirm that last bit.

Mark

[vanhunk]

Thanks Mark,

I didn't understand the code fully, but you put me on the right track. I played with it a bit and I am happy with your answer. We have achieved what I was looking for.

Again, thanks a lot and I appreciate your patience.

Regards,
vanhunk

[GTO]

I am glad that worked and of course you are most welcome :beerchug:.

[Aflatoon]

FYI, you can refer to a control using its caption

Application.CommandBars("Worksheet Menu Bar").Controls("My caption").Delete

[vanhunk]

FYI, you can refer to a control using its caption

Application.CommandBars("Worksheet Menu Bar").Controls("My caption").Delete
Thanks Aflatoon, this is what I had in mind. :yes

Have a great day!