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

[Susan Hayes]

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

 

[Norman Jones]

Hi Susan,

See Chip Pearsons notes on Event procedures at:

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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