Filename leads MacroName

[Guest]

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

 

[Bernie Deitrick]

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
'NotThere:
' 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.

 

[Guest]

Guess in your case, Bernie is either suggesting you change to commandbars or
in the workbook.open event, loop through your jpgs and drawing objects and
reassign the respective macros with code (using the onaction property of the
objects).

 

[Guest]

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

 

[Guest]

Thanks Tom..........I figured out the first part, but was stumbling with the
second when your post appeared. Then I discovered the actual trouble. It
was cockpit trouble, as usual. The "button" I was using was actually a
grouped figure and one of the parts contained the errant macro which was
bothering me. I simply ungrouped and cleared all macros assigned to the
parts and then re-grouped and assigned only my final macro to the combined
figure, saved the workbook and now all is well.........

Vaya con Dios,
Chuck, CABGx3