Excel Drop Down Lists 1

[hahnsm]

I am in need of some help! I have a validation drop down list on an excel spreadsheet in Cell B4. In B5 I would like to create another drop down list based on what the user selected in B4.
Example: If the user were to select "Line 6" from the drop down list in B4 then I would like a set of equipment names to be a drop down list in B5. If the user selected "Line 9" in B4 then a different list of equipment names would be available in a drop down list in B5.
I have tried usin the following VBA Code:
Sub MachineAreaDropList()
Select Case Sheets("UserForm&quot.Range("B4&quot
Case "Line 6"
Sheets("UserForm&quot.Select
Range("B5&quot.Select
With Selection.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Line6MAList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Case "Line 7"
Sheets("UserForm&quot.Select
Range("B5&quot.Select
With Selection.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Line7MAList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Select
End Sub

I get the error "Run-time error '1004': Application-defined or object-defined error" and the debug lands at the .AddType statement. I am not sure what this error means or if I am even coding correctly to get my desired results. Also the reason I am using Case statements is because I have 28 cases to check.
Please Help!
Thanks
Sadie

 

[TonyJollans]

Hi hahnsm,

This error happens on an Add, I think, if you already have data validation on cell B5; you either need to Delete the existing validation first or use the Modify method instead.

I'm not well up on this but you might want to take a look at Debra Dalgeish's examples of dependent lists at

http://www.contextures.com/xlDataVal02.html

Enjoy,
Tony

 

[DrBowes]

A simple method is to have the validation list for B5 as vlookup formulas, based on what is in B4.

Alternatively you could use the following, ensure, it is held in the worksheet module. You can add the other cases.

Private Sub Worksheet_Change(ByVal Target as Range)

If Target.Address = $B$4 Then

Range("B5&quot.Validation.Delete

Select Case Sheets("UserForm&quot.Range("B4&quot
Case "Line 6"
Range("B5&quot.Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Line6MAList"
Case "Line 7"
Range("B5&quot.Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Line7MAList"

End Select

End If

End Sub