Conditional Drop downs Really

Hello All,
There are two data validation dropdowns. There is category and a iten belongs to the category. Now needed is a worksheet change event that triggers this:
If the user selects a category, then first it looks at the numbers of the items that lie in that category range, if the number of items in that category range is more than one, the second dropdown shows “ALL “ by default.
On the other hand, if the category range has only one item in the list, then it shows that one item for the itemChoice range by default
For example: If I select category 1, I get to see “ALL” by default for the item dropdown. If I select category2, then item dropdown gets the first item [a2] shown in the item choice dropdown
prefill.xlsm
RayneAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Try this.
Private Sub Worksheet_Change(ByVal Target As Range)

'if a category choice is made
 If (Sheet1.Range("catChoice").Value <> "") Then
 
 'check the range count to be bigger than 2, if yes then prefil the itemchoice range with "ALL"
        If Sheet2.Range(Sheet1.Range("catChoice").Value).Count > 2 Then
                Application.EnableEvents = False
                Sheet1.Range("itemChoice").Value = "ALL"
                Application.EnableEvents = True
        Else
                'Sheet3.Range("itemChoice").Value = "" ' = first item of the range really
        End If

 End If
        
End Sub

Open in new window

Or you could try this which selects the first item in the selected category no matter what it is or how many items are in the category.
Private Sub Worksheet_Change(ByVal Target As Range)

'if a category choice is made
    If (Sheet1.Range("catChoice").Value <> "") Then
        Application.EnableEvents = False
        Sheet1.Range("itemChoice").Value = Sheet2.Range(Sheet1.Range("catChoice").Value).Cells(1, 1)
        Application.EnableEvents = True
    End If

End Sub

Open in new window

0
 
RayneAuthor Commented:
Please advise on the best way to get this done

Thank you
0
 
RayneAuthor Commented:
Getting errors when I select category 1 and others
0
 
RayneAuthor Commented:
Perfection is what I got. Thank you Imnorie :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.