Right-Click Menu Items -- Duplicate Entries

[Carroll]

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(temporary:=True)
.Caption = "Hide Shapes"
.OnAction = ThisWorkbook.Name & "!HideShapes"
.BeginGroup = True
End With
With Application.CommandBars("Cell").Controls.Add(temporary:=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

 

[Bob Phillips]

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(temporary:=True)
.Caption = "Hide Shapes"
.OnAction = ThisWorkbook.Name & "!HideShapes"
.BeginGroup = True
End With
With Application.CommandBars("Cell").Controls.Add(temporary:=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]

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

 

[Guest]

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(temporary:=True)
.Caption = "Hide Shapes"
.OnAction = ThisWorkbook.Name & "!HideShapes"
.BeginGroup = True
.Tag = "hide"
End With
With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.Caption = "Show Shapes"
.OnAction = ThisWorkbook.Name & "!ShowShapes"
.BeginGroup = True
.Tag = "show"
End With
End Sub