Context Menu 2007/2010

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

There are two context menus which pop up when a cell is right clicked
in excel 2007 and 2010. The topmost appears to be a cell formatting
menu. I do not wish to have users edit the formatting in the cells, so
I would like to disable that menu.

I have been able to delete all the menu items in the second pop-up
menu, but how to do it in the first has eluded me.

I would appreciate any help offered.

Thank you.
 
There are two context menus which pop up when a cell is right clicked
in excel 2007 and 2010. The topmost appears to be a cell formatting
menu. I do not wish to have users edit the formatting in the cells, so
I would like to disable that menu.

I have been able to delete all the menu items in the second pop-up
menu, but how to do it in the first has eluded me.

I would appreciate any help offered.

Thank you.
Click to expand...

Found it, but the syntax does not seem logical...

Application.ShowMenuFloaties = True to hide the Mini Toolbar
Application.ShowMenuFloaties = False to show it.
 
(e-mail address removed) brought next idea :
Found it, but the syntax does not seem logical...

Application.ShowMenuFloaties = True to hide the Mini Toolbar
Application.ShowMenuFloaties = False to show it.
Click to expand...

Jim's reply handles the "floaties" menus. Not sure why you'd go to the
trouble of deleting all the menus on the "Cell" popup when you could
just set its 'Enabled' property to "False" to prevent it from showing.
 
Note that setting...

Application.Commandbars("Cell").Enabled = False

also disables the 'floaties' menus.
 
(e-mail address removed) brought next idea :

Jim's reply handles the "floaties" menus. Not sure why you'd go to the
trouble of deleting all the menus on the "Cell" popup when you could
just set its 'Enabled' property to "False" to prevent it from showing.
Click to expand...

I knew after I posted this question that I hadn't provided enough
information. My error.

I suppose if I'd been more specific by mentioning I had created menu
items for the right click event I would have answered that question
before it was asked.

Enabled = False also turns off my menu.
 
I knew after I posted this question that I hadn't provided enough
information. My error.

I suppose if I'd been more specific by mentioning I had created menu
items for the right click event I would have answered that question
before it was asked.

Enabled = False also turns off my menu.
Click to expand...

No you did not create menus for the right click event! What you're
saying is you 'modified' the "Cell" menu with your own menus. IOW, you
did not create your own right-click menu, which is a more appropriate
approach than modifying built-in menus when you don't intend to use any
of them. It's also easier to do than all the work you had to go through
to fenagal doing it the way you have.

To replace the built-in "Cell" menu with your own popup you merely need
to put code in the Workbook_SheetBeforeRightClick event to display your
menu and cancel Excel's. No fuss, no muss, no messing around with
modifying built-in menus or deleting menu items, nothing to reset or
undo when your wkb closes! (You do realize the built-in menus remain in
whatever state you put them when your wkb modified them, -right?) Now,
doesn't that seem easier to you?
 
No you did not create menus for the right click event! What you're
saying is you 'modified' the "Cell" menu with your own menus. IOW, you
did not create your own right-click menu, which is a more appropriate
approach than modifying built-in menus when you don't intend to use any
of them. It's also easier to do than all the work you had to go through
to fenagal doing it the way you have.

To replace the built-in "Cell" menu with your own popup you merely need
to put code in the Workbook_SheetBeforeRightClick event to display your
menu and cancel Excel's. No fuss, no muss, no messing around with
modifying built-in menus or deleting menu items, nothing to reset or
undo when your wkb closes! (You do realize the built-in menus remain in
whatever state you put them when your wkb modified them, -right?) Now,
doesn't that seem easier to you?
Click to expand...

No, actually...I didn't consider that. I had reset the menu in an
'auto' module instead. I'll give your advice a look. Thanks.

You could steer me in the right direction with a code snippet that
demonstrates a popup menu.
 
No, actually...I didn't consider that. I had reset the menu in an
'auto' module instead. I'll give your advice a look. Thanks.

You could steer me in the right direction with a code snippet that
demonstrates a popup menu.
Click to expand...

Ok.., here's some sample code that creates a custom popup commandbar
and adds menuitems to it. Following is how to implement it when your
workbook opens, AND how to use it when a cell on any sheet is
right-clicked.

