New Menu Items (XP)

[bandido]

I have a spreadsheet in which I have added two new menu items to the toolbar. I have assigned macros to them: one to print and the other to open a userform. All works well until I save and exit. When I reopen and try to use the new menu items, an error message appears stating that two instances of the workbook are open and I can proceed no further. To cure this, I have to reassign the macros and all appears ok until I exit and reopen when the same thing happens again.

Any suggestions?

[HansV]

If you need the toolbar buttons only in that spreadsheet, you should put them on a custom toolbar, attach the toolbar to the workbook and delete it each time the workbook is closed. See Pieterse's <!post=Distributing an Excel application with toolbars (5/95/97/2000/2002),200526>Distributing an Excel application with toolbars (5/95/97/2000/2002)<!/post>.

[pieterse]

You should know, that the menuitems are not added to a workbook but added to a file called Excel.xlb (or similar, name depends on version). The path to the file that contains the macro that gets fired when you click one of the menubuttons is hardcoded into that file. When you have the right file open and save-as to a new name, the link of the buttons is changed too.

What I suspect has happened i this:
- you had your file open (let me call that File0).
- you saved-as to a new location but with the same name (let us call that File1)
- you closed Excel.
- you opened Excel and loaded File0
- you clicked a menubutton, but that button points to File1 and so Excel tries to open that file.
Since there is already a file open by that name, excel gives you the error message you saw.

[pieterse]

Or use code to add the buttons at opening and remove them at closure of the workbook.

[bandido]

Thanks, Jan Karel. You divined my actions accurately. Thanks too, Hans for the link. I now know what to do (perhaps).

[bandido]

Would you be so kind as to provide an example of such code?

[stuck]

Code to remove a toolbar when a workbook closes:
(put it in the ThisWorkbook object )

<pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next 'In case Toolbar is absent
Application.CommandBars("your toolbar name").Delete
End Sub</pre>


As for adding a toolbar, I've not tried it but I'd guess you want something like:
<pre>Application.CommandBars("your toolbar name").Add</pre>

in the Workbook_Open event

stuck

[HansV]

The instruction Application.CommandBars("your toolbar name").Add won't work. To create a new toolbar, use

Application.CommandBars.Add "your toolbar name"

but if you attach the toolbar to the workbook, that is not necessary.

[stuck]

Thank you for setting the record straight, I should no better than stray off the edge of my map.

stuck

[HansV]

"no better"? Watch out for John Gray!

[stuck]

oops! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

stuck
PS I think I'll be OK, can't recall ever seeing John on this board

[bandido]

Thanks all for your input but my single, active brain cell appears particularly unreceptive to the advice given. I have tried all suggestions but I am still lacking a solution.

Here's what I am trying to do. On opening the workbook:

1# Remove normal toolbar

2#Display, in its place, 2 buttons - Print Sheet and Main Menu - Print Sheet runs a procedure "PrintSheet" and Main Menu runs a procedure "GoToMenu".

3# on exiting workbook, after saving changes, delete the buttons and reestablish the normal toolbar.

I have trawled through VBA help and Jan Karel's post but I cannot get this to work.
Any further help would be much appreciated.

[HansV]

I have attached a different approach. The custom toolbar has been created manually; it has been attached to the workbook using the Customize dialog.

Open the ThisWorkbook module to see the code that handles the toolbar. The Workbook_Open event positions the toolbar, the Workbook_Activate event shows the custom toolbar and hides the standard one; the Workbook_Deactivate event hides the custom toolbar and shows the standard one, and the Workbook_BeforeClose event deletes the custom toolbar.

[bandido]

Magnifique! Thanks, John, it does everything I want and so easy to modify. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

[JohnBF]

Here's some code that I cut and pasted from old code, that does some of what you want in hiding standard commandbars and creating a custom toolbar on the fly, and should give you a feel for the techniques. Since it's cut-&-paste, it may not run as posted. The "OnAction lines are calls to other routines.

Sub custToolBar()
Dim tempFETB As CommandBar
Application.CommandBars(2).Enabled = False
For Each tempFETB In CommandBars
If tempFETB.Name = "TbMyToolBar" Then _
Application.CommandBars("TbMyToolBar").Delete
Next tempFETB
Set tempFETB = Application.CommandBars.Add _
("TbMyToolBar", msoBarTop, False, True)
tempFETB.Protection = msoBarNoCustomize + msoBarNoMove
tempFETB.Controls.Add Type:=msoControlButton, Id:=3
tempFETB.Controls.Add Type:=msoControlButton, Id:=4
With tempFETB.Controls.Add(1)
.Style = msoButtonIconAndCaption
.BeginGroup = True
.FaceId = 41
.OnAction = "DoThis"
.Caption = " DoThis "
.TooltipText = " DoThis "
.Enabled = True
End With
With tempFETB.Controls.Add(1)
.Style = msoButtonIconAndCaption
.BeginGroup = True
.FaceId = 39
.OnAction = " DoThat "
.Caption = " DoThat "
.TooltipText = " DoThat "
.Enabled = True
End With
With tempFETB.Controls.Add(1, 2188)
.Style = msoButtonIconAndCaption
.BeginGroup = True
.Caption = "E-Mail me"
.TooltipText = "E-Mail me "
.OnAction = "EMailButton_Click"
.Enabled = True
End With
tempFETB.Visible = True
End Sub

Sub KillCustomToolBar()
Application.CommandBars(2).Enabled = True
Application.CommandBars("TbMyToolBar").Delete
End Sub