Change by Val like an "If" statement

[Guest]

I would like to do a Change by Value change event like you would an IF
statement in a formula. Here is what I have but I need to add more ifs:

Private Sub Worksheet_Change(ByVal TargetCell As Range)
If (Range("X4") = "A") Then
more code here...

(I want to add 6 more of these types of things like:
If (Range("X4") = "B") Then
more code here...etc.

Is it possible to do this and if so how and how do I end it? Thank you.

David P.

 

[Guest]

David,

You could use the Select Case statement rather than If-Then-Else statements.
For example:

Select Case Range("X4").Value
Case "A":
'do something
Case "B":
'do something else
Case Else:
'value is neither A nor B
End Select

Lookup the VBA Help for the "Select case statement" to see an explanation
and example...

 

[Guest]

Hi Vergel,

Thank you for the suggestion. Sounds much easier. I'm relatively
intermediate with VBA code so my question is:

1) What would be before that and what would be after it to end it - for
example do I need an "End Sub" after the "End Select"?

2) I am also gathering that if it is a private macro it is stored in a
different place than a regular macro? (I'm sure you're getting that I need
some guidance on the basics here, so thank you).

David

 

[Guest]

It isn't the 'Private' part that makes it so special in this case. When you
create a worksheet event handling piece of code such as you have there, it
goes into the worksheet's code module. Each sheet has it's own. What
happens with a given
Worksheet_Change()
routine is that when ANY cell on the sheet changes value, the event fires
and the value Target that's in the parameter for the routine is a mirror
image of the cell(s) that changed. So you can also use that as a test.

All Sub and Function routines always eventually end with an End Sub or End
Function statement, whether they are Private or not. Case statement always
begins with Select Case with identification as to what you are going to test,
and ends with the End Select statement.

To get to a worksheet's code module, easiest way is to right-click on the
sheet's name tab and choose [View Code] from the list that pops up. Then you
can cut and paste code into it, or choose Worksheet from the dropdown at
upper right and then Change from the event list. Excel will try to start a
Private Sub for _SelectionChange if you do that, and you can simply delete
the Private Sub ... End Sub stub that it auto-generates. Complete working
code using Vergel's example would look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("X4")
Case Is = "A"
'do something
Case Is = "B"
'do something else
'you can have many Case Is
'statements within a Select Case
'segment
Case Else
'value is none of the above
'either do nothing, or
'give error message or
'take other action
End Select
End Sub

Since you only seem to be interested in a change when X4 changes, you can
even test to see if the change took place (by operator action) in that
specific cell. But if you want to check X4 after any change on the sheet,
then leave out what I've added here:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$X$4" Then
Exit Sub ' some other cell changed, we don't care!
End If
Select Case Range("X4")
Case Is = "A"
'do something
Case Is = "B"
'do something else
'you can have many Case Is
'statements within a Select Case
'segment
Case Else
'value is none of the above
'either do nothing, or
'give error message or
'take other action
End Select
End Sub