Solved

Context Menu VBA / Excel

Posted on 2010-11-23
4
761 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
Comment Utility
Which line?
0
 

Author Comment

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

Accepted Solution

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

Open in new window

0
 

Author Closing Comment

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

J
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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,…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

772 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

10 Experts available now in Live!

Get 1:1 Help Now