Data Validation List Macro

[Lisa C.]

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.

 

[joel]

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.]

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]

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.]

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]

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.]

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]

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

 

[Dave Peterson]

And you don't need both the "select case" and the "if" statement.

 

[Lisa C.]

I changed the code and now I'm getting the error "Subscript out of range".

 

[Dave Peterson]

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.]

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]

I think it's time to post your current code.

And indicate the line that causes the error.

 

[Lisa C.]

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]

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.

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

 

[Lisa C.]

This must have been the problem because I changed the line and now I am not
getting the error. Thanks!

 

[Dave Peterson]

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?

This must have been the problem because I changed the line and now I am not
getting the error. Thanks!

 

[Lisa C.]

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]

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,

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.

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.