Assign macro to toolbar custom button?

[Guest]

Hello,

I an attempting to assign a macro to a user defined button on a toolbar. The
toolbar may be either the Standard Toolbar or a custom toolbar. I am not able
to get VBA to record any of this, which is what troubles me. I have a
Workbook_Open event in ThisWorkbook module which adds the button just fine.
Is there some VBA code that will allow me to automatically assign a macro to
a button?

On a possibly related note I also have no luck recording a macro that will
reset the Standard toolbar.

Confused about toolbars in Eugene Oregon!

 

[Guest]

Hi
Say your CommandBarButton is the variable Cmd, and MyMacro is the macro that
the button should run when clicked. Use the code:
Dim Cmd as CommandBarButton
...
Cmd.OnAction = "'" & ThisWorkbook.Name & "'!MyMacro"

Regards,
Sebastien... From Portland, Oregon.

 

[Guest]

-----------------------------------------------

Hi
Say your CommandBarButton is the variable Cmd, and MyMacro is the macro that
the button should run when clicked. Use the code:
Dim Cmd as CommandBarButton
...
Cmd.OnAction = "'" & ThisWorkbook.Name & "'!MyMacro"

Regards,
Sebastien... From Portland, Oregon.

Dear Sebastien, (or anyone)

Well I did try your code but had a Run-Time error 424: Object Required
I'll copy in my code with a few added comment lines:

Sub AddButtonTest2()
'
' AddButtonTest2 Macro
' Macro recorded 9/24/2004 by Knight ITC
'

'
' File name = EquGen.xls
' Macro name = CopytoEquationGenerator
' CommandBar name = EquationCopies
Dim Cmd As CommandBarButton
Application.CommandBars("EquationCopies").Controls.Add
Type:=msoControlButton, ID _
:=2950, Before:=1
' Following line gives Run-time error '424': Object Required
Cmd.OnAction = "'" & EquGen.xls & "!CopyToEquationGenerator"
End Sub

I would imagine I'm missing some silly little thing and am not all that
nuanced when it comes to programming. Just giving it the Old College try here
at (where else in Eugene?) but the UO.

Thank in advance for any & all advise.

 

[Guest]

2 things,
1. you needed to set the Cmd to the specific object (command button)
2. EquGen.xls is not a variable so it should be included directly within
the string.
Try:
Dim Cmd As CommandBarButton
Set Cmd= Application.CommandBars("EquationCopies").Controls.Add( _
Type:=msoControlButton, ID :=2950, Before:=1)
Cmd.OnAction = "EquGen.xls!CopyToEquationGenerator"

Is EquGen.xls the ThisWorkbook? or is it another book?

Regards,
sebastien

 

[Tom Ogilvy]

You don't match the single quote and the file name is not a string

Cmd.OnAction = "'" & EquGen.xls & "!CopyToEquationGenerator"

would be
Cmd.OnAction = "'EquGen.xls'!CopyToEquationGenerator"



but you haven't gotten to that problem yet. CMD is undefined when you try
to use it.

Sub AddButtonTest2()
'
' AddButtonTest2 Macro
' Macro recorded 9/24/2004 by Knight ITC
'

'
' File name = EquGen.xls
' Macro name = CopytoEquationGenerator
' CommandBar name = EquationCopies
On Error Resume Next
' delete the button if it already exists so you don't get two
Application.CommandBars("EquationCopies").Controls("CopyTo").Delete
On Error GoTo 0
Dim Cmd As CommandBarButton
Set Cmd = Application.CommandBars("EquationCopies").Controls.Add( _
Type:=msoControlButton, _
Id:=2950, Before:=1)
Cmd.Caption = "CopyTo"
Cmd.OnAction = "'EquGen.xls'!CopyToEquationGenerator"
End Sub

Sub CopyToEquationGenerator()
MsgBox "in CopyToEquationGenerator"
End Sub

worked for me. Assumes the CommandBar "EquationCopies" already exists.

 

[Guest]

Dear Tom,

Thank you very much, your code works just as you say it does, viz:

Sub AddButtonTest2()
'
' AddButtonTest2 Macro
' Macro recorded 9/24/2004 by Knight ITC
'
' File name = EquGen.xls
' Toolbar name = EquationCopies
' Toolbar starts without any buttons
' 1st Macro name = CopytoEquationGenerator
' 2nd Macro name = CopytoEquationGeneratorTarget
' 3rd Macro name = CopytoErrorAnalysis
' 4th Macro name = CopytoErrorAnalysis
Dim Cmd As CommandBarButton
Application.CommandBars.Add(Name:="EquationCopies").Visible = True
Set Cmd = Application.CommandBars("EquationCopies").Controls.Add( _
Type:=msoControlButton, _
ID:=2950, Before:=1)
Cmd.Caption = "CopyTo"
Cmd.OnAction = "'EquGen.xls'!CopyToEquationGenerator"
Set Cmd = Application.CommandBars("EquationCopies").Controls.Add( _
Type:=msoControlButton, _
ID:=2950, Before:=2)
Cmd.Caption = "CopyTo"
Cmd.OnAction = "'EquGen.xls'!CopyToEquationGeneratorTarget"
Set Cmd = Application.CommandBars("EquationCopies").Controls.Add( _
Type:=msoControlButton, _
ID:=2950, Before:=3)
Cmd.Caption = "CopyTo"
Cmd.OnAction = "'EquGen.xls'!CopyToErrorAnalysis"
Set Cmd = Application.CommandBars("EquationCopies").Controls.Add( _
Type:=msoControlButton, _
ID:=2950, Before:=4)
Cmd.Caption = "CopyTo"
Cmd.OnAction = "'EquGen.xls'!CopyToErrorAnalysisTarget"
End Sub

I have taken the option to remove some of the code dealing with button
duplication since this is going into a Workbook_Open() event in the
"ThisWorkbook" module.

I do get four identical nice simley buttons and would like to make them
different since they do activate seperate procedures. If possible I would
also like to add whatever those little pop-up tips that appear when the mouse
pointer is on a particular button. I'll just start another thread on this
matter. Thanks again!

Charles Ames
University of Oregon