Filename leads MacroName
Thanks for the quick response Bernie.
I'm not using a CommandBar or ToolBar. I am placing the .jpg's and
DrawingObjects directly on each sheet in the workbook, then assigning macros
directly to them. When I tried the line
"CreateCommandbar", I got an error anyway in my XL97.
The real problem I'm having is that I have a Password to open this workbook,
which gives a pop-up and works fine. then when I trigger the macro with the
workbook name in front of it, the pop-up comes back up for the workbook
password again.....I don't want that to happen. Most of my other macros do
not have the workbook name included with the macroname.....only a few.
Vaya con Dios,
Chuck, CABGx3
"Bernie Deitrick" wrote:
Chuck,
Below is my standard response to this sort of question.
HTH,
Bernie
MS Excel MVP
The best option is to create the commandbar on the fly, when the workbook is
opened, and delete the commandbar when the workbook is closed. Follow
these instructions and example code.
In the workbook's Thisworkbook object code module, place the following code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCommandbar
End Sub
Private Sub Workbook_Open()
CreateCommandbar
End Sub
'These two are optional, use if you want to only use the toolbar
'with the file where the code is located
'Private Sub Workbook_WindowActivate(ByVal Wn As Window)
' On Error GoTo NotThere
' Application.CommandBars("My Bar").Visible = True
' Exit Sub
'NotThe
' CreateCommandbar
'End Sub
'Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
' On Error Resume Next
' Application.CommandBars("My Bar").Visible = False
'End Sub
In a regular code module, place the following:
Dim myBar As CommandBar
Dim myButton As CommandBarButton
Sub CreateCommandbar()
On Error Resume Next
DeleteCommandBar
Set myBar = Application.CommandBars.Add("My Bar")
With myBar
.Position = msoBarTop
.Visible = True
.Enabled = True
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.Enabled = True
.OnAction = "SayHello"
End With
End With
End Sub
Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars("My Bar").Delete
End Sub
Sub SayHello()
MsgBox "Hello there"
End Sub
You can add as many buttons or other menu items as you like.
"CLR" wrote in message
...
Hi All.....
I put drawing objects and .jpg's in some of my workbooks and assign macros
to them. The macros are in that workbook. This works fine, except sometimes
Excel places the WorkbookName in front of the MacroName in the "assigned to"
link. This also works fine unless I change the name of the Workbook. The
"button" is now married to the old WorkbookName so it tries to find that
workbook to run the macro. I change the AssignMacro wizard to exclude the
WorkbookName but Excel keeps putting it back there. This doesn't happen on
ALL the macros I assign to "buttons", just some. How can I permanently
remove the WorkbookName from the MacroName when assigning the macro to a
"button"? I want the button to trigger the named macro in the current active
workbook only.
TIA
Vaya con Dios,
Chuck, CABGx3
|