Help needed starting a macro

[Simon Lloyd]

I want too start a macro when a cell in a certain range is left clicke
or selected by navigating to it using the arrow keys.....i.e if cell i
selected in range xx:yy then run macro or when cell in range i
highlighted by arrow keys.im new to VBA so an idiots guide on how to d
this would be very well apreciated!

Thanks in advance!
Simo

 

[Tom Ogilvy]

Right click on the sheet tab and select view code.

In the resulting module, at the top, in the left dropdown, select Worksheet
and in the right dropdown, select SelectionChange

This puts in a declaration for the selectionchange event

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Target is a range reference to the cell selected by the user using the
methods you describe (or any other way they get there).

You can then test if Target is in your range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if target.count > 1 then exit sub
if not intersect(target, Range("A1:B50")) is nothing then
' selected cell is in the range A1:B50

End if
End Sub

If your code will select another cell inside that range, then you would need
to turn events off (and back on again when done) so you don't get recursive
calls to the event.

 

[Guest]

Use the Worksheet 's SelectionChange Event In your Project window
And do something like thi

Private Sub Worksheet_SelectionChange(ByVal Target As Range
Set MyRange = Range("A120"
Set isect = Application.Intersect(MyRange,(Target.Address)
If isect Is Nothing Then Exit Su
'>> Put what your code is here
End Su

anytime a cell is selected this code will fire but fill only Run your code if its within your range
----- Simon Lloyd > wrote: ----

I want too start a macro when a cell in a certain range is left clicke
or selected by navigating to it using the arrow keys.....i.e if cell i
selected in range xx:yy then run macro or when cell in range i
highlighted by arrow keys.im new to VBA so an idiots guide on how to d
this would be very well apreciated

Thanks in advance
Simo

 

[Tom Ogilvy]

Believe this will cause an error:

Set isect = Application.Intersect(MyRange,(Target.Address))

you can't intersect a range and a string.

 

[Simon Lloyd]

Hi guys at the bottom of the page someone has posted a solution to wha
i need, as Tom said the isect did cause an error as did the wor
"Address" so i took this out and DIM isect as Variant, and also had t
DIM myrange as range....it then worked perfect!

Thank