Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving / Emailing Macro Workbooks with Custom Toolbars
Hi Everyone,
I'm going crazy so I was hoping someone could help me. I have an excel workbook with macros in it and also a custom toolbar. If I move the workbook from my desktop to a folder, or if I email the file, the toolbar icons stop linking back to the macros. It just displays a message saying that you can't have 2 files open with the same name at the same time. Is there a way to fix this so that the toolbar icons function correctly even if the file was moved? I would appreciate any help. Thanks in advance. - Jonathan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving / Emailing Macro Workbooks with Custom Toolbars
Jon,
Do not attach the toolbar: create it using the workbook open event, and detroy it using the workbook close event. There are plenty of code examples available - Here is my example - 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 next two are optional 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, simply by repeating the lines Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23) With myButton ..Caption = "Hello" ..Style = msoButtonIcon ..FaceId = 137 ..Enabled = True ..OnAction = "SayHello" End With but with changes to the values, of course. HTH, Bernie MS Excel MVP "Jon Lam" wrote in message ... Hi Everyone, I'm going crazy so I was hoping someone could help me. I have an excel workbook with macros in it and also a custom toolbar. If I move the workbook from my desktop to a folder, or if I email the file, the toolbar icons stop linking back to the macros. It just displays a message saying that you can't have 2 files open with the same name at the same time. Is there a way to fix this so that the toolbar icons function correctly even if the file was moved? I would appreciate any help. Thanks in advance. - Jonathan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving / Emailing Macro Workbooks with Custom Toolbars
Bernie, thank you for your excellent response. this is exactly the type of
solution I have been needing! David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Menus and Toolbars | Excel Discussion (Misc queries) | |||
Custom Toolbars - keep dissappearing | Excel Discussion (Misc queries) | |||
emailing workbooks | Excel Worksheet Functions | |||
Custom toolbars | Excel Discussion (Misc queries) | |||
Links between workbooks and emailing workbook | Excel Worksheet Functions |