adding a right click command?

[Guest]

I would like to add a feature where a popup box appears when a user
right-mouse clicks on any cell in the range a1:a20. Two options should be
available when the right mouse button is depressed within that range. Option
1 will launch one macro and Option 2 will launch another.

 

[Norman Jones]

Hi Matt,

Assume xl2k or later.

Add data validation to the required range, setting the list items to Option1
and Option2.

Then paste the following code :

'============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Range("D220") '<<==== CHANGE

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, rng) Is Nothing Then
If Target.Value = "Option1" Then
Call Macro1 '<<==== CHANGE
ElseIf Target.Value = "Option2" Then
Call Macro2 '<<==== CHANGE

End If
End If
End Sub
'<<============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

*******************************************
Right-click the worksheet's tab

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
*******************************************

Change the range and the macro names to suit your requirements.

 

[Guest]

Hmmm...I'm not getting a pop up when I right mouse click (other than the
standard menu. any suggestions?

 

[David McRitchie]

see Right Click Menus (Context Menus) in Excel
http://www.mvps.org/dmcritchie/excel/rightclick.htm

You would need to use worksheet deactivate and worksheet activate
instead of workbook close and open for changes to the menu.

some additional help with event macros
http://www.mvps.org/dmcritchie/excel/event.htm

Kind of think it is overkill, and that application.inputbox
would have served the purpose
http://www.mvps.org/dmcritchie/excel/inputbox.htm

 

[Guest]

David -

I like the concept, but I don't think this will work the way I want to
deploy it.
I would like the menu items to be available for this spreadsheet only and to
not require any users to run the script that will add the menu items.

Essentially, I have a spreadsheet that calculates values based on
information in cells A, B & C. Those are the only cells that are not locked
and the rest of the cells are protected. When a user right-clicks on (for
example) cell A3, the user will receive only the options to insert or delete.
Each of those options is linked to a macro that will unprotect the sheet,
run the appropriate steps, and reprotect the sheet.

On a separate note: Do you know a function that can fill a cell with a
formula based on the cells location?
Details (using a simplified formula):
E5 =if(B5<>0,C5*D5,"")
If the selected cell is anywhere on row 120, I want the macro to fill in
cell E120 with =if(B120<>0,C120*D120,""). I prefer not to use E5 as the
reference in case the row is deleted.

 

[David McRitchie]

Hi Matt,
Take a look at event macros
http://www.mvps.org/dmcritchie/excel/event.htm