Updating Custom Toolbar/Menu

[belfourisbetter]

I provide monthly reports that are very long and full of data. I hav
created a custom menu/toolbar with macros associated with the variou
buttons that take the user to specific points in the file. The proble
is, I have found that when I update the toolbar (or the macro
associated with the buttons) and send the updates out, the other user
still see the old toolbar instead of the new one. We have tried havin
the other users delete the custom toolbar from the previous version o
the report before opening the new version, but it doesn't work. Wha
do I need to do to ensure that the people viewing my reports ar
getting the most up-to-date toolbar?

Thanks!!
BelfourIsBetter :confused

 

[Greg Koppel]

You need to attach the toolbar to the workbook (which you may have already
done) and then use Auto_open and Auto_close procedures to assign/unassign
the macros when the workbook is opened or closed. Also include a statement
to delete the toolbar from the users list of available toolbars.

HTH, Greg

 

[Jim Rech]

When a workbook with toolbar "xx" attached is opened in an instance of Excel
that already has an "xx" toolbar, the existing one blocks the new one. I
know you said that users deleted the old one but frankly I don't think so.
Try this: Have the users open Excel (not opening your app) and go into
View, Toolbars, Customize, Toolbars and select the toolbar and Delete it.
Then close Excel. And open it again and confirm the toolbar is gone. Then
ask them to open your app, making sure that they are opening the newest
version. They should get the right toolbar.

For the future the best course is not to use the "attach toolbar" technique.
The pros built a toolbar with code each time their auto-open code runs.
With code you can make the toolbar "temporary, so no matter what happens,
your toolbar does not get saved with the user's other toolbars.

The second best option is to add code to the deleted your toolbar each time
your app is closed. In Sub Auto_Close or the Workbook_Close routine add:

On Error Resume Next
Application.Commandbars("YourToolbarNameHere").Delete

This isn't fail-safe because sometimes your exit code will not be run due to
errors, etc., but most of the time it will so (most of the time) your
updated toolbar will not be blocked.

--
Jim Rech
Excel MVP

message |I provide monthly reports that are very long and full of data. I have
| created a custom menu/toolbar with macros associated with the various
| buttons that take the user to specific points in the file. The problem
| is, I have found that when I update the toolbar (or the macros
| associated with the buttons) and send the updates out, the other users
| still see the old toolbar instead of the new one. We have tried having
| the other users delete the custom toolbar from the previous version of
| the report before opening the new version, but it doesn't work. What
| do I need to do to ensure that the people viewing my reports are
| getting the most up-to-date toolbar?
|
| Thanks!!
| BelfourIsBetter
|
|
| ---
| Message posted
|

 

[belfourisbetter]

Thanks for the advice!!

If I was to make the toolbars "temporary" so that it doesn't get save
to the users computer, how would I do that? Is there specific code
can enter? (I'm not very code-savy. I've only had basic Offic
training and taught myself everything else.)

I really appreciate the help

 

[Jim Rech]

In order to have menu bars and toolbars that are temporary they have to be
created by a macro. One of the arguments of the Commandbars.Add method is
"temporary", which you would set to True. So you'd detach your attached
menu/toolbar and write code to create it dynamically. This code would have
be run when your workbook opens, calling it from Sub Auto_Open or
Workbook_Open.

Having encouraged you to consider doing menus/toolbar via code be aware
there is a learning curve and, if you're not going to be doing this too
often, it might not be worth the climb<g>.

There are a host of articles in the MS Knowledgebase, including this
"overview":

http://support.microsoft.com/default.aspx?scid=kb;en-us;166755&Product=xlw

Just to give you some simple code to demonstrate adding (and removing) a
menu to Excel's worksheet menu bar, you could look at this:

Sub AddOurMenuToWorksheetMenuBar()
Dim MyMenu As CommandBarPopup
RemoveOurMenuToWorksheetMenuBar
Set MyMenu = CommandBars("Worksheet menu bar").Controls. _
Add(Type:=msoControlPopup, Before:=6, Temporary:=True)
With MyMenu
.Caption = "MyMenu"
With .Controls.Add(msoControlButton)
.Caption = "Menu Item 1"
.OnAction = "Sub1"
End With
With .Controls.Add(msoControlButton)
.Caption = "Menu Item 2"
.OnAction = "Sub2"
End With
End With
End Sub


''You don't really have to run this since the Temporary argument was True
Sub RemoveOurMenuToWorksheetMenuBar()
On Error Resume Next
CommandBars("Worksheet menu bar").Controls("MyMenu").Delete
End Sub

Sub Sub1()
MsgBox "Sub 1 called"
End Sub

Sub Sub2()
MsgBox "Sub 2 called"
End Sub



--
Jim Rech
Excel MVP
message | Thanks for the advice!!
|
| If I was to make the toolbars "temporary" so that it doesn't get saved
| to the users computer, how would I do that? Is there specific code I
| can enter? (I'm not very code-savy. I've only had basic Office
| training and taught myself everything else.)
|
| I really appreciate the help!
|
|
| ---
| Message posted
|