Worksheet_Change with discontinuous cells
 

Hi,

I'm working on a commission calculation form. At present, all calculation
rows are hidden until they click "Calculate Commission" (presumably after
they enter all the information...). That unhides the appropriate rows and
shows the calculations. But if they change certain cells, the macro needs to
be run again (as the rows that are shown depend on the choices, and if they
change those choices, different rows may need to be shown/hidden).

There's probably a simpler way to do this, but what I'm thinking of now is
writing code under the worksheet_change event with the target being the
particular cells that would prompt a need for recalculation. Only those cells
aren't continguous. For example, they are
A11
A12
A13
B6
B19
E19
G19

Is there a way to make the target these multiple ranges?

Or if you have a suggestion of a better way, in general, to handle this,
please fire away! My programming experience is mostly in Access, so I'm
struggling with some of the Excel stuff.

Thanks!

Worksheet_Change with discontinuous cells
 

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A11:A13,B6,B19,E19,G19")) Is Nothing
Then
With Target
'do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This i

--
HTH

Bob Phillips

"JKG" wrote in message
...
Hi,

I'm working on a commission calculation form. At present, all calculation
rows are hidden until they click "Calculate Commission" (presumably after
they enter all the information...). That unhides the appropriate rows and
shows the calculations. But if they change certain cells, the macro needs
to
be run again (as the rows that are shown depend on the choices, and if
they
change those choices, different rows may need to be shown/hidden).

There's probably a simpler way to do this, but what I'm thinking of now is
writing code under the worksheet_change event with the target being the
particular cells that would prompt a need for recalculation. Only those
cells
aren't continguous. For example, they are
A11
A12
A13
B6
B19
E19
G19

Is there a way to make the target these multiple ranges?

Or if you have a suggestion of a better way, in general, to handle this,
please fire away! My programming experience is mostly in Access, so I'm
struggling with some of the Excel stuff.

Thanks!