Change Event (2002)

Topic

New Reply

I am using the Worksheet Change event for any cell that is modified in the worksheet. I have the following simple code in the event:

If target.value =

Reply | Quote

Replies

The idea would be to loop through all cells in the Target range, but testing for any cell in a worksheet is a rather tall order. You could do something like this:

Dim oCell As Range
For Each oCell In Intersect(Me.UsedRange, Target)
If oCell.Value = “” Then …
Next oCell

This limits the loop to those cells in the Target range that fall within what Excel considers to be the used range of the worksheet (referred to as “Me”).

Reply | Quote

Thank you Hans! The me.usedrange came in very handy. Legare’s solution would also work but the me.usedrange also allows me to update mutiples cells at once via ctrl+enter and still execute the code in the change event. Thanks again.

Reply | Quote

Thank you Hans! The me.usedrange came in very handy. Legare’s solution would also work but the me.usedrange also allows me to update mutiples cells at once via ctrl+enter and still execute the code in the change event. Thanks again.

Reply | Quote

The idea would be to loop through all cells in the Target range, but testing for any cell in a worksheet is a rather tall order. You could do something like this:

Dim oCell As Range
For Each oCell In Intersect(Me.UsedRange, Target)
If oCell.Value = “” Then …
Next oCell

This limits the loop to those cells in the Target range that fall within what Excel considers to be the used range of the worksheet (referred to as “Me”).

Reply | Quote

    If Target.Count > 1 Then Exit Sub

Reply | Quote

    If Target.Count > 1 Then Exit Sub

Reply | Quote