Solved

Context Menu VBA / Excel

Posted on 2010-11-23
4
764 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

932 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now