?
Solved

Conditional Drop downs Really

Posted on 2012-08-27
4
Medium Priority
?
404 Views
Last Modified: 2012-08-27
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
0
Comment
Question by:Rayne
  • 3
4 Comments
 

Author Comment

by:Rayne
ID: 38338162
Please advise on the best way to get this done

Thank you
0
 

Author Comment

by:Rayne
ID: 38338177
Getting errors when I select category 1 and others
0
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 38338528
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
 

Author Comment

by:Rayne
ID: 38338638
Perfection is what I got. Thank you Imnorie :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question