Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change() and Range question
If someone highlights a block of cells and presses "delete"
to delete all the data in those cells, Excel will pass all those changed cell addresses to "Worksheet_Change()" into the "Target" variable. My question is, how do I enumerate through all the cells contained in the "Target" range variable?? The "Target" variable seems to contain a single string of all the cell addresses that were changed, and I'm looking for an easy way to extract each cell address from the "Target" variable. I initially thought I could use the following code, but I was wrong: Public Sub Worksheet_Change (ByVal Target as Range) for i = 1 to Target.Count nextAddress = Target(i).Address(0,0) ' Get next changed address?? Next i End Sub Thank you! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change() and Range question
On Sat, 12 Dec 2009 15:32:19 -0700, "Robert Crandal"
wrote: If someone highlights a block of cells and presses "delete" to delete all the data in those cells, Excel will pass all those changed cell addresses to "Worksheet_Change()" into the "Target" variable. My question is, how do I enumerate through all the cells contained in the "Target" range variable?? The "Target" variable seems to contain a single string of all the cell addresses that were changed, and I'm looking for an easy way to extract each cell address from the "Target" variable. I initially thought I could use the following code, but I was wrong: Public Sub Worksheet_Change (ByVal Target as Range) for i = 1 to Target.Count nextAddress = Target(i).Address(0,0) ' Get next changed address?? Next i End Sub Thank you! Try this: Private Sub Worksheet_Change(ByVal Target As Range) For Each c In Target nextaddress = c.Address Next c End Sub Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change() and Range question
Yes, that works excellent. Just what I was looking for.
BTW, what would be the correct notation to just extract a SINGLE address? For example, what if I am only interested in the second cell address in the list?? thankx again "Lars-Åke Aspelin" wrote in message ... Try this: Private Sub Worksheet_Change(ByVal Target As Range) For Each c In Target nextaddress = c.Address Next c End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change() and Range question
Try this
Target(2).Address Hope this helps / Lars-Åke On Sat, 12 Dec 2009 16:11:15 -0700, "Robert Crandal" wrote: Yes, that works excellent. Just what I was looking for. BTW, what would be the correct notation to just extract a SINGLE address? For example, what if I am only interested in the second cell address in the list?? thankx again "Lars-Åke Aspelin" wrote in message .. . Try this: Private Sub Worksheet_Change(ByVal Target As Range) For Each c In Target nextaddress = c.Address Next c End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change() and range question | Excel Programming | |||
Worksheet_change question | Excel Programming | |||
worksheet_change question | Excel Programming | |||
Excel VBA .... Worksheet_Change(ByVal Target As Range) question | Excel Programming | |||
Worksheet_Change event question | Excel Programming |