Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change by Val like an "If" statement
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change by Val like an "If" statement
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... -- Hope that helps. Vergel Adriano "David P." wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change by Val like an "If" statement
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 "Vergel Adriano" wrote: 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... -- Hope that helps. Vergel Adriano "David P." wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change by Val like an "If" statement
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 he 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 "David P." wrote: 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 "Vergel Adriano" wrote: 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... -- Hope that helps. Vergel Adriano "David P." wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
embedding "ISERROR" function into an "IF" statement | Excel Worksheet Functions | |||
How to change "automax" to "autosum"? (probablyl wrong terminology) | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
=IF(D13="PAID","YES","NO") Can I change fonts colour | Excel Discussion (Misc queries) |