Adding and Deleting custom commandbars

[Guest]

The code I have currently in an addin is below. What I am finding is that
sometimes Excel doesn't close properly or for any other reason the adding of
these command bars becomes duplicated. I have had to remove up to twenty on
some of my staffs computers. How can I write this so that it checks to see
if the command bar exists and if it does to not add it again?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar"). _
Controls("Tools").Controls("Import DR Data File").Delete
Application.CommandBars("Worksheet Menu Bar"). _
Controls("Tools").Controls("Daily Revenue Reset").Delete
End Sub

Private Sub Workbook_Open()

Set newmenuitem = Application.CommandBars _
("Worksheet Menu Bar").Controls("Tools").Controls.Add
With newmenuitem
.Caption = "Import DR Data File"
.FaceId = 312
.BeginGroup = True
.OnAction = "MorningReport"
End With
Set newmenuitem = Application.CommandBars _
("Worksheet Menu Bar").Controls("Tools").Controls.Add

With newmenuitem
.Caption = "Daily Revenue Reset"
.FaceId = 1678
.BeginGroup = False
.OnAction = "reset_morning_reports"
End With

End Sub

 

[Robert Bruce]

The code I have currently in an addin is below. What I am finding is that
sometimes Excel doesn't close properly or for any other reason the adding
of
these command bars becomes duplicated. I have had to remove up to twenty
on
some of my staffs computers. How can I write this so that it checks to
see
if the command bar exists and if it does to not add it again?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar"). _
Controls("Tools").Controls("Import DR Data File").Delete
Application.CommandBars("Worksheet Menu Bar"). _
Controls("Tools").Controls("Daily Revenue Reset").Delete
End Sub

Private Sub Workbook_Open()

Set newmenuitem = Application.CommandBars _
("Worksheet Menu Bar").Controls("Tools").Controls.Add

Try calling Workbook_BeforeClose(False) as the first line of your
Workbook_Open.

Rob

 

[Guest]

Why not delete it first just in case it exists?

On Error Resume Next
Application.CommandBars("mybar").Delete

Mike

 

[JE McGimpsey]

One way:

Note that you're adding custom Controls, not custom CommandBars...

First, make sure you set the Temporary argument to True in the
..Controls.Add method.


Second, you can delete leftover controls before adding their
replacements with something like:

With Application.CommandBars("Worksheet Menu Bar")
On Error Resume Next
.FindControl(Tag:="ImportDRControl").Delete
.FindControl(Tag:="DailyRevenueReset").Delete
On Error GoTo 0
With .Controls
With .Add(Type:=msoControlButton, Temporary:=True)
.Caption = "Import DR Data File"
.FaceId = 312
.BeginGroup = True
.OnAction = "MorningReport"
.Tag = "ImportDRControl"
End With
With .Add(Type:=msoControlButton, Temporary:=True)
.Caption = "Daily Revenue Reset"
.FaceId = 1678
.BeginGroup = False
.OnAction = "reset_morning_reports"
.Tag = "DailyRevenueReset"
End With
End With
End With

Third - if it's possible that the user might have a custom menu bar, you
might want to substitute

Application.CommandBars.ActiveMenuBar

for

Application.CommandBars("Worksheet Menu Bar")