PDA

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



vanhunk
05-03-2013, 05:02 AM
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
05-03-2013, 05:52 AM
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
05-05-2013, 12:01 PM
Thank you GTO, exactly what I was looking for.

Regards
:beerchug:

snb
05-05-2013, 03:16 PM
or:

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

vanhunk
05-06-2013, 07:21 AM
Great stuff snb!

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

:bow:

GTO
05-07-2013, 03:34 AM
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
05-07-2013, 05:18 AM
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
05-07-2013, 06:05 AM
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
05-07-2013, 06:07 AM
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
05-07-2013, 10:10 PM
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
05-08-2013, 12:10 PM
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
05-08-2013, 03:44 PM
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
05-09-2013, 12:02 AM
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
05-09-2013, 01:07 AM
I am glad that worked and of course you are most welcome :beerchug:.

Aflatoon
05-09-2013, 01:22 AM
FYI, you can refer to a control using its caption

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

vanhunk
05-09-2013, 11:55 PM
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!