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