Linked toolbar

[Guest]

Hello,

I have some excel files with userforms. I have procedures to show these
forms, that are linked to toolbar buttons (1 for each form). If the file name
is changed or is moved to a different folder, the button wont work anymore
(unless the file name is changed with "save as" of course).

My workaroud:

1. in Workbook_Open(), I run a procedure that links all forms to the toolbar
buttons.

2. Once "installed" the toolbar, it stores the workbook's current path in a
hidden sheet and saves the file.

3. The next time we open the file, in Workbook_Open() will check the current
path with tha saved one, if its different, it will install the toolbar again.
Looks like this:

Private Sub Workbook_Open()
If HData.Range(HDATA_CELL_INSTALL) <> ThisWorkbook.Path Then
If MsgBox("Instalar la barra de herramientas?", vbYesNo) = vbYes Then
instalar
ThisWorkbook.Save
End If
End If

End Sub


I am trying to find a way to check if the workbook is linked to the toolbar,
or if the toolbar has a valid path and file, or something like it.

Appreciate your help!

 

[Peter T]

Hi Fernando,

You could create a new set of menus each time you load your file and delete
on close.

If you have several similar files open at the same time with similar code in
each, you could create and delete menus in workbook activate / deactivate
events.

Regards,
Peter T

 

[Guest]

Where can I read about how to create/delete menus?

Thanks

 

[Peter T]

I used the term "menu" very loosely to mean one or more pop up buttons,
either on one of Excel's toolbars or on your own custom toolbar, possibly
cascading in a tree like structure.

The majority of downloadable addins you find linked in posts in this ng
normally create some form of menu on file open and delete same on close.

John Walkenbeck makes it all very easy here:
http://j-walk.com/ss/excel/tips/tip53.htm

Look at "Menu Routines" on Andy Wiggins' page
http://www.bygsoftware.com/examples/examples.htm

I'm sure a quick search will find several other links.

Try this highly simplified example that assumes you want to create and
delete your own custom toolbar each time the workbook is activated /
deactivated. This might be an approach if you have several similar files
each with same code and possibly open at the same time, as implied in your
original post.

'' start code in "ThisWorkbook module
Private Sub Workbook_Activate()
MakeToolbar
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars("My Bar").Delete
End Sub

'' end code in "ThisWorkbook module

''start code in a normal module

Sub MakeToolbar()

On Error Resume Next
Application.CommandBars("My Bar").Delete
On Error GoTo 0
With Application.CommandBars.Add("My Bar", , , True)
With .Controls.Add(Type:=msoControlButton)
.Style = msoButtonCaption
.OnAction = "Test in "
.Caption = "Test Message "
.TooltipText = "fires Test in : " & ThisWorkbook.Name
End With
.Left = 500
.Top = 150
.Visible = True
End With
End Sub

'' end code normal module

Put this in a few workbooks, switch wb's and press the menu button.

Regards,
Peter T