Moving / Emailing Macro Workbooks with Custom Toolbars

[Jon Lam]

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

 

[Bernie Deitrick]

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
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, 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

 

[Shanx]

Bernie, thank you for your excellent response. this is exactly the type of
solution I have been needing!

David