Old Toolbar won't go away

J

jfitzpat

I used the code below to create a floating toolbar for my spreadsheet. I had
previously tried a manual method of creating a custom toolbar but it did not
work. The new toolbar works but the old one still comes up when the users
open the file. I have deleted it and resaved the file many times but this
old toolbar seems to still be connect to the file -- how can I get rid of it?


Option Explicit

Public Const ToolBarName As String = "MyToolbarSR"
Sub Auto_Open()
Call CreateMenubar
End Sub
Sub Auto_Close()
Call RemoveMenubar
End Sub
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error GoTo 0
End Sub
Sub CreateMenubar()

Dim iCtr As Long

Dim MacNames As Variant
Dim CapNamess As Variant
Dim TipText As Variant

Call RemoveMenubar

MacNames = Array("Spell_Check")

CapNamess = Array("SR Spell Check")

TipText = Array("Use this button to Spell Check Status Report")

With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

For iCtr = LBound(MacNames) To UBound(MacNames)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNamess(iCtr)
.Style = msoButtonIconAndCaption
.FaceId = 71 + iCtr
.TooltipText = TipText(iCtr)
End With
Next iCtr
End With
End Sub
 
B

Bill Renaud

<<... the old one still comes up when the users open the file. I have
deleted it and resaved the file many times but this old toolbar seems to
still be connect to the file ...>>

Maybe you "attached" the old toolbar to the workbook in your experiments
somewhere along the line.
Right click anywhere on an Excel toolbar or in the toolbar area, then
choose the "Customize..." command at the bottom of the context menu.
On the Toolbars tab of the Customize dialog box, click on the
"Attach..." button. This will bring up another dialog box with 2 list
boxes in it - the one on the right ("Toolbars in workbook") will show if
you have any "attached" toolbars. Select any toolbar in this list box,
then click on the button in between the 2 list boxes, which should now
read "Delete".

Normally, you can just create a toolbar, assign macros to the buttons on
your toolbar, and "attach" it to the workbook using the Customize
command. Building and manipulating toolbars with VBA code is not really
required, except in the case where you need to change something
(OnAction, ToolTip, etc.) at runtime, or you need to delete a toolbar
when uninstalling an add-in. This has made life an order of magnitude
simpler for me! (Grin!)
 
D

Dave Peterson

Did you open the workbook with the attached toolbar and remove the attached
toolbar?

Tools|Customize|Attach button
and delete the toolbars that are attached

And don't forget to save the workbook in that state.
 
J

jfitzpat

thanks

Dave Peterson said:
Did you open the workbook with the attached toolbar and remove the attached
toolbar?

Tools|Customize|Attach button
and delete the toolbars that are attached

And don't forget to save the workbook in that state.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top