Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change worksheet, two trigger cells on one worksheet, only one triggers correctly; using two If statements, do I need End If?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change worksheet, two trigger cells on one worksheet, only onetriggers correctly; using two If statements, do I need End If?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change worksheet, two trigger cells on one worksheet, only one triggers correctly; using two If statements, do I need End If?
Thanks very much, I will give this a try.
Harold "jasontferrell" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change worksheet, two trigger cells on one worksheet, only one triggers correctly; using two If statements, do I need End If?
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code does | Excel Programming | |||
trigger worksheet activate event in another worksheet | Excel Programming | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Copying Worksheet triggers Click event of combobox | Excel Programming | |||
Copying Worksheet triggers Click event of combobox on another worksheet | Excel Programming |