===
In a standard module...

Sub MakeMyPopupMenu()
'Always delete in case it already exists
On Error Resume Next
CommandBars("MyPopupMenu").Delete
On Error GoTo 0

'Add a menu bar
With CommandBars.Add(Name:="MyPopupMenu", Position:=msoBarPopup)
'Add menuitems
With .Controls.Add(Type:=msoControlButton)
.Caption = "Menuitem&1" 'the menuitem
.OnAction = "RunMenuitem1" 'the procedure to run
.TooltipText = "Do this"
'.BeginGroup = True
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "Menuitem&2" 'the menuitem
.OnAction = "RunMenuitem2" 'the procedure to run
.TooltipText = "Do that"
'.BeginGroup = True
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "Menuitem&3" 'the menuitem
.OnAction = "RunMenuitem3" 'the procedure to run
.TooltipText = "Do other stuff"
.BeginGroup = True
End With
End With '//CommandBars.Add
End Sub

Sub RunMenuitem1()
MsgBox "You clicked Menuitem1! "
End Sub

Sub RunMenuitem2()
MsgBox "You clicked Menuitem2! "
End Sub

Sub RunMenuitem3()
MsgBox "You clicked Menuitem3! "
End Sub


===
In the Workbook_Open event
-OR- Sub Auto_Open in a standard module...

Call MakeMyPopupMenu

===
In the Worksheet_BeforeRightClick event...

Commandbars("MyPopupMenu").ShowPopup
Cancel = True '//cancel Excel's menus

Good luck!
 
Ok.., here's some sample code that creates a custom popup commandbar
and adds menuitems to it. Following is how to implement it when your
workbook opens, AND how to use it when a cell on any sheet is
right-clicked.
Click to expand...
<Brilliant Solution Snipped>

WOW!!!

What an elegant way to simplify my clunky method. I had no idea it
could be so easy. You turned my Cessna into an F14.

I sincerely thank you for your help.
 
(e-mail address removed) explained on 1/23/2011 :
WOW!!!

What an elegant way to simplify my clunky method. I had no idea it
could be so easy. You turned my Cessna into an F14.

I sincerely thank you for your help.
Click to expand...

You're welcome!
Thanks for the feedback, ..always appreciated!
 
Just thought I'd add some more info you might find helpful...

Originally, I stated that you could use the
Workbook_SheetBeforeRightClick event to display your custom popup menu,
but that's not what I posted in my code example. What I posted only
works in the Worksheet_BeforeRightClick event for each sheet you want
to use the menu on.

