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
--
Thanks!
Shane W

"whylite" <(E-Mail Removed)> wrote in message
news:C7531FF8-CFAF-4958-8B58-(E-Mail Removed)...
> 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

Why not delete it first just in case it exists?

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

Mike

"whylite" wrote:

> 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
> --
> Thanks!
> Shane W

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")

In article <C7531FF8-CFAF-4958-8B58-(E-Mail Removed)>,
whylite <(E-Mail Removed)> wrote:

> 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