Solved

Populate Excel User Form Combo Box with Unique Items from a column in a filtered table using VBA

Posted on 2011-03-24
9
1,046 Views
Last Modified: 2012-08-14
Dear Experts,

I have a spreadsheet that contains data pertaining to items held in stock. Each item has a part number and a decsription and then various attributes such as colour, size, supplier etc. There are approx 1000 items (rows) and the attributes etc. take up 20 columns

I want to be able to use an Excel User Form with combo boxes to assist the user in "intelligently" identifying the best item number to request from stock given that he/she may know the attributes but not the item number. It's intended that there will be 5 or 6 combo boxes and each box will relate to one of the key attributes and that the drop down selections on the remaining boxes will be narrowed down after each successive selection.

It seems to me that the best way of doing this is to use the combobox values to create autofiltered data, using the results of each successive combo box selection to further filter on the available data until the last combo box is the actual description of the item required (from which the item number can be deduced).

I'm OK with creating the autofilters from combo boxes but I've hit a problem.

I want the combo boxes to only populate with the unique values of a visible range instead of all the values. In other words if the first combobox filters on all the "BIG" items this will leave filtered output of 30 rows with a mixture of colours being say, "RED", "BLUE", "YELLOW".

If I try and populate the "Select Colour" Combo box I get all 30 colour values. What I really want is the unique values in the visible range (column) so that the user only sees choices of RED, BLUE, YELLOW in that drop down list in the combo box.

I could probably do this by copying the values to a sheet and manipulating them there but that seems inelegant and time consuming for multiple operations and for some reason an array is in my mind.

Can anyone assist me in creating the VBA that will:

1) Allow me to efficiently detect the unique values in a column of visible autofiltered cells
2) Allow me to then populate a combo box with the values collected during the identification of the unique values.

Many thanks for your help

0
Comment
Question by:pfmurray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205633
>>>Can anyone assist me in creating the VBA that will:

Sure :) May I see a sample file so that I get my references correct? Also please do specify the Excel Ver.

Sid
0
 

Author Comment

by:pfmurray
ID: 35206358
hi Sid

Using Excel 2007. Sample file attached  as requested.


 Test-combo-box-form.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35206662
Ok 1st question.

>>>Allow me to efficiently detect the unique values in a column of visible autofiltered cells

Which Column are we looking at?

Sid
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35206687
Also which subsequent column are we looking at? Assuming that the first column is Col 2.

Sid
0
 

Author Comment

by:pfmurray
ID: 35206703
Hi Sid

For the sample the first combo box looks at the column 2, then the second combo box looks at column 4 and on selection outputs data to the text box

Appreciate your help

Pete
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35206710
Ok Great. give me few moments While I create a sample for you :)

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35206926
Ok Is this what you want? Sample Attached. I just modified your UserForm little bit.

Sid

Code Used

Option Explicit

Dim i As Long, lastRow As Long
Dim ws As Worksheet
Dim rng As Range, cl As Range

Private Sub UserForm_Initialize()
    Set ws = Sheets("Source")
    PopulateCombo1
    PopulateCombo2
    RemoveDuplicates ComboBox1
    RemoveDuplicates ComboBox2
End Sub

Private Sub CommandButton1_Click()
    If Len(Trim(ComboBox1.Text)) = 0 Or Len(Trim(ComboBox1.Text)) = 0 Then
        MsgBox "Filter criteria cannot be empty"
        Exit Sub
    End If
    ws.Range("A1").AutoFilter Field:=2, Criteria1:="=" & Trim(ComboBox1.Text)
    ws.Range("A1").AutoFilter Field:=4, Criteria1:="=" & Trim(ComboBox2.Text)
    ListBox1.Clear
    Set rng = ws.Range("A1:E1").Offset(1, 0).Resize(ws.UsedRange.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    For Each cl In rng
        With Me.ListBox1
            If cl.Column = 1 Then
                .AddItem cl.Value
                .List(.ListCount - 1, 1) = cl.Offset(0, 1).Value
                .List(.ListCount - 1, 2) = cl.Offset(0, 2).Value
                .List(.ListCount - 1, 3) = cl.Offset(0, 3).Value
                .List(.ListCount - 1, 4) = cl.Offset(0, 4).Value
            End If
        End With
    Next cl
End Sub

Sub PopulateCombo1()
    lastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
    ComboBox1.Clear
    For i = 2 To lastRow
        ComboBox1.AddItem ws.Range("B" & i)
    Next
End Sub

Sub PopulateCombo2()
    lastRow = ws.Range("D" & Rows.Count).End(xlUp).Row
    ComboBox2.Clear
    For i = 2 To lastRow
        ComboBox2.AddItem ws.Range("D" & i)
    Next
End Sub

Public Sub RemoveDuplicates(cmb As ComboBox)
    Dim a As Integer, b As Integer, c As Integer
    a = cmb.ListCount - 1
    Do While a >= 0
        For b = a - 1 To 0 Step -1
            If cmb.List(b) = cmb.List(a) Then
                cmb.RemoveItem b
                a = a - 1
            End If
        Next b
        a = a - 1
    Loop
End Sub

Open in new window

Test-combo-box-form.xlsm
0
 

Author Comment

by:pfmurray
ID: 35206944
Thanks Sid,  I'll look at it now :)
0
 

Author Comment

by:pfmurray
ID: 35208185
Thank you Sid, I was able to adapt the code to do exactlywhat I wanted (instead of populating both combo boxes at once I wanted to populate one, then have the filtered data requeried). Your code for "RemoveDuplicates " was just what I needed. Thank you for saving me so much time :)
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Copy Sheet to New Workbook based on Cell Value 6 58
Excel shared spreadsheet 12 38
LOOK FOR 22 28
Format Control on two Buttons 6 23
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

734 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