Worksheet Change Events
 

I am monitoring a column on a worksheet for changes - using the change
event. I check that the column is the correct one and use the value of the
cell to decide on the action to take. Code as follows

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And Target.Value 0 Then
' take some action
End If
End Sub

All works well until I select a Range of cells, then the Target.Value throws
an error.

My questions are

1. How can I test if the user has chosen more than one cell ?

2. How can I iterate through a multi-cell selection and act on each value in
turn?

Many thanks

--
Cheers
Nigel

Worksheet Change Events
 

Hi Nigel,

Try something like:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim rCell As Range

Set Rng = Me.Columns("B") '<<==== CHANGE
Set Rng = Intersect(Target, Rng)

If Not Rng Is Nothing Then

For Each rCell In Rng.Cells
'do something, e.g:
MsgBox rCell.Address(0, 0)
Next rCell
End If

End Sub
'<<=============


---
Regards,
Norman


"Nigel" wrote in message
...
I am monitoring a column on a worksheet for changes - using the change
event. I check that the column is the correct one and use the value of the
cell to decide on the action to take. Code as follows

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And Target.Value 0 Then
' take some action
End If
End Sub

All works well until I select a Range of cells, then the Target.Value
throws an error.

My questions are

1. How can I test if the user has chosen more than one cell ?

2. How can I iterate through a multi-cell selection and act on each value
in turn?

Many thanks

--
Cheers
Nigel

Worksheet Change Events
 

Many thanks, works fine now

--
Cheers
Nigel



"Norman Jones" wrote in message
...
Hi Nigel,

Try something like:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim rCell As Range

Set Rng = Me.Columns("B") '<<==== CHANGE
Set Rng = Intersect(Target, Rng)

If Not Rng Is Nothing Then

For Each rCell In Rng.Cells
'do something, e.g:
MsgBox rCell.Address(0, 0)
Next rCell
End If

End Sub
'<<=============


---
Regards,
Norman


"Nigel" wrote in message
...
I am monitoring a column on a worksheet for changes - using the change
event. I check that the column is the correct one and use the value of
the cell to decide on the action to take. Code as follows

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And Target.Value 0 Then
' take some action
End If
End Sub

All works well until I select a Range of cells, then the Target.Value
throws an error.

My questions are

1. How can I test if the user has chosen more than one cell ?

2. How can I iterate through a multi-cell selection and act on each value
in turn?

Many thanks

--
Cheers
Nigel