Change worksheet, two trigger cells on one worksheet, only one triggers correctly; using two If stat

[Harold Good]

Hello, I have a worksheet with two cells (B4 and E173) on it that should act as triggers for VBA Change code to run. One changes the currency symbol of named values, the other hides columns that are not needed.

The code below works if only one of the If statements is present, but doesn't work correctly when both are present as shown below.

If I enter a $ symbol in B4, the first If statement catches it and correctly changes the currency symbol for the values, and then the execution goes to the second If statement, and exits at that Exit Sub.

But if I enter a 3 in E173, the first If statement catches it, and it then immediately exits at the first Exit Sub and executes nothing.

Yet if the first If and Case statments are not there, it correctly hides the columns when I enter a 3 into E173.

Can you help me figure out what the problem may be.

Thanks,
Harold

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Private Sub Worksheet_Change(ByVal Target As Range)
'Budget
Set t = Target
Dim symbbud As String
symbbud = Range("CurrencySymbolBudget").Value
On Error GoTo ErrHandler:
If Intersect(t, Range("B4")) Is Nothing Then Exit Sub
'ActiveSheet.Unprotect Password:=" "

Select Case Range("B4").Value
Case "$"
'this line does not use any spaces to separate the $ symbol from the number
Range("BudgOtherCurrencyCells").NumberFormat = symbbud & "#,##0;[red]" & symbbud & "-#,##0;0"
Case Else
'this line uses one space to separate the currency symbol from the number
Range("BudgOtherCurrencyCells").NumberFormat = symbbud & " #,##0;__[red]" & symbbud & "__-#,##0;0"
End Select

If Intersect(t, Range("E173")) Is Nothing Then Exit Sub

Select Case Range("E173").Value
Case "3"
Range("H2:U4").UnMerge
Columns("L:M").Select
Selection.EntireColumn.Hidden = True
Range("H2:U4").Merge
End Select

'ActiveSheet.Protect Password:=""

Exit Sub
ErrHandler:
End Sub

 

[jasontferrell]

The process needs to check both conditions before exiting the
subroutine. I think you can just change the initial "exit" check to
only process if the if is "true", as follows.


Private Sub Worksheet_Change(ByVal Target As Range)
'Budget
Set t = Target
Dim symbbud As String
symbbud = Range("CurrencySymbolBudget").Value
On Error GoTo ErrHandler:
If Not Intersect(t, Range("B4")) Is Nothing Then
    'ActiveSheet.Unprotect Password:=" "

    Select Case Range("B4").Value
    Case "$"
    'this line does not use any spaces to separate the $ symbol from
the number
        Range("BudgOtherCurrencyCells").NumberFormat = symbbud &
"#,##0;[red]" & symbbud & "-#,##0;0"
    Case Else
    'this line uses one space to separate the currency symbol from the
number
        Range("BudgOtherCurrencyCells").NumberFormat = symbbud & "
#,##0;__[red]" & symbbud & "__-#,##0;0"
    End Select
End If
If Not Intersect(t, Range("E173")) Is Nothing Then

    Select Case Range("E173").Value
    Case "3"
        Range("H2:U4").UnMerge
        Columns("L:M").Select
        Selection.EntireColumn.Hidden = True
        Range("H2:U4").Merge
    End Select
End If
'ActiveSheet.Protect Password:=""

Exit Sub
ErrHandler:
End Sub

 

[Harold Good]

Thanks very much, I will give this a try.

Harold



The process needs to check both conditions before exiting the
subroutine. I think you can just change the initial "exit" check to
only process if the if is "true", as follows.


Private Sub Worksheet_Change(ByVal Target As Range)
'Budget
Set t = Target
Dim symbbud As String
symbbud = Range("CurrencySymbolBudget").Value
On Error GoTo ErrHandler:
If Not Intersect(t, Range("B4")) Is Nothing Then
'ActiveSheet.Unprotect Password:=" "

Select Case Range("B4").Value
Case "$"
'this line does not use any spaces to separate the $ symbol from
the number
Range("BudgOtherCurrencyCells").NumberFormat = symbbud &
"#,##0;[red]" & symbbud & "-#,##0;0"
Case Else
'this line uses one space to separate the currency symbol from the
number
Range("BudgOtherCurrencyCells").NumberFormat = symbbud & "
#,##0;__[red]" & symbbud & "__-#,##0;0"
End Select
End If
If Not Intersect(t, Range("E173")) Is Nothing Then

Select Case Range("E173").Value
Case "3"
Range("H2:U4").UnMerge
Columns("L:M").Select
Selection.EntireColumn.Hidden = True
Range("H2:U4").Merge
End Select
End If
'ActiveSheet.Protect Password:=""

Exit Sub
ErrHandler:
End Sub

 

[Harold Good]

Thanks, it took me awhile to notice that you changed the If to If Not. Once
I saw that, it works perfectly! Thanks very much, Harold


The process needs to check both conditions before exiting the
subroutine. I think you can just change the initial "exit" check to
only process if the if is "true", as follows.


Private Sub Worksheet_Change(ByVal Target As Range)
'Budget
Set t = Target
Dim symbbud As String
symbbud = Range("CurrencySymbolBudget").Value
On Error GoTo ErrHandler:
If Not Intersect(t, Range("B4")) Is Nothing Then
'ActiveSheet.Unprotect Password:=" "

Select Case Range("B4").Value
Case "$"
'this line does not use any spaces to separate the $ symbol from
the number
Range("BudgOtherCurrencyCells").NumberFormat = symbbud &
"#,##0;[red]" & symbbud & "-#,##0;0"
Case Else
'this line uses one space to separate the currency symbol from the
number
Range("BudgOtherCurrencyCells").NumberFormat = symbbud & "
#,##0;__[red]" & symbbud & "__-#,##0;0"
End Select
End If
If Not Intersect(t, Range("E173")) Is Nothing Then

Select Case Range("E173").Value
Case "3"
Range("H2:U4").UnMerge
Columns("L:M").Select
Selection.EntireColumn.Hidden = True
Range("H2:U4").Merge
End Select
End If
'ActiveSheet.Protect Password:=""

Exit Sub
ErrHandler:
End Sub