|
Data Validation List Macro
Is there a way to run a macro that hides/unhides multiple worksheets based on
the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. |
Data Validation List Macro
Sub worksheet_change(ByVal target As Range)
Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. |
Data Validation List Macro
Joel - This worked great. Thank you!
Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. |
Data Validation List Macro
Change this one line
from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. |
Data Validation List Macro
Thank you for replying but I cannot get this to work. For one reason, my
example was wrong. The two cells are not consecutive...they are in cells A1 and A3 but not A2, so I don't want to use A1:A3. Also, I don't like the macro running when I don't want it to. I think it would it be easier to use a button macro (named "Continue") that the user selects after he/she has input values in cells A1 and A3. Then the macro needs to say something like: If (cell A1) = X and (cell A3 = Y), then hide these sheets and unhide these sheets. Can you help me with the syntax up until I include the sheets? "joel" wrote: Change this one line from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. |
Data Validation List Macro
something like this
Sub HideSheets() with sheets("Sheet1") If Not isect Is Nothing Then Select Case .Range("A1").Value Case "Account Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False end if Case "Order Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False end if Case "Billing Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True end if End Select End If end with End Sub "Lisa C." wrote: Thank you for replying but I cannot get this to work. For one reason, my example was wrong. The two cells are not consecutive...they are in cells A1 and A3 but not A2, so I don't want to use A1:A3. Also, I don't like the macro running when I don't want it to. I think it would it be easier to use a button macro (named "Continue") that the user selects after he/she has input values in cells A1 and A3. Then the macro needs to say something like: If (cell A1) = X and (cell A3 = Y), then hide these sheets and unhide these sheets. Can you help me with the syntax up until I include the sheets? "joel" wrote: Change this one line from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. |
Data Validation List Macro
Here is my exact code. I have assigned this as a macro to a button. When I
try to run it, I get the error "Object Required". Sub HideSheets() With Sheets("Choose Form") If Not isect Is Nothing Then Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End If End With End Sub "joel" wrote: something like this Sub HideSheets() with sheets("Sheet1") If Not isect Is Nothing Then Select Case .Range("A1").Value Case "Account Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False end if Case "Order Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False end if Case "Billing Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True end if End Select End If end with End Sub "Lisa C." wrote: Thank you for replying but I cannot get this to work. For one reason, my example was wrong. The two cells are not consecutive...they are in cells A1 and A3 but not A2, so I don't want to use A1:A3. Also, I don't like the macro running when I don't want it to. I think it would it be easier to use a button macro (named "Continue") that the user selects after he/she has input values in cells A1 and A3. Then the macro needs to say something like: If (cell A1) = X and (cell A3 = Y), then hide these sheets and unhide these sheets. Can you help me with the syntax up until I include the sheets? "joel" wrote: Change this one line from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. |
Data Validation List Macro
ou no longer have a TARGET which is used with the worksheet change method.
You also didn't need to perform a check of the intersection of the cell you wanted with the target. You left the check of ISECT which also isn't needed. the error was occuring because ISECT is not defined. So I removed the unecessary IF statment. Sub HideSheets() With Sheets("Choose Form") Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End With End Sub "Lisa C." wrote: Here is my exact code. I have assigned this as a macro to a button. When I try to run it, I get the error "Object Required". Sub HideSheets() With Sheets("Choose Form") If Not isect Is Nothing Then Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End If End With End Sub "joel" wrote: something like this Sub HideSheets() with sheets("Sheet1") If Not isect Is Nothing Then Select Case .Range("A1").Value Case "Account Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False end if Case "Order Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False end if Case "Billing Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True end if End Select End If end with End Sub "Lisa C." wrote: Thank you for replying but I cannot get this to work. For one reason, my example was wrong. The two cells are not consecutive...they are in cells A1 and A3 but not A2, so I don't want to use A1:A3. Also, I don't like the macro running when I don't want it to. I think it would it be easier to use a button macro (named "Continue") that the user selects after he/she has input values in cells A1 and A3. Then the macro needs to say something like: If (cell A1) = X and (cell A3 = Y), then hide these sheets and unhide these sheets. Can you help me with the syntax up until I include the sheets? "joel" wrote: Change this one line from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. |
Data Validation List Macro
And you don't need both the "select case" and the "if" statement.
joel wrote: ou no longer have a TARGET which is used with the worksheet change method. You also didn't need to perform a check of the intersection of the cell you wanted with the target. You left the check of ISECT which also isn't needed. the error was occuring because ISECT is not defined. So I removed the unecessary IF statment. Sub HideSheets() With Sheets("Choose Form") Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End With End Sub "Lisa C." wrote: Here is my exact code. I have assigned this as a macro to a button. When I try to run it, I get the error "Object Required". Sub HideSheets() With Sheets("Choose Form") If Not isect Is Nothing Then Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End If End With End Sub "joel" wrote: something like this Sub HideSheets() with sheets("Sheet1") If Not isect Is Nothing Then Select Case .Range("A1").Value Case "Account Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False end if Case "Order Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False end if Case "Billing Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True end if End Select End If end with End Sub "Lisa C." wrote: Thank you for replying but I cannot get this to work. For one reason, my example was wrong. The two cells are not consecutive...they are in cells A1 and A3 but not A2, so I don't want to use A1:A3. Also, I don't like the macro running when I don't want it to. I think it would it be easier to use a button macro (named "Continue") that the user selects after he/she has input values in cells A1 and A3. Then the macro needs to say something like: If (cell A1) = X and (cell A3 = Y), then hide these sheets and unhide these sheets. Can you help me with the syntax up until I include the sheets? "joel" wrote: Change this one line from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. -- Dave Peterson |
Data Validation List Macro
I changed the code and now I'm getting the error "Subscript out of range".
"joel" wrote: ou no longer have a TARGET which is used with the worksheet change method. You also didn't need to perform a check of the intersection of the cell you wanted with the target. You left the check of ISECT which also isn't needed. the error was occuring because ISECT is not defined. So I removed the unecessary IF statment. Sub HideSheets() With Sheets("Choose Form") Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End With End Sub "Lisa C." wrote: Here is my exact code. I have assigned this as a macro to a button. When I try to run it, I get the error "Object Required". Sub HideSheets() With Sheets("Choose Form") If Not isect Is Nothing Then Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End If End With End Sub "joel" wrote: something like this Sub HideSheets() with sheets("Sheet1") If Not isect Is Nothing Then Select Case .Range("A1").Value Case "Account Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False end if Case "Order Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False end if Case "Billing Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True end if End Select End If end with End Sub "Lisa C." wrote: Thank you for replying but I cannot get this to work. For one reason, my example was wrong. The two cells are not consecutive...they are in cells A1 and A3 but not A2, so I don't want to use A1:A3. Also, I don't like the macro running when I don't want it to. I think it would it be easier to use a button macro (named "Continue") that the user selects after he/she has input values in cells A1 and A3. Then the macro needs to say something like: If (cell A1) = X and (cell A3 = Y), then hide these sheets and unhide these sheets. Can you help me with the syntax up until I include the sheets? "joel" wrote: Change this one line from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. |
Data Validation List Macro
The subscript out of error usually means you have a difference in spelling.
With Sheets("Choose Form") Is the name of the sheet really "Choose Form"? I'd start there. If that's not the line with the problem, post back with the troublesome line. Lisa C. wrote: I changed the code and now I'm getting the error "Subscript out of range". "joel" wrote: ou no longer have a TARGET which is used with the worksheet change method. You also didn't need to perform a check of the intersection of the cell you wanted with the target. You left the check of ISECT which also isn't needed. the error was occuring because ISECT is not defined. So I removed the unecessary IF statment. Sub HideSheets() With Sheets("Choose Form") Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End With End Sub "Lisa C." wrote: Here is my exact code. I have assigned this as a macro to a button. When I try to run it, I get the error "Object Required". Sub HideSheets() With Sheets("Choose Form") If Not isect Is Nothing Then Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End If End With End Sub "joel" wrote: something like this Sub HideSheets() with sheets("Sheet1") If Not isect Is Nothing Then Select Case .Range("A1").Value Case "Account Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False end if Case "Order Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False end if Case "Billing Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True end if End Select End If end with End Sub "Lisa C." wrote: Thank you for replying but I cannot get this to work. For one reason, my example was wrong. The two cells are not consecutive...they are in cells A1 and A3 but not A2, so I don't want to use A1:A3. Also, I don't like the macro running when I don't want it to. I think it would it be easier to use a button macro (named "Continue") that the user selects after he/she has input values in cells A1 and A3. Then the macro needs to say something like: If (cell A1) = X and (cell A3 = Y), then hide these sheets and unhide these sheets. Can you help me with the syntax up until I include the sheets? "joel" wrote: Change this one line from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. -- Dave Peterson |
Data Validation List Macro
I found and corrected a worksheet name that was wrong. The code now runs and
works (hides/unhides sheets as expected) but a visual basic error window appears that has a red "X" and only says "400". "Dave Peterson" wrote: The subscript out of error usually means you have a difference in spelling. With Sheets("Choose Form") Is the name of the sheet really "Choose Form"? I'd start there. If that's not the line with the problem, post back with the troublesome line. Lisa C. wrote: I changed the code and now I'm getting the error "Subscript out of range". "joel" wrote: ou no longer have a TARGET which is used with the worksheet change method. You also didn't need to perform a check of the intersection of the cell you wanted with the target. You left the check of ISECT which also isn't needed. the error was occuring because ISECT is not defined. So I removed the unecessary IF statment. Sub HideSheets() With Sheets("Choose Form") Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End With End Sub "Lisa C." wrote: Here is my exact code. I have assigned this as a macro to a button. When I try to run it, I get the error "Object Required". Sub HideSheets() With Sheets("Choose Form") If Not isect Is Nothing Then Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End If End With End Sub "joel" wrote: something like this Sub HideSheets() with sheets("Sheet1") If Not isect Is Nothing Then Select Case .Range("A1").Value Case "Account Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False end if Case "Order Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False end if Case "Billing Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True end if End Select End If end with End Sub "Lisa C." wrote: Thank you for replying but I cannot get this to work. For one reason, my example was wrong. The two cells are not consecutive...they are in cells A1 and A3 but not A2, so I don't want to use A1:A3. Also, I don't like the macro running when I don't want it to. I think it would it be easier to use a button macro (named "Continue") that the user selects after he/she has input values in cells A1 and A3. Then the macro needs to say something like: If (cell A1) = X and (cell A3 = Y), then hide these sheets and unhide these sheets. Can you help me with the syntax up until I include the sheets? "joel" wrote: Change this one line from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. -- Dave Peterson |
Data Validation List Macro
I think it's time to post your current code.
And indicate the line that causes the error. Lisa C. wrote: I found and corrected a worksheet name that was wrong. The code now runs and works (hides/unhides sheets as expected) but a visual basic error window appears that has a red "X" and only says "400". "Dave Peterson" wrote: The subscript out of error usually means you have a difference in spelling. With Sheets("Choose Form") Is the name of the sheet really "Choose Form"? I'd start there. If that's not the line with the problem, post back with the troublesome line. Lisa C. wrote: I changed the code and now I'm getting the error "Subscript out of range". "joel" wrote: ou no longer have a TARGET which is used with the worksheet change method. You also didn't need to perform a check of the intersection of the cell you wanted with the target. You left the check of ISECT which also isn't needed. the error was occuring because ISECT is not defined. So I removed the unecessary IF statment. Sub HideSheets() With Sheets("Choose Form") Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End With End Sub "Lisa C." wrote: Here is my exact code. I have assigned this as a macro to a button. When I try to run it, I get the error "Object Required". Sub HideSheets() With Sheets("Choose Form") If Not isect Is Nothing Then Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End If End With End Sub "joel" wrote: something like this Sub HideSheets() with sheets("Sheet1") If Not isect Is Nothing Then Select Case .Range("A1").Value Case "Account Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False end if Case "Order Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False end if Case "Billing Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True end if End Select End If end with End Sub "Lisa C." wrote: Thank you for replying but I cannot get this to work. For one reason, my example was wrong. The two cells are not consecutive...they are in cells A1 and A3 but not A2, so I don't want to use A1:A3. Also, I don't like the macro running when I don't want it to. I think it would it be easier to use a button macro (named "Continue") that the user selects after he/she has input values in cells A1 and A3. Then the macro needs to say something like: If (cell A1) = X and (cell A3 = Y), then hide these sheets and unhide these sheets. Can you help me with the syntax up until I include the sheets? "joel" wrote: Change this one line from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. -- Dave Peterson -- Dave Peterson |
Data Validation List Macro
Here it is. There is no particular line that is being highlighted when the
error occurs. On the worksheet "Enter Account & Choose Form", cell Q11 has the formula =CONCATENATE(H9)&" - "&(H11) which combines the results of two other cells with data validation lists. Cell H9 has "CVS - Odyssey" and cell H11 has "Order Requires No Installation Leader / Budget". After these two cells are valued, the user hits the button "Continue" which runs the following macro. As I mentioned, the macro works except it displays the 400 error. Thanks for continuing to help me! Sub HideSheets() With Sheets("Enter Account & Choose Form") If .Range("Q11") = "CVS - Odyssey - Order Requires No Installation Leader / Budget" Then Sheets("Account & Install Info - Page 1").Visible = False Sheets("Account Info - Page 1").Visible = True Sheets("Installation Info - Page 1").Visible = False Sheets("Tailored Counter - Page 2").Visible = True Sheets("Standard Systems - Page 3").Visible = True Sheets("Signing - Page 4").Visible = True Sheets("WG Signing - Page 4").Visible = False Sheets("Dealer Srvc & Fixtures - Page 5").Visible = True Sheets("WG Dealer Srvc & Fixt - Page 5").Visible = False Sheets("Enter Account & Choose Form").Visible = False Sheets("Account Info - Page 1").Select Range("B9").Select End If End With End Sub "Dave Peterson" wrote: I think it's time to post your current code. And indicate the line that causes the error. Lisa C. wrote: I found and corrected a worksheet name that was wrong. The code now runs and works (hides/unhides sheets as expected) but a visual basic error window appears that has a red "X" and only says "400". "Dave Peterson" wrote: The subscript out of error usually means you have a difference in spelling. With Sheets("Choose Form") Is the name of the sheet really "Choose Form"? I'd start there. If that's not the line with the problem, post back with the troublesome line. Lisa C. wrote: I changed the code and now I'm getting the error "Subscript out of range". "joel" wrote: ou no longer have a TARGET which is used with the worksheet change method. You also didn't need to perform a check of the intersection of the cell you wanted with the target. You left the check of ISECT which also isn't needed. the error was occuring because ISECT is not defined. So I removed the unecessary IF statment. Sub HideSheets() With Sheets("Choose Form") Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End With End Sub "Lisa C." wrote: Here is my exact code. I have assigned this as a macro to a button. When I try to run it, I get the error "Object Required". Sub HideSheets() With Sheets("Choose Form") If Not isect Is Nothing Then Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End If End With End Sub "joel" wrote: something like this Sub HideSheets() with sheets("Sheet1") If Not isect Is Nothing Then Select Case .Range("A1").Value Case "Account Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False end if Case "Order Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False end if Case "Billing Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True end if End Select End If end with End Sub "Lisa C." wrote: Thank you for replying but I cannot get this to work. For one reason, my example was wrong. The two cells are not consecutive...they are in cells A1 and A3 but not A2, so I don't want to use A1:A3. Also, I don't like the macro running when I don't want it to. I think it would it be easier to use a button macro (named "Continue") that the user selects after he/she has input values in cells A1 and A3. Then the macro needs to say something like: If (cell A1) = X and (cell A3 = Y), then hide these sheets and unhide these sheets. Can you help me with the syntax up until I include the sheets? "joel" wrote: Change this one line from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. -- Dave Peterson -- Dave Peterson |
Data Validation List Macro
First, I'd change this line:
Range("B9").Select to Sheets("Account Info - Page 1").Range("B9").Select It may not help, but it won't hurt. And then run a test. Change the values of the dropdowns, but don't hit the button. Then go into the VBE and put the cursor on the Sub HideSheets() line. And hit F8. This will step through the code. Maybe you'll be able to tell when the code breaks. I didn't take the time to set up a test workbook, but except for the line wrap problem (in the If statement), it looked like it should work to me. Lisa C. wrote: Here it is. There is no particular line that is being highlighted when the error occurs. On the worksheet "Enter Account & Choose Form", cell Q11 has the formula =CONCATENATE(H9)&" - "&(H11) which combines the results of two other cells with data validation lists. Cell H9 has "CVS - Odyssey" and cell H11 has "Order Requires No Installation Leader / Budget". After these two cells are valued, the user hits the button "Continue" which runs the following macro. As I mentioned, the macro works except it displays the 400 error. Thanks for continuing to help me! Sub HideSheets() With Sheets("Enter Account & Choose Form") If .Range("Q11") = "CVS - Odyssey - Order Requires No Installation Leader / Budget" Then Sheets("Account & Install Info - Page 1").Visible = False Sheets("Account Info - Page 1").Visible = True Sheets("Installation Info - Page 1").Visible = False Sheets("Tailored Counter - Page 2").Visible = True Sheets("Standard Systems - Page 3").Visible = True Sheets("Signing - Page 4").Visible = True Sheets("WG Signing - Page 4").Visible = False Sheets("Dealer Srvc & Fixtures - Page 5").Visible = True Sheets("WG Dealer Srvc & Fixt - Page 5").Visible = False Sheets("Enter Account & Choose Form").Visible = False Sheets("Account Info - Page 1").Select Range("B9").Select End If End With End Sub "Dave Peterson" wrote: I think it's time to post your current code. And indicate the line that causes the error. Lisa C. wrote: I found and corrected a worksheet name that was wrong. The code now runs and works (hides/unhides sheets as expected) but a visual basic error window appears that has a red "X" and only says "400". "Dave Peterson" wrote: The subscript out of error usually means you have a difference in spelling. With Sheets("Choose Form") Is the name of the sheet really "Choose Form"? I'd start there. If that's not the line with the problem, post back with the troublesome line. Lisa C. wrote: I changed the code and now I'm getting the error "Subscript out of range". "joel" wrote: ou no longer have a TARGET which is used with the worksheet change method. You also didn't need to perform a check of the intersection of the cell you wanted with the target. You left the check of ISECT which also isn't needed. the error was occuring because ISECT is not defined. So I removed the unecessary IF statment. Sub HideSheets() With Sheets("Choose Form") Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End With End Sub "Lisa C." wrote: Here is my exact code. I have assigned this as a macro to a button. When I try to run it, I get the error "Object Required". Sub HideSheets() With Sheets("Choose Form") If Not isect Is Nothing Then Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End If End With End Sub "joel" wrote: something like this Sub HideSheets() with sheets("Sheet1") If Not isect Is Nothing Then Select Case .Range("A1").Value Case "Account Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False end if Case "Order Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False end if Case "Billing Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True end if End Select End If end with End Sub "Lisa C." wrote: Thank you for replying but I cannot get this to work. For one reason, my example was wrong. The two cells are not consecutive...they are in cells A1 and A3 but not A2, so I don't want to use A1:A3. Also, I don't like the macro running when I don't want it to. I think it would it be easier to use a button macro (named "Continue") that the user selects after he/she has input values in cells A1 and A3. Then the macro needs to say something like: If (cell A1) = X and (cell A3 = Y), then hide these sheets and unhide these sheets. Can you help me with the syntax up until I include the sheets? "joel" wrote: Change this one line from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Data Validation List Macro
This must have been the problem because I changed the line and now I am not
getting the error. Thanks! "Dave Peterson" wrote: First, I'd change this line: Range("B9").Select to Sheets("Account Info - Page 1").Range("B9").Select It may not help, but it won't hurt. And then run a test. Change the values of the dropdowns, but don't hit the button. Then go into the VBE and put the cursor on the Sub HideSheets() line. And hit F8. This will step through the code. Maybe you'll be able to tell when the code breaks. I didn't take the time to set up a test workbook, but except for the line wrap problem (in the If statement), it looked like it should work to me. Lisa C. wrote: Here it is. There is no particular line that is being highlighted when the error occurs. On the worksheet "Enter Account & Choose Form", cell Q11 has the formula =CONCATENATE(H9)&" - "&(H11) which combines the results of two other cells with data validation lists. Cell H9 has "CVS - Odyssey" and cell H11 has "Order Requires No Installation Leader / Budget". After these two cells are valued, the user hits the button "Continue" which runs the following macro. As I mentioned, the macro works except it displays the 400 error. Thanks for continuing to help me! Sub HideSheets() With Sheets("Enter Account & Choose Form") If .Range("Q11") = "CVS - Odyssey - Order Requires No Installation Leader / Budget" Then Sheets("Account & Install Info - Page 1").Visible = False Sheets("Account Info - Page 1").Visible = True Sheets("Installation Info - Page 1").Visible = False Sheets("Tailored Counter - Page 2").Visible = True Sheets("Standard Systems - Page 3").Visible = True Sheets("Signing - Page 4").Visible = True Sheets("WG Signing - Page 4").Visible = False Sheets("Dealer Srvc & Fixtures - Page 5").Visible = True Sheets("WG Dealer Srvc & Fixt - Page 5").Visible = False Sheets("Enter Account & Choose Form").Visible = False Sheets("Account Info - Page 1").Select Range("B9").Select End If End With End Sub "Dave Peterson" wrote: I think it's time to post your current code. And indicate the line that causes the error. Lisa C. wrote: I found and corrected a worksheet name that was wrong. The code now runs and works (hides/unhides sheets as expected) but a visual basic error window appears that has a red "X" and only says "400". "Dave Peterson" wrote: The subscript out of error usually means you have a difference in spelling. With Sheets("Choose Form") Is the name of the sheet really "Choose Form"? I'd start there. If that's not the line with the problem, post back with the troublesome line. Lisa C. wrote: I changed the code and now I'm getting the error "Subscript out of range". "joel" wrote: ou no longer have a TARGET which is used with the worksheet change method. You also didn't need to perform a check of the intersection of the cell you wanted with the target. You left the check of ISECT which also isn't needed. the error was occuring because ISECT is not defined. So I removed the unecessary IF statment. Sub HideSheets() With Sheets("Choose Form") Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End With End Sub "Lisa C." wrote: Here is my exact code. I have assigned this as a macro to a button. When I try to run it, I get the error "Object Required". Sub HideSheets() With Sheets("Choose Form") If Not isect Is Nothing Then Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End If End With End Sub "joel" wrote: something like this Sub HideSheets() with sheets("Sheet1") If Not isect Is Nothing Then Select Case .Range("A1").Value Case "Account Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False end if Case "Order Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False end if Case "Billing Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True end if End Select End If end with End Sub "Lisa C." wrote: Thank you for replying but I cannot get this to work. For one reason, my example was wrong. The two cells are not consecutive...they are in cells A1 and A3 but not A2, so I don't want to use A1:A3. Also, I don't like the macro running when I don't want it to. I think it would it be easier to use a button macro (named "Continue") that the user selects after he/she has input values in cells A1 and A3. Then the macro needs to say something like: If (cell A1) = X and (cell A3 = Y), then hide these sheets and unhide these sheets. Can you help me with the syntax up until I include the sheets? "joel" wrote: Change this one line from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Data Validation List Macro
Just curious...
This code is assigned to a button from the Forms toolbar that was placed on a worksheet? And the macro assigned is located in a General module (not in the worksheet module), right? Lisa C. wrote: This must have been the problem because I changed the line and now I am not getting the error. Thanks! "Dave Peterson" wrote: First, I'd change this line: Range("B9").Select to Sheets("Account Info - Page 1").Range("B9").Select It may not help, but it won't hurt. And then run a test. Change the values of the dropdowns, but don't hit the button. Then go into the VBE and put the cursor on the Sub HideSheets() line. And hit F8. This will step through the code. Maybe you'll be able to tell when the code breaks. I didn't take the time to set up a test workbook, but except for the line wrap problem (in the If statement), it looked like it should work to me. Lisa C. wrote: Here it is. There is no particular line that is being highlighted when the error occurs. On the worksheet "Enter Account & Choose Form", cell Q11 has the formula =CONCATENATE(H9)&" - "&(H11) which combines the results of two other cells with data validation lists. Cell H9 has "CVS - Odyssey" and cell H11 has "Order Requires No Installation Leader / Budget". After these two cells are valued, the user hits the button "Continue" which runs the following macro. As I mentioned, the macro works except it displays the 400 error. Thanks for continuing to help me! Sub HideSheets() With Sheets("Enter Account & Choose Form") If .Range("Q11") = "CVS - Odyssey - Order Requires No Installation Leader / Budget" Then Sheets("Account & Install Info - Page 1").Visible = False Sheets("Account Info - Page 1").Visible = True Sheets("Installation Info - Page 1").Visible = False Sheets("Tailored Counter - Page 2").Visible = True Sheets("Standard Systems - Page 3").Visible = True Sheets("Signing - Page 4").Visible = True Sheets("WG Signing - Page 4").Visible = False Sheets("Dealer Srvc & Fixtures - Page 5").Visible = True Sheets("WG Dealer Srvc & Fixt - Page 5").Visible = False Sheets("Enter Account & Choose Form").Visible = False Sheets("Account Info - Page 1").Select Range("B9").Select End If End With End Sub "Dave Peterson" wrote: I think it's time to post your current code. And indicate the line that causes the error. Lisa C. wrote: I found and corrected a worksheet name that was wrong. The code now runs and works (hides/unhides sheets as expected) but a visual basic error window appears that has a red "X" and only says "400". "Dave Peterson" wrote: The subscript out of error usually means you have a difference in spelling. With Sheets("Choose Form") Is the name of the sheet really "Choose Form"? I'd start there. If that's not the line with the problem, post back with the troublesome line. Lisa C. wrote: I changed the code and now I'm getting the error "Subscript out of range". "joel" wrote: ou no longer have a TARGET which is used with the worksheet change method. You also didn't need to perform a check of the intersection of the cell you wanted with the target. You left the check of ISECT which also isn't needed. the error was occuring because ISECT is not defined. So I removed the unecessary IF statment. Sub HideSheets() With Sheets("Choose Form") Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End With End Sub "Lisa C." wrote: Here is my exact code. I have assigned this as a macro to a button. When I try to run it, I get the error "Object Required". Sub HideSheets() With Sheets("Choose Form") If Not isect Is Nothing Then Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End If End With End Sub "joel" wrote: something like this Sub HideSheets() with sheets("Sheet1") If Not isect Is Nothing Then Select Case .Range("A1").Value Case "Account Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False end if Case "Order Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False end if Case "Billing Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True end if End Select End If end with End Sub "Lisa C." wrote: Thank you for replying but I cannot get this to work. For one reason, my example was wrong. The two cells are not consecutive...they are in cells A1 and A3 but not A2, so I don't want to use A1:A3. Also, I don't like the macro running when I don't want it to. I think it would it be easier to use a button macro (named "Continue") that the user selects after he/she has input values in cells A1 and A3. Then the macro needs to say something like: If (cell A1) = X and (cell A3 = Y), then hide these sheets and unhide these sheets. Can you help me with the syntax up until I include the sheets? "joel" wrote: Change this one line from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Data Validation List Macro
Yes, it is assigned to a buttom from the Forms toolbar and placed on a
worksheet but the macro code was on the same worksheet module. It still works, however, when I move the code to a General module. "Dave Peterson" wrote: Just curious... This code is assigned to a button from the Forms toolbar that was placed on a worksheet? And the macro assigned is located in a General module (not in the worksheet module), right? Lisa C. wrote: This must have been the problem because I changed the line and now I am not getting the error. Thanks! "Dave Peterson" wrote: First, I'd change this line: Range("B9").Select to Sheets("Account Info - Page 1").Range("B9").Select It may not help, but it won't hurt. And then run a test. Change the values of the dropdowns, but don't hit the button. Then go into the VBE and put the cursor on the Sub HideSheets() line. And hit F8. This will step through the code. Maybe you'll be able to tell when the code breaks. I didn't take the time to set up a test workbook, but except for the line wrap problem (in the If statement), it looked like it should work to me. Lisa C. wrote: Here it is. There is no particular line that is being highlighted when the error occurs. On the worksheet "Enter Account & Choose Form", cell Q11 has the formula =CONCATENATE(H9)&" - "&(H11) which combines the results of two other cells with data validation lists. Cell H9 has "CVS - Odyssey" and cell H11 has "Order Requires No Installation Leader / Budget". After these two cells are valued, the user hits the button "Continue" which runs the following macro. As I mentioned, the macro works except it displays the 400 error. Thanks for continuing to help me! Sub HideSheets() With Sheets("Enter Account & Choose Form") If .Range("Q11") = "CVS - Odyssey - Order Requires No Installation Leader / Budget" Then Sheets("Account & Install Info - Page 1").Visible = False Sheets("Account Info - Page 1").Visible = True Sheets("Installation Info - Page 1").Visible = False Sheets("Tailored Counter - Page 2").Visible = True Sheets("Standard Systems - Page 3").Visible = True Sheets("Signing - Page 4").Visible = True Sheets("WG Signing - Page 4").Visible = False Sheets("Dealer Srvc & Fixtures - Page 5").Visible = True Sheets("WG Dealer Srvc & Fixt - Page 5").Visible = False Sheets("Enter Account & Choose Form").Visible = False Sheets("Account Info - Page 1").Select Range("B9").Select End If End With End Sub "Dave Peterson" wrote: I think it's time to post your current code. And indicate the line that causes the error. Lisa C. wrote: I found and corrected a worksheet name that was wrong. The code now runs and works (hides/unhides sheets as expected) but a visual basic error window appears that has a red "X" and only says "400". "Dave Peterson" wrote: The subscript out of error usually means you have a difference in spelling. With Sheets("Choose Form") Is the name of the sheet really "Choose Form"? I'd start there. If that's not the line with the problem, post back with the troublesome line. Lisa C. wrote: I changed the code and now I'm getting the error "Subscript out of range". "joel" wrote: ou no longer have a TARGET which is used with the worksheet change method. You also didn't need to perform a check of the intersection of the cell you wanted with the target. You left the check of ISECT which also isn't needed. the error was occuring because ISECT is not defined. So I removed the unecessary IF statment. Sub HideSheets() With Sheets("Choose Form") Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End With End Sub "Lisa C." wrote: Here is my exact code. I have assigned this as a macro to a button. When I try to run it, I get the error "Object Required". Sub HideSheets() With Sheets("Choose Form") If Not isect Is Nothing Then Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End If End With End Sub "joel" wrote: something like this Sub HideSheets() with sheets("Sheet1") If Not isect Is Nothing Then Select Case .Range("A1").Value Case "Account Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False end if Case "Order Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False end if Case "Billing Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True end if End Select End If end with End Sub "Lisa C." wrote: Thank you for replying but I cannot get this to work. For one reason, my example was wrong. The two cells are not consecutive...they are in cells A1 and A3 but not A2, so I don't want to use A1:A3. Also, I don't like the macro running when I don't want it to. I think it would it be easier to use a button macro (named "Continue") that the user selects after he/she has input values in cells A1 and A3. Then the macro needs to say something like: If (cell A1) = X and (cell A3 = Y), then hide these sheets and unhide these sheets. Can you help me with the syntax up until I include the sheets? "joel" wrote: Change this one line from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Data Validation List Macro
Just an explanation...
If the code is in a worksheet module, then the unqualified ranges like this: Range("B9").Select refer to the activesheet. But when the code is in a worksheet module, then those unqualified ranges belong to the sheet that owns the code. And you can only select a range on the activesheet. So when you changed sheets with this code, Sheets("Account Info - Page 1").Select Range("B9").Select It blew up. The range("B9").select was trying to select B9 on the sheet with the code--not on the "Account Info - Page 1" sheet. My opinion is that this kind of code (not related to a worksheet event) really belongs in a general module--not behind a worksheet. Lisa C. wrote: Yes, it is assigned to a buttom from the Forms toolbar and placed on a worksheet but the macro code was on the same worksheet module. It still works, however, when I move the code to a General module. "Dave Peterson" wrote: Just curious... This code is assigned to a button from the Forms toolbar that was placed on a worksheet? And the macro assigned is located in a General module (not in the worksheet module), right? Lisa C. wrote: This must have been the problem because I changed the line and now I am not getting the error. Thanks! "Dave Peterson" wrote: First, I'd change this line: Range("B9").Select to Sheets("Account Info - Page 1").Range("B9").Select It may not help, but it won't hurt. And then run a test. Change the values of the dropdowns, but don't hit the button. Then go into the VBE and put the cursor on the Sub HideSheets() line. And hit F8. This will step through the code. Maybe you'll be able to tell when the code breaks. I didn't take the time to set up a test workbook, but except for the line wrap problem (in the If statement), it looked like it should work to me. Lisa C. wrote: Here it is. There is no particular line that is being highlighted when the error occurs. On the worksheet "Enter Account & Choose Form", cell Q11 has the formula =CONCATENATE(H9)&" - "&(H11) which combines the results of two other cells with data validation lists. Cell H9 has "CVS - Odyssey" and cell H11 has "Order Requires No Installation Leader / Budget". After these two cells are valued, the user hits the button "Continue" which runs the following macro. As I mentioned, the macro works except it displays the 400 error. Thanks for continuing to help me! Sub HideSheets() With Sheets("Enter Account & Choose Form") If .Range("Q11") = "CVS - Odyssey - Order Requires No Installation Leader / Budget" Then Sheets("Account & Install Info - Page 1").Visible = False Sheets("Account Info - Page 1").Visible = True Sheets("Installation Info - Page 1").Visible = False Sheets("Tailored Counter - Page 2").Visible = True Sheets("Standard Systems - Page 3").Visible = True Sheets("Signing - Page 4").Visible = True Sheets("WG Signing - Page 4").Visible = False Sheets("Dealer Srvc & Fixtures - Page 5").Visible = True Sheets("WG Dealer Srvc & Fixt - Page 5").Visible = False Sheets("Enter Account & Choose Form").Visible = False Sheets("Account Info - Page 1").Select Range("B9").Select End If End With End Sub "Dave Peterson" wrote: I think it's time to post your current code. And indicate the line that causes the error. Lisa C. wrote: I found and corrected a worksheet name that was wrong. The code now runs and works (hides/unhides sheets as expected) but a visual basic error window appears that has a red "X" and only says "400". "Dave Peterson" wrote: The subscript out of error usually means you have a difference in spelling. With Sheets("Choose Form") Is the name of the sheet really "Choose Form"? I'd start there. If that's not the line with the problem, post back with the troublesome line. Lisa C. wrote: I changed the code and now I'm getting the error "Subscript out of range". "joel" wrote: ou no longer have a TARGET which is used with the worksheet change method. You also didn't need to perform a check of the intersection of the cell you wanted with the target. You left the check of ISECT which also isn't needed. the error was occuring because ISECT is not defined. So I removed the unecessary IF statment. Sub HideSheets() With Sheets("Choose Form") Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End With End Sub "Lisa C." wrote: Here is my exact code. I have assigned this as a macro to a button. When I try to run it, I get the error "Object Required". Sub HideSheets() With Sheets("Choose Form") If Not isect Is Nothing Then Select Case .Range("Q11").Value Case "Account Info" If .Range("Q11") = "Account Info" Then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False End If End Select End If End With End Sub "joel" wrote: something like this Sub HideSheets() with sheets("Sheet1") If Not isect Is Nothing Then Select Case .Range("A1").Value Case "Account Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False end if Case "Order Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False end if Case "Billing Info" if .Range("A3") = "abc" then Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True end if End Select End If end with End Sub "Lisa C." wrote: Thank you for replying but I cannot get this to work. For one reason, my example was wrong. The two cells are not consecutive...they are in cells A1 and A3 but not A2, so I don't want to use A1:A3. Also, I don't like the macro running when I don't want it to. I think it would it be easier to use a button macro (named "Continue") that the user selects after he/she has input values in cells A1 and A3. Then the macro needs to say something like: If (cell A1) = X and (cell A3 = Y), then hide these sheets and unhide these sheets. Can you help me with the syntax up until I include the sheets? "joel" wrote: Change this one line from Set isect = Application.Intersect(target, Range("A1")) to Set isect = Application.Intersect(target, Range("A1:A2")) You need to add additional If statements (or Select Case) to distinquish between all the different combinations. You can't stop the macro from running until both values are changed easily. You would need to use some additional cells to keep a history on the worksheet would keep the last value of both validation lists. then only make changes when both are different from the previous value. Then update the history cells when the 2nd list was changed. I don't think you want to do this because what happens if you really want to change only one of the cedlls and not the other. "Lisa C." wrote: Joel - This worked great. Thank you! Got another scenario......If I create two different data validation lists on the same worksheet, one in cell A1 and one in cell A2, is there a way to have the combination of the two values selected for these cells to run a macro that hides/unhides worksheets? Do I need to have a third cell (cell X3) with a formula that concatenates the two values in A1 and A2 and then use cell X3 in the macro? Can you use a target cell in the macro that has a formula in it but have the macro use the values calculated from it? How do I keep the macro from kicking off until both cell A1 and A2 have been valued and combined in cell X3? "joel" wrote: Sub worksheet_change(ByVal target As Range) Set isect = Application.Intersect(target, Range("A1")) If Not isect Is Nothing Then Select Case Range("A1").Value Case "Account Info" Sheets("Account Info").Visible = True Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = False Case "Order Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = True Sheets("Billing Info").Visible = False Case "Billing Info" Sheets("Account Info").Visible = False Sheets("Order Info").Visible = False Sheets("Billing Info").Visible = True End Select End If End Sub "Lisa C." wrote: Is there a way to run a macro that hides/unhides multiple worksheets based on the value selected on a data validation list? If so, what would the VB syntax be for the macro? For example, if Cell A1 contains a data validation list with 3 values to choose from (Account Info, Order Info, Billing Info) and Account Info is chosen, I want to unhide the Account Info worksheet and hide the Order Info and Billing Info worksheets. If Order Info is chosen, I want to unhide the Order Info worksheet, and hide the Account Info and Billing Info worksheets, etc. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 08:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com