Help needed starting a macro
 

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

--
Message posted from http://www.ExcelForum.com

Help needed starting a macro
 

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.

--
Regards,
Tom Ogilvy

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

Thanks in advance!
Simon


---
Message posted from http://www.ExcelForum.com/

Help needed starting a macro
 

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("A1:D20"
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


--
Message posted from http://www.ExcelForum.com

Help needed starting a macro
 

Believe this will cause an error:

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

you can't intersect a range and a string.

--
Regards,
Tom Ogilvy


"chris" wrote in message
...
Use the Worksheet 's SelectionChange Event In your Project window
And do something like this

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

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
clicked
or selected by navigating to it using the arrow keys.....i.e if cell
is
selected in range xx:yy then run macro or when cell in range is
highlighted by arrow keys.im new to VBA so an idiots guide on how to
do
this would be very well apreciated!

Thanks in advance!
Simon


---
Message posted from http://www.ExcelForum.com/

Help needed starting a macro
 

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

--
Message posted from http://www.ExcelForum.com