Hello,

I have VBA that adds items to my right-click menu upon opening the
spreadsheet, then deletes them upon closing. Unfortunately, I have
ended up with the items being repeated 5 times over. It currently is
not adding any more to this number, but I don't know how to get rid of
the extra items, or how to prevent this from happening in the future.
Any ideas? I think it may have occurred when I was creating a backup
of the current spreadsheet.

Private Sub Workbook_Open()
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "Hide Shapes"
.OnAction = ThisWorkbook.Name & "!HideShapes"
.BeginGroup = True
End With
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "Show Shapes"
.OnAction = ThisWorkbook.Name & "!ShowShapes"
.BeginGroup = True
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("Hide Shapes").Delete
Application.CommandBars("Cell").Controls("Show Shapes").Delete
End Sub

Thanks,

Carroll Rinehart

Go to the VBE immediate window and just keep issuing the delete

Application.CommandBars("Cell").Controls("Hide Shapes").Delete

etc.

To make it more resolute for future, try this code


Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Cell").Controls("Hide Shapes").Delete
Application.CommandBars("Cell").Controls("Show Shapes").Delete
On Error Goto 0

With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "Hide Shapes"
.OnAction = ThisWorkbook.Name & "!HideShapes"
.BeginGroup = True
End With
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "Show Shapes"
.OnAction = ThisWorkbook.Name & "!ShowShapes"
.BeginGroup = True
End With
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Carroll" wrote in message
ups.com...
Hello,

I have VBA that adds items to my right-click menu upon opening the
spreadsheet, then deletes them upon closing. Unfortunately, I have
ended up with the items being repeated 5 times over. It currently is
not adding any more to this number, but I don't know how to get rid of
the extra items, or how to prevent this from happening in the future.
Any ideas? I think it may have occurred when I was creating a backup
of the current spreadsheet.

Private Sub Workbook_Open()
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "Hide Shapes"
.OnAction = ThisWorkbook.Name & "!HideShapes"
.BeginGroup = True
End With
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "Show Shapes"
.OnAction = ThisWorkbook.Name & "!ShowShapes"
.BeginGroup = True
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("Hide Shapes").Delete
Application.CommandBars("Cell").Controls("Show Shapes").Delete
End Sub

Thanks,

Carroll Rinehart

Thanks Bob,

I really need to start using the VBE immediate window for testing and
running bits of code.

Thanks also for the improvements to the VBA.

Carroll

I found this code on some other site a long time ago and have since tweaked
it to suit my needs.

Instead of using Workbook_Open, try Worksheet_BeforeRightClick. You will
need to put this procedure in the code page for the worksheet instead of the
code page for ThisWorkbook.

You will notice that the CommandBars declaration now includes a .Tag
property. The FOR loop at the top makes use of the tag to find and delete
each custom CommandBar.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Dim icbc As Object
For Each icbc In Application.CommandBars("Cell").Controls
If icbc.Tag = "show" Or icbc.Tag = "hide" Then icbc.Delete
Next icbc

With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "Hide Shapes"
.OnAction = ThisWorkbook.Name & "!HideShapes"
.BeginGroup = True
.Tag = "hide"
End With
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.Caption = "Show Shapes"
.OnAction = ThisWorkbook.Name & "!ShowShapes"
.BeginGroup = True
.Tag = "show"
End With
End Sub