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

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

Thanks very much, I will give this a try.

Harold



"jasontferrell" <(E-Mail Removed)> wrote in message
news:950b431b-4f61-45d8-adb9-(E-Mail Removed)...
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

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


"jasontferrell" <(E-Mail Removed)> wrote in message
news:950b431b-4f61-45d8-adb9-(E-Mail Removed)...
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