msoControlButton is Multiplying - Need to Delete

[Devin]

I started using some code, but it seems to not be deleting all the controls upon exit. Since I have been using this for a little while, many copies of this control are popping up when I hit the application key while an entire column is selected. How do I need to adjust my DELETE code?

Thanks guys!!!!

Sub AddToCellMenu()
    Dim cbr As CommandBar
    For Each cbr In Application.CommandBars
        Debug.Print cbr.Name
        If LCase$(cbr.Name) = "cell" Then
            With cbr.Controls.Add(msoControlButton, Before:=4)
                .Caption = "UnHide All"
                .Tag = "UnHide All"
                .OnAction = "UnHideAll"
                .FaceId = 10
                .Enabled = True
                .Visible = True
            End With
            With Application.CommandBars(cbr.Index + 4).Controls.Add(msoControlButton, Before:=4)
                .Caption = "UnHide All"
                .Tag = "UnHide All"
                .OnAction = "UnHideAll"
                .FaceId = 10
                .Enabled = True
                .Visible = True
            End With
        End If
    Next cbr
End Sub


Sub DeleteFromCellMenu()
    Dim ContextMenu As CommandBar
    Dim ctrl As CommandBarControl
    Set ContextMenu = Application.CommandBars("Cell")
    For Each ctrl In ContextMenu.Controls
        If ctrl.Tag = "UnHide All" Then
        ctrl.Delete
        End If
    Next ctrl
End Sub

 

[Domenic]

Try...

[FONT=Arial][COLOR=darkblue]Sub[/COLOR] AddToCellMenu()
    [COLOR=darkblue]Dim[/COLOR] cbr [COLOR=darkblue]As[/COLOR] CommandBar
    [COLOR=darkblue]Call[/COLOR] DeleteFromCellMenu [COLOR=green]'in case controls weren't already deleted[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cbr [COLOR=darkblue]In[/COLOR] Application.CommandBars
        Debug.Print cbr.Name
        [COLOR=darkblue]If[/COLOR] LCase$(cbr.Name) = "cell" [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]With[/COLOR] cbr.Controls.Add(msoControlButton, Before:=4, Temporary:=True) [COLOR=green]'automatically deleted when app is closed[/COLOR]
                .Caption = "UnHide All"
                .Tag = "UnHide All"
                .OnAction = "UnHideAll"
                .FaceId = 10
                .Enabled = [COLOR=darkblue]True[/COLOR]
                .Visible = [COLOR=darkblue]True[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
            [COLOR=darkblue]With[/COLOR] Application.CommandBars(cbr.Index + 4).Controls.Add(msoControlButton, Before:=4, Temporary:=True) [COLOR=green]'automatically deleted when app is closed[/COLOR]
                .Caption = "UnHide All"
                .Tag = "UnHide All"
                .OnAction = "UnHideAll"
                .FaceId = 10
                .Enabled = [COLOR=darkblue]True[/COLOR]
                .Visible = [COLOR=darkblue]True[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] cbr
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

Hope this helps!