Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename leads MacroName
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename leads MacroName
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename leads MacroName
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). -- Regards, Tom Ogilvy "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filename leads MacroName
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 "Tom Ogilvy" wrote: 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). -- Regards, Tom Ogilvy "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filename!macroname as variables | Excel Discussion (Misc queries) | |||
Quarterly Leads Tracking Template | Excel Discussion (Misc queries) | |||
need to track sales leads and appointments what software do I nee | Excel Discussion (Misc queries) | |||
Is there a free excel spreadsheet for tracking leads? | Excel Discussion (Misc queries) | |||
Organize spreadsheet of sales leads by zip code? | Excel Discussion (Misc queries) |