Assign macro to toolbar custom button?
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, Befo=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.
--
Regards,
Tom Ogilvy
"amescha" wrote in message
...
-----------------------------------------------
"amescha" wrote:
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!
"sebastienm" wrote:
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, Befo=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.
|