Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom toolbar for template - macros not working
Hi,
I am running Excel 2003. I have tried to follow Dave Peterson's instructions for creating and destroying a toolbar when a document is opened/closed. The create/destroy is working ok but when I click on the buttons an error when I click the buttons on the toolbar. Even though the macros are ALL in the file, I get an error like the following. (I've substituted generic terms. It always accurately lists the open file name and the macro I'm trying to execute.) The macro 'OpenFileName!MacroName' cannot be found. Here is my VBA code. Can anyone tell me how to get Excel to find the macros? Thanks very much for your help. Ann ______________________________________ Sub create_menubar() Dim i As Long Dim mac_names As Variant Dim cap_names As Variant Dim tip_text As Variant Call remove_menubar mac_names = Array("CMISfilter", _ "CMISUnfilter", _ "SortBySCR", _ "MoveTotal", _ "eBizFilter", _ "eBizUnfilter", _ "CopyCMISdata") cap_names = Array("Collapse CMIS Data", _ "Expand CMIS Data", _ "Sort CMIS Data", _ "Move CMIS Totals", _ "Collapse eBiz Data", _ "Expand eBiz Data", _ "Copy SCR Data") tip_text = Array("Hide unused rows in CMIS Data sheet", _ "Display all rows in CMIS Data sheet", _ "Sort CMIS Data sheet by SCR then Program", _ "Move CMIS Data Totals and clear daily hours", _ "Hide unused rows in eBiz Data sheet", _ "Display all rows in eBiz Data sheet", _ "Copy SCR rows for pasting into aggregate worksheet") With Application.CommandBars.Add .Name = "BiWeeklyStatusReportTools" .Left = 200 .Top = 200 .Protection = msoBarNoProtection .Visible = True .Position = msoBarTop For i = LBound(mac_names) To UBound(mac_names) With .Controls.Add(Type:=msoControlButton) .OnAction = ThisWorkbook.Name & "!" & mac_names(i) .Caption = cap_names(i) .Style = msoButtonIconAndCaption .FaceId = 552 .TooltipText = tip_text(i) End With Next i End With End Sub Sub remove_menubar() On Error Resume Next Application.CommandBars("BiWeeklyStatusReportTools ").Delete On Error GoTo 0 End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom toolbar for template - macros not working
Where did you put those 7 macros?
Did you put them in a General module (not behind a worksheet and not behind ThisWorkbook)? If you did (and spelled them so that they matched <bg), try changing this: ..OnAction = ThisWorkbook.Name & "!" & mac_names(i) to ..OnAction = "'" & ThisWorkbook.Name & "'!" & mac_names(i) (note the apostrophe added before the exclamtion point) Ann Scharpf wrote: Hi, I am running Excel 2003. I have tried to follow Dave Peterson's instructions for creating and destroying a toolbar when a document is opened/closed. The create/destroy is working ok but when I click on the buttons an error when I click the buttons on the toolbar. Even though the macros are ALL in the file, I get an error like the following. (I've substituted generic terms. It always accurately lists the open file name and the macro I'm trying to execute.) The macro 'OpenFileName!MacroName' cannot be found. Here is my VBA code. Can anyone tell me how to get Excel to find the macros? Thanks very much for your help. Ann ______________________________________ Sub create_menubar() Dim i As Long Dim mac_names As Variant Dim cap_names As Variant Dim tip_text As Variant Call remove_menubar mac_names = Array("CMISfilter", _ "CMISUnfilter", _ "SortBySCR", _ "MoveTotal", _ "eBizFilter", _ "eBizUnfilter", _ "CopyCMISdata") cap_names = Array("Collapse CMIS Data", _ "Expand CMIS Data", _ "Sort CMIS Data", _ "Move CMIS Totals", _ "Collapse eBiz Data", _ "Expand eBiz Data", _ "Copy SCR Data") tip_text = Array("Hide unused rows in CMIS Data sheet", _ "Display all rows in CMIS Data sheet", _ "Sort CMIS Data sheet by SCR then Program", _ "Move CMIS Data Totals and clear daily hours", _ "Hide unused rows in eBiz Data sheet", _ "Display all rows in eBiz Data sheet", _ "Copy SCR rows for pasting into aggregate worksheet") With Application.CommandBars.Add .Name = "BiWeeklyStatusReportTools" .Left = 200 .Top = 200 .Protection = msoBarNoProtection .Visible = True .Position = msoBarTop For i = LBound(mac_names) To UBound(mac_names) With .Controls.Add(Type:=msoControlButton) .OnAction = ThisWorkbook.Name & "!" & mac_names(i) .Caption = cap_names(i) .Style = msoButtonIconAndCaption .FaceId = 552 .TooltipText = tip_text(i) End With Next i End With End Sub Sub remove_menubar() On Error Resume Next Application.CommandBars("BiWeeklyStatusReportTools ").Delete On Error GoTo 0 End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom toolbar for template - macros not working
Bless you!!!!!!!!!!!!!!! That fixed it! Thanks so much for answering so
quickly! I have been pulling my hair out today because 45 people are waiting for me to roll out this template. I decided Friday night to add a menu bar because they couldn't remember the mnemonics for the six macros that applied to them. This has been a real educational experience! I really apprediate your taking the time to answer. Ann "Dave Peterson" wrote: Where did you put those 7 macros? Did you put them in a General module (not behind a worksheet and not behind ThisWorkbook)? If you did (and spelled them so that they matched <bg), try changing this: ..OnAction = ThisWorkbook.Name & "!" & mac_names(i) to ..OnAction = "'" & ThisWorkbook.Name & "'!" & mac_names(i) (note the apostrophe added before the exclamtion point) Ann Scharpf wrote: Hi, I am running Excel 2003. I have tried to follow Dave Peterson's instructions for creating and destroying a toolbar when a document is opened/closed. The create/destroy is working ok but when I click on the buttons an error when I click the buttons on the toolbar. Even though the macros are ALL in the file, I get an error like the following. (I've substituted generic terms. It always accurately lists the open file name and the macro I'm trying to execute.) The macro 'OpenFileName!MacroName' cannot be found. Here is my VBA code. Can anyone tell me how to get Excel to find the macros? Thanks very much for your help. Ann ______________________________________ Sub create_menubar() Dim i As Long Dim mac_names As Variant Dim cap_names As Variant Dim tip_text As Variant Call remove_menubar mac_names = Array("CMISfilter", _ "CMISUnfilter", _ "SortBySCR", _ "MoveTotal", _ "eBizFilter", _ "eBizUnfilter", _ "CopyCMISdata") cap_names = Array("Collapse CMIS Data", _ "Expand CMIS Data", _ "Sort CMIS Data", _ "Move CMIS Totals", _ "Collapse eBiz Data", _ "Expand eBiz Data", _ "Copy SCR Data") tip_text = Array("Hide unused rows in CMIS Data sheet", _ "Display all rows in CMIS Data sheet", _ "Sort CMIS Data sheet by SCR then Program", _ "Move CMIS Data Totals and clear daily hours", _ "Hide unused rows in eBiz Data sheet", _ "Display all rows in eBiz Data sheet", _ "Copy SCR rows for pasting into aggregate worksheet") With Application.CommandBars.Add .Name = "BiWeeklyStatusReportTools" .Left = 200 .Top = 200 .Protection = msoBarNoProtection .Visible = True .Position = msoBarTop For i = LBound(mac_names) To UBound(mac_names) With .Controls.Add(Type:=msoControlButton) .OnAction = ThisWorkbook.Name & "!" & mac_names(i) .Caption = cap_names(i) .Style = msoButtonIconAndCaption .FaceId = 552 .TooltipText = tip_text(i) End With Next i End With End Sub Sub remove_menubar() On Error Resume Next Application.CommandBars("BiWeeklyStatusReportTools ").Delete On Error GoTo 0 End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros - copying macros from one computer to another | Excel Discussion (Misc queries) | |||
Macros Don't Show On Commands List and 'Normal.dot' | Excel Worksheet Functions | |||
custom toolbar looses buttons when closed | Setting up and Configuration of Excel | |||
Removing custom toolbar from Excel 2003 | Excel Discussion (Misc queries) | |||
I would like to delete a custom toolbar from the workspace. How? | Setting up and Configuration of Excel |