Nested If Workaround in Data Validation

[g0atface]

Hi

I have this sheet set up - im currently using data validation on the values shown. The column B holds the selection optoins the 1st being section list, which when clicked gives 3 options, then the next list has a validation based on the 3 ranges, STROPT, PIPOPT, MEIOPT

Basically im trying to go down the next step where the next validation cell will bring up the correct list when a list is chosen.

For example chosing A- Preliminaries will show the options for SCTA and so on.

i can do it with Nested IF up to section G but after this the 7 limitation kicks in and i can do no more - is there a way round this??

EDIT: Removed HTML - Image is too big for the page - Moderator

 

[stanleydgromjr]

Part 4: Three Linked Data Validation Lists with VBA

Part 4: Three Linked Data Validation Lists with VBA

More screenshots:

Data Validation x 3 - g0atface - Nested If Workaround in Data Validation.xls
	L	M	N	O
1	SCTC	SCTD	SCTE
2
3	C0-Typeofworknotstated	D0-Typeofworknotstated	E0-Typeofworknotstated
4			E2-Excavationtypenotstated
5			E3-Excavationforfoundations
6			E4-GeneralExcavation
7			E6-Filling
8			E7-Fillingancillaries
9
Sheet1

 

[stanleydgromjr]

Part 5: Three Linked Data Validation Lists with VBA

Part 5: Three Linked Data Validation Lists with VBA

Manually create the first validation list in cell B1:
Click in cell B1
Data, Validation, List, Source: =$F$2:$F$6


Copy the below code:

'----------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'
' The following code was created on 10/31/2006 by Stanley D. Grom, Jr.
'   Thank you 'lenze', reference 'Oct 31, 2006 4:21 pm  Assumming you mean
'   a Data Validation List Box, you can use the worksheet change event'

    If Target.Address = "$B$1" Then

        Select Case Target
        Case ""
            Range("C1").Select
            Range("C1").ClearContents
            With Selection.Validation
                .Delete
            End With
            Range("D1").Select
            Range("D1").ClearContents
            With Selection.Validation
                .Delete
            End With
            Range("B1").Select
            Exit Sub

        Case "Structures"
            Range("C1").Select
            Range("C1").ClearContents
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=$G$2:$G$17"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Exit Sub
    
        Case "Pipework"
            Range("C1").Select
            Range("C1").ClearContents
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=$H$2:$H$11"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Exit Sub

        Case "MEICA"
            Range("C1").Select
            Range("C1").ClearContents
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=$I$2:$I$4"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Exit Sub

        Case Else: Exit Sub
        End Select

    End If


    If Target.Address = "$C$1" Then

        Select Case Mid(Target, 1, 1)
        Case ""
            Range("D1").Select
            Range("D1").ClearContents
            With Selection.Validation
                .Delete
            End With
            Range("C1").Select
            Exit Sub

        Case "A" ' - PRELIMINARIES"
            Range("D1").Select
            Range("D1").ClearContents
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=$J$2:$J$9"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Exit Sub

        Case "B" ' - GROUND INVESTIGATION"
            Range("D1").Select
            Range("D1").ClearContents
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=$K$2:$K$12"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Exit Sub

        Case "C" ' - GEOTECHNICAL AND OTHER SERVICES"
            Range("D1").Select
            Range("D1").ClearContents
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=$L$2:$L$4"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Exit Sub

        Case "D" ' - DEMOLITION AND SITE CLEARANCE"
            Range("D1").Select
            Range("D1").ClearContents
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=$M$2:$M$4"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Exit Sub

        Case "E" ' - EARTHWORKS"
            Range("D1").Select
            Range("D1").ClearContents
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=$N$2:$N$9"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Exit Sub

        Case "F", "G", "M", "N", "R", "X", "Y", "Z"
            Range("D1").Clear
            Range("C1").Select
            MsgBox "F, G, M, N, R, X, Y, Z, or Miscellaneous, are not setup yet."
            Exit Sub

            ' Setup code for the following selections (see above example)
            'F - IN SITU CONCRETE
            'G - CONCRETE ANCILLIARIES
            'M - STRUCTURAL METALWORK
            'N - MISCELLANEOUS METALWORK
            'R - ROADS AND PAVINGS
            'X - MISCELLANEOUS WORK
            'Y - SEWER AND WATER MAIN RENOVATION AND ANCILLIARY WORKS
            'Z - CLASS Z: BUILDING WORKS
            '? - Miscellaneous

        Case Else: Exit Sub
        End Select

    End If

End Sub
'----------------------------------------------------------

Press ALT F11, and paste the above code into the
VBA Project, Microsoft Excel Objects, Sheet1(Sheet1)


Click in cell B1, and make your first choice.

Click in cell C1, and make your second choice.

Click in cell D1, and make your third choice.


I am using Windows XP Professional SP2, and Excel 2003 SP2.

Have a great day,
Stan