execute a macro from cell click event

[pburk]

I am trying to execute a macro from a cell single or double click event,
dependent on a value of another cell. There are multiple cells and depending
on the value of a drop down within those cells, a different macro would
execute. Is this possible?
thank you in advance for any input.

 

[Gary''s Student]

Say we double click on A1 and want a macro called based upon the contents of
B1:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Set t = Target
Set a = Range("A1")
Set b = Range("B1")
If Intersect(t, a) Is Nothing Then Exit Sub
Cancel = True
If b.Value = 1 Then
Call macro1
Else
Call macro2
End If
End Sub

Sub macro1()
MsgBox ("1")
End Sub

Sub macro2()
MsgBox ("2")
End Sub



Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[pburk]

gary's student,
thank you for that input. worked just as advertised. i need to expand the
selection now. my range would be cells e12 through h12 and execute based on
cells o29 through r29, each executing a different macro. Would a "with"
statement be used to loop through?