Adding a Macro

[nickyc]

Hi guys,

Was hoping you could help me understand adding Macros to Menus. I've been giving the task to add a new macro to a sheet. The macro has to appear in the right-click menu. I've been playing around with this and basically I can see 5 instances of the Macro listed. Is there a way of resetting the menu before and after?

So here is the Macro I created which is in a seperate module

Sub TestFindReplace()
Sheets("Keywords").Select
    Columns("D:D").Select
    Selection.Replace What:="+", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Now in terms of amending the current sheet (there's currently 4 other macros), how do I go about doing this? Initially I essentially added a new entry (see below) and that screwed it up (kept adding a new instance of the Macro in the drop down). Am guessing it's something to do with resetting the menu?

Is there a better way of running the below? Can you confirm how to reset the menu so it doesn't keep showing a new instance of the Macro every time I run it.

Private Sub Workbook_Deactivate()
    On Error Resume Next
            With Application
                .CommandBars("Cell").Controls("MACRO1").Delete
                .CommandBars("Cell").Controls("MACRO2").Delete
                .CommandBars("Cell").Controls("MACRO5").Delete
                .CommandBars("Cell").Controls("MACRO4").Delete
            End With
    On Error GoTo 0
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim cBut1 As CommandBarButton
Dim cBut2 As CommandBarButton
Dim cBut3 As CommandBarButton
Dim cBut4 As CommandBarButton

    On Error Resume Next
        With Application
                
                .CommandBars("Cell").Controls("MACRO1").Delete
            Set cBut1 = .CommandBars("Cell").Controls.Add(Temporary:=True)
            
                .CommandBars("Cell").Controls("MACRO2").Delete
            Set cBut2 = .CommandBars("Cell").Controls.Add(Temporary:=True)
            
                .CommandBars("Cell").Controls("MACRO3").Delete
            Set cBut3 = .CommandBars("Cell").Controls.Add(Temporary:=True)
               
               .CommandBars("Cell").Controls("MACRO4").Delete
            Set cBut4 = .CommandBars("Cell").Controls.Add(Temporary:=True)
            
            
        End With
        
        With cBut1
           .Caption = "MACRO1"
           .Style = msoButtonCaption
           .OnAction = "MACRO1"
        End With
        
        With cBut2
           .Caption = "MACRO2"
           .Style = msoButtonCaption
           .OnAction = "MACRO2"
        End With
        
        With cBut3
           .Caption = "MACRO3"
           .Style = msoButtonCaption
           .OnAction = "MACRO3"
        End With
        
          With cBut4
           .Caption = "MACRO4"
           .Style = msoButtonCaption
           .OnAction = "MACRO4"
        End With
                
    On Error GoTo 0

End Sub

 

[nickyc]

Rather than ammending the original code, I created this:

Private Sub Workbook_Open()
Application.CommandBars("Cell").Reset

With Application.CommandBars("Cell")
With .Controls.Add(Type:=msoControlButton)
.Caption = "Remove Broad (Keyword Tab)"
.OnAction = "TestFindReplace"
End With
End With
End Sub