You don't need everything

Not many of my friends like my Excel post. Just like my previous post, customize the right click menu may be useful to certain group of people, but not everyone. I post that just want my friends to know, there is such function, if you interested, you may find out more.

You bought a map, but you won't memorize every places on every page (unless you are taxi driver). You may just look up for the location of the place you want. You bought a dictionary, but you won't memorize every words from A to Z (unless you are taking TOEFL). You may just look up for the words that you want.Information are available every place and they keep changing. There is really no need to push yourself to know everything, to be good at everything. It will just make yourself tired. Just like you are going to eat Buffet, you don't need to force yourself eat everything, just pick a few that you like, then you are done.

There are many investment strategies, you don't need to be expert on every techniques. You don't need to be Warren Buffett to get rich. You just need to know roughly what strategies are available, pick up what suit you, then follow it.

Live is not about trying everything, but, be yourself.

Customize Excel Right Click Menu

You can change the "right click menu" or "Popup menu" or "Context Menu" in Microsoft Excel. For example, remove the "Pick from drop-down list...", "Add Watch" and "Hyperlink..." or even add in Macro you want. You may choose your own Icon from the available list. :)

Google "Excel context menu" and you can learn from there. But this required some programming.

Below is my version of menu. I set up for (a) General right click (b) Query (c) Pivot Table.

Private Sub ResetContentMenu()
Dim cBut As CommandBarControl
Dim myControl As CommandBarControl

    On Error Resume Next
      'Format right click content - General
       With Application.CommandBars("Cell")
            .Reset
            .Controls("Pick from drop-down list...").Visible = False
            .Controls("Add Watch").Visible = False
            .Controls("Create List...").Visible = False
            .Controls("Hyperlink...").Visible = False
            .Controls("Look up...").Visible = False
            .Controls.Add Type:=msoControlButton, ID:=443
            .Controls.Add Type:=msoControlButton, ID:=458
            .Controls.Add Type:=msoControlButton, ID:=852
          Call AddMacroContentMenu("Cell", 557, "PERSONAL.XLSB!RemoveFormula")
            .Controls(.Controls.Count).BeginGroup = True
          Call AddMacroContentMenu("Cell", 578, "PERSONAL.XLSB!TrimColumn")
          Call AddMacroContentMenu("Cell", 440, "PERSONAL.XLSB!AutoFit")
          Call AddMacroContentMenu("Cell", 578, "PERSONAL.XLSB!TrimColumn")
          Call AddMacroContentMenu("Cell", 630, "PERSONAL.XLSB!ConvertDate")
          Call AddMacroContentMenu("Cell", 288, "PERSONAL.XLSB!CheckActiveCellInfo")
          Call AddMacroContentMenu("Cell", 222, "PERSONAL.XLSB!FilterString")
            .Controls.Add Type:=msoControlButton, ID:=293
            .Controls(.Controls.Count).BeginGroup = True
            .Controls.Add Type:=msoControlButton, ID:=294
            .Controls.Add Type:=msoControlButton, ID:=296
            .Controls.Add Type:=msoControlButton, ID:=297
      End With

       With Application.CommandBars("Query")
            .Reset
            Set myControl = .Controls.Add(Type:=msoControlButton, ID:=443)
            myControl.BeginGroup = True
            .Controls.Add Type:=msoControlButton, ID:=458
          Call AddMacroContentMenu("Query", 440, "PERSONAL.XLSB!AutoFit")
          Call AddMacroContentMenu("Query", 288, "PERSONAL.XLSB!CheckActiveCellInfo")
          Call AddMacroContentMenu("Query", 222, "PERSONAL.XLSB!FilterString")
      End With
      
      'Format right click content - Pivot Table
       With Application.CommandBars("PivotTable Context Menu")
          .Reset
       Set myControl = .Controls.Add(Type:=msoControlButton, ID:=443)
       myControl.BeginGroup = True
       
      End With
      Set myControl = Nothing

On Error GoTo 0
End Sub

Sub AddMacroContentMenu(myComBarName As String, myFaceID As Double, myMacro As String)
'Purpose: link to "SetupCommandBar" macro
Dim cmdButton As CommandBarButton
Dim strMyAction As String

Set cmdButton = Application.CommandBars(myComBarName).Controls.Add(Type:=msoControlButton)
strMyAction = Right(myMacro, Len(myMacro) - InStr(1, myMacro, "!"))

With cmdButton
   .FaceId = myFaceID
   .Caption = strMyAction
   .OnAction = myMacro
   .TooltipText = strMyAction
   .Style = msoButtonIconAndWrapCaption
End With

Set cmdButton = Nothing

End Sub