Trying to Have specific cell values trigger multiple macros in VBA code-CodePudding

I am creating an inputs page on an excel document.

Cell B3- can have three values, each value triggers a different macro Cell B4- can have two values, each value triggers a different macro

I wrote the following code:

Sub worksheet_change(ByVal target As Excel.Range)
    If target.Cells.Count > 1 Then Exit Sub
    If IsNumeric(target) And target.Address = "$B$3" Then
        Select Case target.Value
        Case Is = 2: Class2
        Case Is = 3: Class3

        End Select
    End If
End Sub
Sub worksheet_change(ByVal target As Range)
Set target = Range("$B$4")
If target.Value = "yes" Then
 Call RetireeLife
End If
End Sub

However, I found out the worksheet_change can only be used once in a worksheet but I am unsure on how to combine the codes. Any help would be greatly appreciated.

CodePudding user response：

Depending on what you want to use, ElseIf or Cases, the following demonstrates both used in your worksheet_change:

Sub worksheet_change(ByVal target As Excel.Range)
    If target.Cells.Count > 1 Then Exit Sub
    If IsNumeric(target) And target.Address = "$B$3" Then
        Select Case target.Value
        Case Is = 2: Class2
        Case Is = 3: Class3
        Case Is = 4: YourOtherSub 'You did say 3 options :p
        Case Else 
              MsgBox("Not a correct value in B3")
              Exit Sub            
        End Select
    ElseIf target.Address = "$B$4" Then
        If target.Value =  "yes" Then RetireeLife
        ElseIf target.Value = "no" Then KeepLiving 'or whatever your other macro is called
        Else
            MsgBox("Not a correct value in B4")
            Exit Sub
        End If          
    End If
End Sub

Do note that the Exit Sub (apart from the first one) isn't necessary since you'll get out of the If's and exit the sub regardless when you get to those lines in the code. Just in case you'll append extra code, it's there already. If you want to use Select for the cell-choices, you can use Select for the target.Address and then check within the "$B$3" case if it's Numeric.

Hope this helps!

CodePudding user response：

Like this for example:

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
        Case "$B$3"
            Select Case Target.Value
                Case 2: Class2
                Case 3: Class3
            End Select
        Case "$B$4"
            If IsNumeric(Target.Value) Then RetireeLife
    End Select
End Sub