How to let users specify multiple-level categories in Excel?

Question

I'm developing a kind of template with Excel 2007. Users will use it to create records which fall in 3-level categories. A user should be able to create a new category, specify an existing category or not specify one. A record may belong to multiple categories.

I'm wondering what my best choice is to present the category structure to users. Categories are serving as labels, so there are basically no VBA programming needed. Are there any controls or functionalities in Excel suitable for this situation? Please advise.

Answer 1

I'd just use the built in data validation feature. Point it to a list and then the users will be presented with a float-level dropdown. Edit: Ok, then the basic principle here is linked lists. For every sub category there will be the category text and the parent category. Whenever the main category changes you re-filter the sub-category to only show the items belonging to that parent category.

There are a lot of ways to implement this idea. Here is a trivial example. To use it, create a user form with two comboboxes and paste in the code.

Option Explicit

Private masCat2() As String

Private Sub UserForm_Initialize()
    Me.ComboBox1.List = CreateTestData(0)
    With Me.ComboBox2
        masCat2 = CreateTestData(1)
    End With
End Sub

Private Sub ComboBox1_Change()
    Me.ComboBox2.List = FilterArray(masCat2, Me.ComboBox1.Value)
End Sub

Private Function FilterArray(ByRef vals() As String, ByVal match As String) As String()
    Dim i As Long, j As Long
    Dim asVals() As String
    ReDim asVals(UBound(vals, 1)) As String
    For i = 0 To UBound(vals, 1)
        If vals(i, 0) = match Then
            asVals(j) = vals(i, 1)
            j = j + 1
        End If
    Next
    ReDim Preserve asVals(j - 1)
    FilterArray = asVals
End Function

Private Function CreateTestData(ByVal series As Long) As String()
    Dim asRtnVal() As String
    Select Case series
        Case 0
            ReDim asRtnVal(1) As String
            asRtnVal(0) = "Thing1"
            asRtnVal(1) = "Thing2"
        Case 1
            ReDim asRtnVal(3, 1) As String
            asRtnVal(0, 0) = "Thing1"
            asRtnVal(1, 0) = "Thing1"
            asRtnVal(2, 0) = "Thing2"
            asRtnVal(3, 0) = "Thing2"
            asRtnVal(0, 1) = "ThingA"
            asRtnVal(1, 1) = "ThingB"
            asRtnVal(2, 1) = "ThingC"
            asRtnVal(3, 1) = "ThingD"
    End Select
    CreateTestData = asRtnVal
End Function

Answer 2

A user should be able to create a new category, specify an existing category or not specify one. A record may belong to multiple categories.

Question - If the user specifies a new entry for any level of the category, should that new entry become part of the list of options shown on later rows?

If so, this is more than can be easily done with excel formulas and validation. Instead, you should search online for examples of VBA for dynamic lists using combo boxes.