H
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
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