Context Menu VBA / Excel

Hi Guys

I'm trying to add a few menu items to my right click context menu in excel but im having a few problems - my current code is attached.

No matter how i tweak this code, it errors with "Object Variable or With Block variable not set".

Any ideas?

James
Dim JMButton As CommandBarButton
    Dim JMControl As CommandBarControl
    
    Application.CommandBars("Cell").Reset
   
    Set JMButton = CommandBars("Cell").Controls.Add(Type:=msoControlButton)
        With JMButton
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "JM_GridLines"
        .Caption = ""
        .TooltipText = "Removes gridlines and sets zoom to 85%"
        .Style = msoButtonIconAndCaption
        .FaceId = 485
    End With
   
   
    Set JMControl = CommandBars("Cell").Controls.Add(Type:=msoControlPopup, Temporary:=True)
    With JMControl
        .Caption = "&Misc"

        'Adds Menu Item - Checks selection for duplicates and highlights them
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Duplicate Check"
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "JM_Duplicates"
            .FaceId = 1098
        End With
        'Adds Menu Item - Checks selection for duplicates in single column
        'and highlights them and adds the word 'duplicate' to following column
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Duplicate Check - Single Column"
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "JM_DuplicatesSingle"
            .FaceId = 1098
        End With

   End With

Open in new window

Delerium1978Asked:
Who is Participating?
 
StephenJRCommented:
Does this make any difference?
Set JMButton = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton)

Open in new window

0
 
StephenJRCommented:
Which line?
0
 
Delerium1978Author Commented:
Set JMButton = CommandBars("Cell").Controls.Add(Type:=msoControlButton)
0
 
Delerium1978Author Commented:
Yes that works - i cant beleive i missed something so simple. Another pair of eyes always helps - thanks a lot :)

J
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.