Solved

Context Menu VBA / Excel

Posted on 2010-11-23
4
766 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:Delerium1978
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 34195718
Which line?
0
 

Author Comment

by:Delerium1978
ID: 34195747
Set JMButton = CommandBars("Cell").Controls.Add(Type:=msoControlButton)
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
ID: 34195772
Does this make any difference?
Set JMButton = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton)

Open in new window

0
 

Author Closing Comment

by:Delerium1978
ID: 34195818
Yes that works - i cant beleive i missed something so simple. Another pair of eyes always helps - thanks a lot :)

J
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question