Excel output list of variables based on drop down menu

Question

I'm pretty new to VBA, so hopefully this isn't too complex a question. What I'd like is to have a drop-down list (which I know how to do) in a cell on one sheet (say sheet1 A1) and then depending on what is chosen the cells below will fill based on a table in another sheet.

So for example some of the options in the list might be "North" "West" etc and if "West" is selected, all sites marked as "West" in Sheet2 will return their site number in sheet1 A2, A3 etc. (and maybe site name in B2, B3.. if possible)

eg sheet1:

West <- drop down menu

28 <- site numbers will auto fill
60
61
68
87
…

It's made a bit more complicated because my dropdown list won't all be based on area but on a range of columns in Sheet2 such as site type, site status etc.

Column A is then referenced in another macro - but at the moment has to be updated manually, so I want it to be possible to get a list based on a variable but then cutomise(/delete) some values if they are not required.

This sheet needs to be operable by someone with no VBA knowledge at all - and I intend to hide the more complex sheet, so it doesn't lead to extra confusion.

Thanks

user3514930 user3514930 · Accepted Answer · 2014-04-11T09:19:03

First I have used range with name to reduce the check for length of data.
The Range where write the data is called "WriteData".
The Range where YOU write the list in the DropDown is called "DropDown". Eventuall these data can be autofill later.
The Range where are taken the data is called "Dati"
I have put all in a sheet, but are the same...
The Combobox write the choose in the cell "B5" and are filled with the Range "DropDown".
The code is:

Dim Start As Integer
Dim Row As Boolean
Dim FoundC As Boolean
Start = Range("WriteData").Row
Row = False
FoundC = False
Range("WriteData").Value = ""
For Each xx In Range("Dati")
    If xx.Value = "" Then Exit For
    If Row = False Then
        Row = True
        If xx.Value = Range("B5").Value Then
            FoundC = True
            Cells(Start, 2).Value = xx.Value
        End If
    Else
        Row = False
        If FoundC Then
            Cells(Start, 3).Value = xx.Value
            FoundC = False
            Start = Start + 1
        End If
    End If
Next

Are insert in the ComboBox event Change.

I add the code to Autofilter the Combobox in base of all the data in the "Dati" Range.

Range("DropDown").ClearContents
Range("Key").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("DropDown"), Unique:=True

The Range named "Key" it's the column inside "Dati" that have the key you want ("West" ...)

Excel output list of variables based on drop down menu

1 Answers