Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign macro to toolbar custom button?
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign macro to toolbar custom button?
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. ----------------------------------------------- "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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign macro to toolbar custom button?
-----------------------------------------------
"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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign macro to toolbar custom button?
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, Befo=1) Cmd.OnAction = "EquGen.xls!CopyToEquationGenerator" Is EquGen.xls the ThisWorkbook? or is it another book? Regards, sebastien "amescha" wrote: ----------------------------------------------- "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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign macro to toolbar custom button?
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, Befo=1) Cmd.Caption = "CopyTo" Cmd.OnAction = "'EquGen.xls'!CopyToEquationGenerator" Set Cmd = Application.CommandBars("EquationCopies").Controls .Add( _ Type:=msoControlButton, _ ID:=2950, Befo=2) Cmd.Caption = "CopyTo" Cmd.OnAction = "'EquGen.xls'!CopyToEquationGeneratorTarget" Set Cmd = Application.CommandBars("EquationCopies").Controls .Add( _ Type:=msoControlButton, _ ID:=2950, Befo=3) Cmd.Caption = "CopyTo" Cmd.OnAction = "'EquGen.xls'!CopyToErrorAnalysis" Set Cmd = Application.CommandBars("EquationCopies").Controls .Add( _ Type:=msoControlButton, _ ID:=2950, Befo=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 - assign macro to custom toolbar button | Excel Discussion (Misc queries) | |||
Assign Add-In to Custom Toolbar Button Microsoft Office 2007 | Excel Discussion (Misc queries) | |||
Lost new custom toolbar button with its macro | Excel Discussion (Misc queries) | |||
Invoking a Visual Basic macro from a Custom Toolbar Button | Excel Worksheet Functions | |||
How Do I Assign a Toolbar Button to a Custom Number Format I creat | Excel Discussion (Misc queries) |