Code Question
 

What do the following statements mean or do?

1) Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Order").Activate
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub

I know the above will execute when there is a change on the worksheet, but I
don't know what the part in the () does or means. I have also been unable
to locate in the HELP section anything to do with TARGET.xxxxxxx.

2) Application.EnableEvent = TRUE/FALSE

What does this command affect and why would it be used?

3) If Not Intersect(Target, Range("A13:B90")) Is Nothing Then
If Target.Column = 1 Then

All above programming appear in a macro I use, but I was given the code via
this forum from questions I had.


Thanks,
Les

Code Question
 

Les,

The code

Private Sub Worksheet_Change(ByVal Target As Range)

declares the event procedure that Excel will automatically execute when a
change is made to a cell on the worksheet. This can be a change made by the
user or by other VBA code. It is not executed if the value of a cell is
changed as a result of a calculation. Target is a reference to the cell(s)
that were changed.

If Target.Count 1 Then Exit Sub

This is poor coding for

If Target.Cells.Count 1 Then Exit Sub

This code will cause the procedure to terminate if more than one cell was
changed at a time (for example, selecting a range of cells and pressing the
DEL key).

If IsEmpty(Target.Value) Then Exit Sub

This code tests whether the Target cell that was changed is Empty. In other
words, if the user deleted the contents of the cell. If so, the procedure
exits. If the Target cell contains some value (it is not Empty), the code
continues executing.

Application.EnableEvent = TRUE/FALSE

The EnableEvents property tells Excel whether or not to execute event
procedures when an event occurs. And event is something that happens,
usually a user action. For example, changing the value of a cell is an
event. If there is an event procedure for that event (e.g., the
Worksheet_Change event), Excel will automatically execute that event
procedure. See www.cpearson.com/excel/events.htm for more information about
events.

If the EnableEvents property is True, Excel will automatically execute the
appropriate event procedure, if one exists. If this property is False, Excel
will not execute the event procedure. Setting the property to False is
usually done in a Change event to prevent loops. If you don't set
EnableEvents to False in a Change event, and that event procedure makes a
change, that would cause the Change event to run, which makes a change,
causing Change to run, which makes a change, causing Change to run, which
makes a change, causing Change to run, over and over again.. By setting
EnableEvents to False, you prevent the Change event from causing itself to
be called.

If Not Intersect(Target, Range("A13:B90")) Is Nothing Then
If Target.Column = 1 Then

The Intersect method tests whether the Target cell (the cell that was
changed) has any cells in common with the range A13:B90. If there are no
cells in common between Target and the range A13:B90, Intersect returns a
special value called Nothing. If there are cells in common (Target is within
A13:B90), "Not Intersect(Target,Range("A13:B90") Is Nothing" is True. In
this case (the two ranges overlap, having at least one cell in common), the
code tests whether Target, the cell that was changed, is in column 1 ("A").
If this is True, the code continues with code that follows "If
Target.Column = 1 Then".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"WLMPilot" wrote in message
...
What do the following statements mean or do?

1) Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Order").Activate
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub

I know the above will execute when there is a change on the worksheet, but
I
don't know what the part in the () does or means. I have also been unable
to locate in the HELP section anything to do with TARGET.xxxxxxx.

2) Application.EnableEvent = TRUE/FALSE

What does this command affect and why would it be used?

3) If Not Intersect(Target, Range("A13:B90")) Is Nothing Then
If Target.Column = 1 Then

All above programming appear in a macro I use, but I was given the code
via
this forum from questions I had.


Thanks,
Les

Code Question
 

Thank you for the time you took to explain the code. Very helpful!

Les

"Chip Pearson" wrote:

Les,

The code

Private Sub Worksheet_Change(ByVal Target As Range)

declares the event procedure that Excel will automatically execute when a
change is made to a cell on the worksheet. This can be a change made by the
user or by other VBA code. It is not executed if the value of a cell is
changed as a result of a calculation. Target is a reference to the cell(s)
that were changed.

If Target.Count 1 Then Exit Sub

This is poor coding for

If Target.Cells.Count 1 Then Exit Sub

This code will cause the procedure to terminate if more than one cell was
changed at a time (for example, selecting a range of cells and pressing the
DEL key).

If IsEmpty(Target.Value) Then Exit Sub

This code tests whether the Target cell that was changed is Empty. In other
words, if the user deleted the contents of the cell. If so, the procedure
exits. If the Target cell contains some value (it is not Empty), the code
continues executing.

Application.EnableEvent = TRUE/FALSE

The EnableEvents property tells Excel whether or not to execute event
procedures when an event occurs. And event is something that happens,
usually a user action. For example, changing the value of a cell is an
event. If there is an event procedure for that event (e.g., the
Worksheet_Change event), Excel will automatically execute that event
procedure. See www.cpearson.com/excel/events.htm for more information about
events.

If the EnableEvents property is True, Excel will automatically execute the
appropriate event procedure, if one exists. If this property is False, Excel
will not execute the event procedure. Setting the property to False is
usually done in a Change event to prevent loops. If you don't set
EnableEvents to False in a Change event, and that event procedure makes a
change, that would cause the Change event to run, which makes a change,
causing Change to run, which makes a change, causing Change to run, which
makes a change, causing Change to run, over and over again.. By setting
EnableEvents to False, you prevent the Change event from causing itself to
be called.

If Not Intersect(Target, Range("A13:B90")) Is Nothing Then
If Target.Column = 1 Then

The Intersect method tests whether the Target cell (the cell that was
changed) has any cells in common with the range A13:B90. If there are no
cells in common between Target and the range A13:B90, Intersect returns a
special value called Nothing. If there are cells in common (Target is within
A13:B90), "Not Intersect(Target,Range("A13:B90") Is Nothing" is True. In
this case (the two ranges overlap, having at least one cell in common), the
code tests whether Target, the cell that was changed, is in column 1 ("A").
If this is True, the code continues with code that follows "If
Target.Column = 1 Then".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"WLMPilot" wrote in message
...
What do the following statements mean or do?

1) Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Order").Activate
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub

I know the above will execute when there is a change on the worksheet, but
I
don't know what the part in the () does or means. I have also been unable
to locate in the HELP section anything to do with TARGET.xxxxxxx.

2) Application.EnableEvent = TRUE/FALSE

What does this command affect and why would it be used?

3) If Not Intersect(Target, Range("A13:B90")) Is Nothing Then
If Target.Column = 1 Then

All above programming appear in a macro I use, but I was given the code
via
this forum from questions I had.


Thanks,
Les