ExcelBanter - General question about using "worksheet" when select from the VBA Editor?

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - General question about using "worksheet" when select from the VBA Editor? (https://www.excelbanter.com/excel-programming/318918-general-question-about-using-worksheet-when-select-vba-editor.html)

General question about using "worksheet" when select from the VBA Editor?

question about Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
This probably seems basic to you, but Im trying to create a
spreadsheet that would need to be updated as the user
changes/manipulates the worksheet. The only way I see to change
things as the user manipulates is to use the if else statement. Is
there a more general statement that can be used. I have tried
..selection. The for, while, case select dont work as I intended

When something is changed/moved/manipulated on that sheet does what
you have coded run from the very top all the time?

This is difficult to explain, but any advice is appreciated

Thanks

General question about using "worksheet" when select from the VBA Editor?

Hi Susan,

See Chip Pearsons notes on Event procedures at:

http://www.cpearson.com/excel/events.htm

---
Regards,
Norman

"Susan Hayes" wrote in message
...
question about Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
This probably seems basic to you, but Im trying to create a
spreadsheet that would need to be updated as the user
changes/manipulates the worksheet. The only way I see to change
things as the user manipulates is to use the if else statement. Is
there a more general statement that can be used. I have tried
.selection. The for, while, case select dont work as I intended

When something is changed/moved/manipulated on that sheet does what
you have coded run from the very top all the time?

This is difficult to explain, but any advice is appreciated

Thanks

General question about using "worksheet" when select from the VBA Editor?

Susan,

I am not really sure what you are asking, but a few thoughts.

Worksheet_SelectionChange is triggered when a cell is selected. If you want
to catch when a cell is changed, then you need the Worksheet_Change event.

In itself this has nothing to do with IF/Else, that just becomes a control
mechanism within the code, albeit there are others. Often you only want to
test for changes within a certain area of the worksheet and you can use the
intersect method for this

If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then

which is only true if the changed cell is in the range A1:H10.

If you want to test for it has been changed to a particular value, you can
use If/ElseIf/Else/Endif

If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
If .Value =2 Then
.Value = 100
ElseIf .Value = 3 Then
.Value = 1000
Else
.Value = "Error"
End If
End With
End If

which should be quite simple to follow. But you can also use Case statements

If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
Select Case .Value
Case 2: .Value = 100
Case 3: .Value = 1000
Case Else: .Value = "Error"
End Select
End With
End If

which is more elegant IMO.

Another thing to be wary of is stopping your changes re-firing the event.
This is done by adding
Application.EnableEvents = False
at the start, and re-set at the end.

Putting all this together, here is a simple example that checks a range for
a value (planetary in this case, and sets the cell colour accordingly)

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A3:H9")) Is Nothing Then
With Target
Select Case LCase(Target.Value)
Case "mars": .Interior.ColorIndex = 3 'Red
Case "moon": .Interior.ColorIndex = 5 'Blue
Case "sun": .Interior.ColorIndex = 6 'Yellow
Case "saturn": .Interior.ColorIndex = 10 'Green
Case "venus": .Interior.ColorIndex = 45 'orange
End Select
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub
--

HTH

RP
(remove nothere from the email address if mailing direct)

"Susan Hayes" wrote in message
...
question about Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
This probably seems basic to you, but Im trying to create a
spreadsheet that would need to be updated as the user
changes/manipulates the worksheet. The only way I see to change
things as the user manipulates is to use the if else statement. Is
there a more general statement that can be used. I have tried
.selection. The for, while, case select dont work as I intended

When something is changed/moved/manipulated on that sheet does what
you have coded run from the very top all the time?

This is difficult to explain, but any advice is appreciated

Thanks

General question about using "worksheet" when select from the

dear bob,

i read you informative reply to susain's query about tracking changes in an
execl file.

Actually i am working on a project where i have to maintain audit trail on a
web-based excel . Do you think that this intersect method will be helpful in
web based project's environment ?

i'll be greatfull if you can take out some time and reply to this query .
others are also equally welcome to give their opinions so that i can proceed
with my project.

thanks in advance
with kind regards

anugrah atreya
Softsolvers Technologies

"Bob Phillips" wrote:

Susan,

I am not really sure what you are asking, but a few thoughts.

Worksheet_SelectionChange is triggered when a cell is selected. If you want
to catch when a cell is changed, then you need the Worksheet_Change event.

In itself this has nothing to do with IF/Else, that just becomes a control
mechanism within the code, albeit there are others. Often you only want to
test for changes within a certain area of the worksheet and you can use the
intersect method for this

If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then

which is only true if the changed cell is in the range A1:H10.

If you want to test for it has been changed to a particular value, you can
use If/ElseIf/Else/Endif

If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
If .Value =2 Then
.Value = 100
ElseIf .Value = 3 Then
.Value = 1000
Else
.Value = "Error"
End If
End With
End If

which should be quite simple to follow. But you can also use Case statements

If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
Select Case .Value
Case 2: .Value = 100
Case 3: .Value = 1000
Case Else: .Value = "Error"
End Select
End With
End If

which is more elegant IMO.

Another thing to be wary of is stopping your changes re-firing the event.
This is done by adding
Application.EnableEvents = False
at the start, and re-set at the end.

Putting all this together, here is a simple example that checks a range for
a value (planetary in this case, and sets the cell colour accordingly)

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A3:H9")) Is Nothing Then
With Target
Select Case LCase(Target.Value)
Case "mars": .Interior.ColorIndex = 3 'Red
Case "moon": .Interior.ColorIndex = 5 'Blue
Case "sun": .Interior.ColorIndex = 6 'Yellow
Case "saturn": .Interior.ColorIndex = 10 'Green
Case "venus": .Interior.ColorIndex = 45 'orange
End Select
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub
--

HTH

RP
(remove nothere from the email address if mailing direct)

"Susan Hayes" wrote in message
...
question about Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
This probably seems basic to you, but Im trying to create a
spreadsheet that would need to be updated as the user
changes/manipulates the worksheet. The only way I see to change
things as the user manipulates is to use the if else statement. Is
there a more general statement that can be used. I have tried
.selection. The for, while, case select dont work as I intended

When something is changed/moved/manipulated on that sheet does what
you have coded run from the very top all the time?

This is difficult to explain, but any advice is appreciated

Thanks

General question about using "worksheet" when select from the

dear bob ,

i read your informative articale.
can you please advice that if i can use this same approach to generate audit
trail of changes made to any workbook in web-based excel environment.

thanks in advance.
with kind regards
anugrah atreya

"Bob Phillips" wrote:

Susan,

I am not really sure what you are asking, but a few thoughts.

Worksheet_SelectionChange is triggered when a cell is selected. If you want
to catch when a cell is changed, then you need the Worksheet_Change event.

In itself this has nothing to do with IF/Else, that just becomes a control
mechanism within the code, albeit there are others. Often you only want to
test for changes within a certain area of the worksheet and you can use the
intersect method for this

If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then

which is only true if the changed cell is in the range A1:H10.

If you want to test for it has been changed to a particular value, you can
use If/ElseIf/Else/Endif

If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
If .Value =2 Then
.Value = 100
ElseIf .Value = 3 Then
.Value = 1000
Else
.Value = "Error"
End If
End With
End If

which should be quite simple to follow. But you can also use Case statements

If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
Select Case .Value
Case 2: .Value = 100
Case 3: .Value = 1000
Case Else: .Value = "Error"
End Select
End With
End If

which is more elegant IMO.

Another thing to be wary of is stopping your changes re-firing the event.
This is done by adding
Application.EnableEvents = False
at the start, and re-set at the end.

Putting all this together, here is a simple example that checks a range for
a value (planetary in this case, and sets the cell colour accordingly)

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A3:H9")) Is Nothing Then
With Target
Select Case LCase(Target.Value)
Case "mars": .Interior.ColorIndex = 3 'Red
Case "moon": .Interior.ColorIndex = 5 'Blue
Case "sun": .Interior.ColorIndex = 6 'Yellow
Case "saturn": .Interior.ColorIndex = 10 'Green
Case "venus": .Interior.ColorIndex = 45 'orange
End Select
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub
--

HTH

RP
(remove nothere from the email address if mailing direct)

"Susan Hayes" wrote in message
...
question about Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
This probably seems basic to you, but Im trying to create a
spreadsheet that would need to be updated as the user
changes/manipulates the worksheet. The only way I see to change
things as the user manipulates is to use the if else statement. Is
there a more general statement that can be used. I have tried
.selection. The for, while, case select dont work as I intended

When something is changed/moved/manipulated on that sheet does what
you have coded run from the very top all the time?

This is difficult to explain, but any advice is appreciated

Thanks