Iterating through an intersect range

[Laura McKittrick]

Would anyone have any idea how to get this to work:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Dim cel As Range

Set isect = Application.Intersect(Target, Range("Ticks"))
If isect Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
For Each cel In isect
<do something>
Next cel
Application.EnableEvents = True
End Sub

In short, isect is the subset of the user's selection that's part of
the legal range for this operation, and I want to iterate through it,
operating on each cell individually. Problem is, there's only one
cell object in the collection. So far I can't figure out how to get
at the rest of the cells.

Thanks in advance!
Laura

 

[Frank Kabel]

Hi Laura
this looks o.k. for me. What error did you get?
You may check your named range 'Ticks'

 

[Chip Pearson]

Laura,

Is the user changing all the cells at once (e.g., using
Ctrl+Enter)? If the user selects a range of cells, but changes
only one, the Change event will fire only for that one cell being
changed.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Laura]

No error at this stage. It just goes through the loop only once, even
when I select more than one cell.

 

[Laura]

I haven't given it to the users yet. I'm still developing it.

I wasn't aware of the need to use Ctrl+Enter, though. I'm not a big
Excel user -- just delving into this one application. Maybe that's
where my problem is...

 

[Frank Kabel]

Hi Laura
this event will only get triggered if you ENTER a value. You may change
the worksheet_change event to the selection_change event. The following
works for me:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim isect As Range
Dim cel As Range

Set isect = Application.Intersect(Target, Range("A1:B20"))
If isect Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
For Each cel In isect
MsgBox cel.Address
Next cel
Application.EnableEvents = True
End Sub

 

[Laura]

This raises a question, though...

How to test for keystrokes? Basically I'm trying to set this up so
that if the user enters anything, it's set to the correct characters.

I've seen SelectionChange used to toggle tick marks if the user simply
moved to a range, and I want to do something somewhat similar.
However, what I saw seemed to me to be too confusing for the user --
too easy to make inadvertent changes. I want the ticks to be entered
when the user types something rather than just moving to the cell.
What's typed doesn't matter, which is why I'm not using OnKey.

 

[Frank Kabel]

Hi Laura
but then your previous code should do. The evnt should get fired each
time the user changes a value (and in nearly all cased he just change
one value at a time)