Is it possible to have a macro run by clicking on a range or a single cell?

Corey....

Hello Corey,

Yes you can. The drawback is the macro must be placed in the event code
of each Worksheet where you want to use it.

Here is the code. MyRange can be a single cell or range. Change MyMacro
to the name of the macro you wish to execute.


Code:
--------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = $A2:$E100
If Not Application.Intersect(Target, MyRange) Is Nothing Then Call MyMacro
End Sub

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=561911

Corey,
Check out the SelectionChange event.
You get new selected range in the Target argument.

NickHK

"Corey" wrote in message
...
Is it possible to have a macro run by clicking on a range or a single
cell?

Corey....

Not necessarily, as there is the Workbook_SelectionChange.
And if you use Application level events, a single routine for the whole of
excel.
http://www.cpearson.com/excel/AppEvent.htm

NickHK

"Leith Ross" wrote
in message ...

Hello Corey,

Yes you can. The drawback is the macro must be placed in the event code
of each Worksheet where you want to use it.

Here is the code. MyRange can be a single cell or range. Change MyMacro
to the name of the macro you wish to execute.


Code:
--------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = $A2:$E100
If Not Application.Intersect(Target, MyRange) Is Nothing Then Call
MyMacro
End Sub

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=561911

Thanks guys,
As the sheet i placed the code in was part of a copy and paste new sheet
code, the code transfers to the other sheets also.
I am using it to bring up a msg box to warn that a formula is contained in
that cell selected. (Similar to protecting the cells)
It works great.

Regards

Corey

data validate - info is another way doing that



"Corey" skrev:

Thanks guys,
As the sheet i placed the code in was part of a copy and paste new sheet
code, the code transfers to the other sheets also.
I am using it to bring up a msg box to warn that a formula is contained in
that cell selected. (Similar to protecting the cells)
It works great.

Regards

Corey