G
Guest
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
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