Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
starting macro again | Excel Discussion (Misc queries) | |||
Starting macro | Excel Discussion (Misc queries) | |||
Starting A Macro | Excel Discussion (Misc queries) | |||
starting a macro at startup | Excel Discussion (Misc queries) | |||
Macro not starting | Excel Programming |