To have the menu run in the Workbook_SheetBeforeRightClick event
(behind ThisWorkbook) you need to establish an object variable that
holds a ref to the popup menu (actually it's a CommandBar), as follows:

In the standard module containing Sub MakeMyPopupMenu():

===
In the General (Declarations) section...
Public MyPopup As CommandBar

===
In Sub MakeMyPopupMenu()...
change this:
With CommandBars.Add(Name:="MyPopupMenu", Position:=msoBarPopup)

---
to this:
Set MyPopup = CommandBars.Add(Name:="MyPopupMenu", _
Position:=msoBarPopup)
With MyPopup
===

In the code module behind ThisWorkbook...

Select Workbook in the left side dropdown.
Select SheetBeforeRightClick in the right side dropdown.
Insert the following:
MyPopup.ShowPopup: Cancel = True

Comment out any code in the Worksheet_BeforeRightClick event of each
sheet.

Now your custom menu will appear on any sheet in the workbook. You can
still use it on a sheet by sheet basis by commenting the code out
behind ThisWorkbook and inserting it into the event behind any sheet
you want to show this menu on.

<FWIW>
I use one menu for all sheets in a multi-sheet project wkb, but
different menuitems appear depending on which wks is active. This
requires setting the menus up before displaying the popup, which is
managed in the project's events handler class module. This means that
there may be a dozen menuitems on the commandbar but only certain ones
are visible for a given sheet. -Saves having to make numerous
popups<g>!

While this may seem somewhat complex it's really quite simple. The
SheetBeforeRightClick event determines the codename of the sheet ref
passed to it and uses a Select Case construct to set up the menus. This
passes a string of ones and zeros (representing the visible state of
each menuitem) to Sub Set_PopupMenuState before showing the popup.
 
I forgot to mention that you need to destroy the object variable at
shutdown (before closing the wkb). This requires placing the following
code is either the ThisWorkbook, BeforeClose event
- OR -
the Auto_Close sub in a standard module (if used instead)

'Clean up menus
Set MyPopup = Nothing
 
I forgot to mention that you need to destroy the object variable at
shutdown (before closing the wkb). This requires placing the following
code is either the ThisWorkbook, BeforeClose event
- OR -
the Auto_Close sub in a standard module (if used instead)

'Clean up menus
Set MyPopup = Nothing
Click to expand...

So much information for me to digest, I completely missed this today.

Using Excel 2010, and it started acting really buggy...stopping code
in the middle of a procedure without reason. Only saying code
execution had been stopped...but the option to continue???

I then restarted my computer and tried again, and a menu I toyed with
yesterday with a list of sub menu items showed up in an unrelated
workbook.

SetMyPopup = Nothing

Got it.
 
===
In the Workbook_Open event
-OR- Sub Auto_Open in a standard module...

Call MakeMyPopupMenu

===
In the Worksheet_BeforeRightClick event...

Commandbars("MyPopupMenu").ShowPopup
Cancel = True '//cancel Excel's menus

Good luck!
Click to expand...


Is there a method for capturing the click event that would tell which
button was clicked, or set a variable before calling a procedure?

Dim Act As String, ActName as String, ActNum As Long
ActNum = 1
With .Controls.Add(Type:=msoControlPopup, before:=1)
ActEmp = "Actor" & ActNum
.Caption = "Actors"
ActNum = 1
Set CurrentCell = Range("Data!" & Act)
Do Until IsEmpty(CurrentCell)
If Left(CurrentCell, 4) = "Time" Then Set CurrentCell
= CurrentCell.Offset(1, 0)
If Left(CurrentCell, 4) = "Name" Then Set CurrentCell
= CurrentCell.Offset(1, 0)
ActName = CurrentCell.Value
With .Controls.Add(Type:=msoControlButton)


.OnAction = "Act" & ActNum

'This line I'd like to do two things;
'Set a variable, and then Call a function.
'If I can do that, I can eliminate 24 procedures.
'Otherwise I need to create 24 additional subs.


.FaceId = 79 + ActNum 'Alphabet starting with A
.Caption = ActName 'Person's name
Select Case ActNum 'Draws line after each 5
Case 6
.BeginGroup = True
Case 11
.BeginGroup = True
Case 16
.BeginGroup = True
Case 21
.BeginGroup = True
End Select
End With
Set CurrentCell = CurrentCell.Offset(1, 0)
ActNum = ActNum + 1
Loop
End With
 
(e-mail address removed) used his keyboard to write :
Maybe this will clear it up.

I'd like to use the same macro for all 25 possible button clicks, but
the variable would determine selections.

I have the single macro code worked out...I just need to send it a
variable from the button click in the Context menu you helped me with
before calling the procedure.
Click to expand...

Ok, to send variable values from a button click, you need to have a
button for every possible variable. I strongly suggest you DO NOT make
a menuitem for each possibility.

It sounds to me like you need to do some validation of the ActiveCell
at the time the menuitem is selected. This should be done in an
'EntryPoint' procedure, where your decision as to what to do is
evaluated and passed to the appropriate procedure with args.

So.., I'm saying use a single entry point menuitem to execute a
procedure that does the logic part for all possibilities, and then
passes the instruction via args to your processing sub/function. Does
this make sense?
 
It happens that (e-mail address removed) formulated :
Depending on whether or not there are entries in the cells which the
code checks, there can be up to 25 popup buttons created with the code
I posted.

I _could_ use one macro only if I could set a variable _then_ call the
procedure.

For example,

.OnAction ActNum = 5, RunSomeCode

The macro would then run the code using the ActNum variable to choose
different information from the spreadsheet.

Is that possible...Can I extract some information from the button
click event that I could use to set a variable? Like the tag
attribute?

I hope I'm clear, but I somehow doubt I am.
Click to expand...

Unfortunately .OnAction doesn't work that way. See my reply to your
next post. What you want is 1 menuitem that runs a proc that determines
what to do based on evaluation of the ActiveCell at the time the
menuitem is selected. That procedure would then pass args to your
process.

Example:
<Control>.OnAction = "EvaluateNextAction"

Sub EvaluateNextAction() 'EntryPoint
Dim Act As String, ActName as String, ActNum As Long
'Code to figure out values
'for each var goes here...

'Once the values are loaded, process them
Call ThisProcess Act, ActName, ActNum
End Sub

Sub ThisProcess(Act As String, ActName as String, ActNum As Long)
'Do your stuff
End Sub
 
So.., I'm saying use a single entry point menuitem to execute a
It does, but it's not where I'm headed.

http://tinyurl.com/66ah47u

In the picture of the menu the URL directs you to, if, say...Elmer
Fudd was to set the value of ActNum to 2, then run the macro, the
macro would then populate a base page with data.

That's where I'm headed.

So, in effect, the menuitem would need to perform two functions when
clicked...set the variable, and then call the procedure.

Is that possible?
Click to expand...

I saw where you're headed; -not recommending you continue that path!

Menuitems have 2 properties you can store values in: 'Tag' and
'Parameter'. Both these store string values by default, so any numeric
values will have to be converted with a function like CLng(), for
example. Excel will know which menuitem was selected.

So.., in your procedure you need to get the property value stored and
then run with it...

<Control>.OnAction = "MyProcess"

Sub MyProcess()
Dim myValue As String, myNum As Long
myValue = CommandBars.ActionControl.Tag
'or...
myNum = CLng(CommandBars.ActionControl.Tag)
'Do stuff based on myValue or myNum
End Sub
===

Optionally...

Choice1:
You could implement a VB[A] function named "CallByName" whereby you can
run a method and pass any args you want, but this would require putting
the procs inside a class so they are Public methods of the class. IMO,
this is way too complicated for what you're trying to do.

Given what you explained so far, I would use a single menuitem and let
the entry point procedure (or VBA) decide what to do.

Choice2:
Have a single menuitem display a userform with a ListBox that users can
scroll to select actions. Use the list item's Index property to
identify which item was clicked, and pass a parameter based on that.
The names can be read into the list from a worksheet, and any other
values you want to use can be put into additional columns of the
ListBox. (not all cols need be visible if all you want to show is a
list of names)
 
(e-mail address removed) presented the following explanation :
If I haven't said Thank You, I have now.

This just eliminated over 100 lines of code in 25 separate
routines...in fact eliminated 24 routines...poof! Sianara!

I'm sure you have your reasons that you find this method unacceptable,
and after a dozen years messing with excel vba for entertainment, it
never ceases to amaze me how many alternate and more simple approaches
there can be, but for me this is a thrilling revelation.

I just bought you a cyber beer.

I will be using it as such, and I cannot thank you enough for
simplifying my code.
Click to expand...

Glad I could help!

<FWIW>
I just remembered a project where I did something similar in a popup
menu, whereby I used a dropdown (msoControlDropdown) under a flyout
menuitem (msoControlPopup). I used the Index property to determine what
action to take based on the user's selection. This is similar to
holding a value in the Tag or Parameter props of multiple menuitems but
just takes up the space of a single item. I'm thinking you might
benefit having a shorter menu by replacing the list under each flyout
with a dropdown control. The same methodology is used to determine
which item was selected, just accessing different props:

Example:
<Control>.OnAction = "MyProcess"

Sub MyProcess
Dim vVal As Variant
vVal = CommandBars.ActionControl.List.ListIndex
If vVal = "Choose..." Then Beep: Exit Sub
'Do stuff based on vVal
End Sub

The dropdown was populated from a worksheet list using a For Each loop
that iterated each cell in the wks list range. The first item in the
list was "Choose...", which provided a means to exit the sub if that
was still the value when clicked. This means the rest of the list items
indexes started at 1. Arg values were stored in adjacent cells to the
list and so retrieving them was a simple task.

Optionally, you could store the list (and/or any parameters) in an
array.
 
Back
Top