Data Validation Based on Indent?

[USAMax]

I have a list with over 1700 items for data validation. I am sure Excel could not handle this many items for data validation and even if it could, who would want to read through this list.

My client has the list indented based on hierarchy. I need everything with an indent value of one and the next data validation will look for an indent value of two.

Is this possible or will I have to reformat the data? If I have to reformat the data is there a way to automate this?

This one is a tuffy and I thank you in advance just for looking at this...

Dave

 

[USAMax]

I had to fake the results but here is how I want the drop-down message box to look. Download Book4 xlsm, fast and secure downloading from crocko.com

I need the user to be able to select one of the items that have a single indent. Once they select that I need them to be given an option of one of the cells under their selection with two indents. They can choose to stop here or continue to select another cell under their selection with three indents. This process could continue up to 5 indents.

 

[USAMax]

I had to fake the results but here is how I want the drop-down message box to look. Download Book4 xlsm, fast and secure downloading from crocko.com

I need the user to be able to select one of the items that have a single indent. Once they select that I need them to be given an option of one of the cells under their selection with two indents. They can choose to stop here or continue to select another cell under their selection with three indents. This process could continue up to 5 indents.

 

[USAMax]

Good news, I have made a lot of progress but I am stumped again. My solution was to use the IndentLevel like this:

Sheets("Flow").Cells(i, 3).IndentLevel = 1

This gives me my first level of indent exactly as I want. Once the user makes their selection I have four new problems:

1) The drop-down list does not go away unless I click somewhere on the userform.
2) I can get the row that each item with only 1 Indent Level but where do I keep it for later? A separate public array?
3) How do I re-populate the ComboBox when they click Next?
4) How do I pass the final value they selected when they click Done?

The following is code that I have been modifying so it is not complete. Next needs to clear ComboBox1 then repopulate it with the next indent level starting at the row of the previously selected item. Done needs to return the final value that was selected along with the row it was found on.


Private Sub ComboBox1_Change()
Dim lst As Long
With Sheet1
lst = .Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lst
If .Range("A" & i) = ComboBox1 Then
.Range("B" & i) = "Y"
End If
Next i
End With
Unload Me
End Sub

Private Sub CommandButtonCancel_Click()
Unload Me
End Sub

Private Sub CommandButtonDone_Click()

End Sub

Private Sub CommandButtonNext_Click()
Dim lst As Long
IndentLevel = IndentLevel + 1
With Sheet1
lst = Sheets("Flow").Range("C" & Rows.Count).End(xlUp).Row
For i = 3 To lst 'Read the header values from the First to the Last columns
'MyTest = Sheets("Flow").Cells(i, 3).IndentLevel
If Sheets("Flow").Cells(i, 3) <> "" And Sheets("Flow").Cells(i, 3).IndentLevel = IndentLevel Then 'Is the current header blank?
MyTest = Sheets("Flow").Cells(i, 3) 'FOR TESTING ONLY
ComboBox1.AddItem Sheets("Flow").Cells(i, 3) ' NO:Advance to the next column
End If ' Get the next header
Next i '
End With

End Sub

Private Sub UserForm_Initialize()
Dim lst As Long
With Sheet5
lst = Sheets("Flow").Range("C" & Rows.Count).End(xlUp).Row
For i = 3 To lst 'Read the header values from the First to the Last columns
'MyTest = Sheets("Flow").Cells(i, 3).IndentLevel
If Sheets("Flow").Cells(i, 3) <> "" And Sheets("Flow").Cells(i, 3).IndentLevel = IndentLevel Then 'Is the current header blank?
MyTest = Sheets("Flow").Cells(i, 3) 'FOR TESTING ONLY
ComboBox1.AddItem Sheets("Flow").Cells(i, 3) ' NO:Advance to the next column
End If ' Get the next header
Next i '
End With
End Sub

 

[USAMax]

I found a way to get this to work but the array values will not pass along between subroutines.

Private GlobalArray(2, 10) As String


[B]Private Sub ComboBox1_Change()[/B]
    x = x / 0
Dim lst As Long
With Sheet1
lst = .Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lst
    If .Range("A" & i) = ComboBox1 Then
        .Range("B" & i) = "Y"
    End If
Next i
End With
Unload Me
End Sub

[B]Private Sub ComboBox2_Change()[/B]

End Sub

[B]Private Sub CommandButtonCancel_Click()[/B]
    Unload Me
End Sub

[B]Private Sub CommandButtonDone_Click()[/B]

End Sub

[B]Private Sub CommandButtonNext_Click()[/B]
    Dim lst As Long
    IndentLevel = IndentLevel + 1
    
    CurRow = 1
    Do While GlobalArray(2, CurRow) <> ComboBox2
        CurRow = CurRow + 1
    Loop
    
    With Sheet1
    lst = Sheets("Flow").Range("C" & Rows.Count).End(xlUp).Row
    For i = 3 To lst                                          'Read the header values from the First to the Last columns
        'MyTest = Sheets("Flow").Cells(i, 3).IndentLevel
        If Sheets("Flow").Cells(i, 3) <> "" And Sheets("Flow").Cells(i, 3).IndentLevel = IndentLevel Then                'Is the current header blank?
            MyTest = Sheets("Flow").Cells(i, 3)                'FOR TESTING ONLY
            ComboBox1.AddItem Sheets("Flow").Cells(i, 3)        ' NO:Advance to the next column
        End If                                                  '    Get the next header
    Next i                                                      '
End With

End Sub

[B]Private Sub UserForm_Initialize()[/B]
Dim lst As Long
With Sheet5
lst = Sheets("Flow").Range("C" & Rows.Count).End(xlUp).Row
    For i = 3 To lst                                          'Read the header values from the First to the Last columns
        'MyTest = Sheets("Flow").Cells(i, 3).IndentLevel
        If Sheets("Flow").Cells(i, 3) <> "" And Sheets("Flow").Cells(i, 3).IndentLevel = IndentLevel Then                'Is the current header blank?
            ComboBox2.AddItem Sheets("Flow").Cells(i, 3)        ' NO:Advance to the next column
            GlobalArray(1, CurRow) = i                          'Get the row that the value was found on.
            GlobalArray(2, CurRow) = Sheets("Flow").Cells(i, 3) 'Get the value of the cell
        End If                                                  '    Get the next header
    Next i                                                      '
End With
End Sub

[code]

 

[stoolpidgeon]

What do you ultimately want to achieve? Do you want to just populate separate data validations with a list of only those cells containing x indents (x = 1 to 5?)? Or do you want those to be dependent on each other, so that if you make a selection in your first validation, the values in the neighbouring validation are restricted based on this choice?

 

[stoolpidgeon]

I've just read your earlier explanation so ignore my last post. The best strategy here is to use VBA to adjust your data from the current system of indenting into a system which will enable you to use named ranges to identify the data sets for your dependent validations.
If you google 'dependent data validations' and look for 'Excel Data Validation with Dependent Lists' that should walk you through what you need to do.
Without looking at your exact data setup I can't write anything in VBA to adjust your current setup to one which is 'dependent list friendly' but if you give me a better idea of how it's setup currently I may be able to help with that.