+ Reply to Thread
Results 1 to 5 of 5

vba excel - Filtering userform combobox based on separate combobox using identifiers

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    vba excel - Filtering userform combobox based on separate combobox using identifiers

    I'm trying to modify code from an existing routine I have that works really well for what I want it to do. This one is stumping me though because I am using the same data and code structure and command logic. What's even more confusing is that if I swap data columns, the routine works correctly, but the result becomes part of the "query", and the "query" becomes the result. That's not what I want, since the intended result is needed for calculation and the intended "query" is a string value. The fact that I can switch columns and get it to work, albeit backwards, leads me to believe I'm either missing something or overlooking something very simple. Either way, I've been staring at this thing all day and can't get it to work. Can someone see something I'm not seeing?
    Attached Files Attached Files
    Last edited by terriertrip; 10-03-2017 at 02:24 AM. Reason: title not complete

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: vba excel - Filtering userform combobox based on separate combobox using

    Can you tell us how the code isn't working? Perhaps post some examples of input and expected output.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: vba excel - Filtering userform combobox based on separate combobox using

    I posted an example within the workbook with expected input and output. Please let me know if it's not clear enough. From the form code below with explanation following:
    Private Sub UserForm_Initialize()
        Dim x
        Dim g&, h&, i&, j&
          x = Sheets(msSHEET_LIN).Range("A1").CurrentRegion.Value
          With Me.cboLiner1
            For g = 2 To UBound(x)
                If Not IsEmpty(x(g, 4)) Then
                    .AddItem x(g, 4)    'Liner
                    .List(h, 1) = x(g, 5)    'Liner code
                    h = h + 1
                End If
            Next g
          End With
    End Sub
    
    Private Sub cboLiner1_Change()
        Dim x
        Dim i&, LinerCode$
          If Me.cboLiner1.ListIndex = -1 Then Exit Sub
             Me.cboStrokes1.Enabled = True
             With Me.cboLiner1
                LinerCode = .List(.ListIndex, 1)
             End With
             With Me.cboStrokes1
                .Clear
                For i = 2 To UBound(x)
                  If x(i, 3) = LinerCode Then .AddItem x(i, 1) ''x(i, 3) --> SkLine_Code
                Next i
             End With
    End Sub
    
    Private Sub cboStrokes1_Change()
    
       Dim val as Range
       If Me.cboStrokes1.ListIndex = -1 Then Exit Sub
          Set val = Worksheets(msSHEET_LIN).Columns(1).Find(cboStrokes1.Value, LookIn:=xlValues, Lookat:=xlWhole)
          txtPmpVol1.Value = val.Offset(0, 1)
    End Sub
    A list of 13 pump dimensions (D). Each dimension has an associated code# (1-13) in (E). Another list of pump housing dimensions are in (A) (13 choices). The fact there are 13 pump options and 13 housing
    options is purely coincidental. There are 13 pumps and 13 housings, so there are possible 169 unique outputs with selection of (D & A). These 169 outputs can be parsed into 13 groups in (A) using the associated
    code# from (E). With any configuration of (D) and (A), output volume (B) changes. Each (B) is directly referenced to (A) through the code# from (E).
    e.g. If input 4.5" (D) is selected in combobox1, the result in combobox2 is all housings (A) with code (C) matching code (E). Next, when a housing (A) is selected in combobox2, an output value from (B) populates the textbox.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: vba excel - Filtering userform combobox based on separate combobox using

    You are looking in the entire column and the first match for 2" is in row 2.

    The code needs to be changed so it only looks in the relevant rows based on the Liner Size chosen by the user, try this.

    Private Sub cboStroke1_Change()
    Dim rngStroke As Range
    Dim rngFnd As Range
    Dim idx As Long
    
        idx = cboLiner1.ListIndex
    
        If idx = -1 Then Exit Sub
    
        Set rngStroke = Worksheets(1).Range("A2:A14").Offset(idx * 13)
    
        Set rngFnd = rngStroke.Find(cboStroke1.Value, LookIn:=xlValues, Lookat:=xlWhole)
    
        If Not rngFnd Is Nothing Then
            txtPmpVol1.Value = rngFnd.Offset(0, 1)
        End If
    
    End Sub

  5. #5
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Colorado, US
    MS-Off Ver
    Microsoft 365 Apps
    Posts
    518

    Re: vba excel - Filtering userform combobox based on separate combobox using

    Once again I am in awe. I was wondering if that was the case when I swapped the columns, being the outputs are all unique it listed those no problem. Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Filtering a userform combobox based on another combobox
    By terriertrip in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-22-2017, 02:46 AM
  2. [SOLVED] Userform: Change Combobox list based on a second ComboBox selection
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-11-2016, 03:45 PM
  3. ComboBox values source being changed based on another combobox selection in a UserForm
    By stephenanderson in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-08-2016, 11:12 PM
  4. Userform Combobox to populate based on selection in another combobox
    By Excelnoub in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-14-2014, 03:34 PM
  5. [SOLVED] VBA Userform ComboBox: Content of ComboBox based off of Conditional Statement
    By Brianandstewie in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-16-2013, 03:00 PM
  6. [SOLVED] Excel Userform: Populate other controls (i.e. textbox & combobox) based on combobox select
    By MileHigh_PhD in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-22-2013, 04:50 PM
  7. [SOLVED] Add RowSource to Userform ComboBox based on value on Another ComboBox
    By Baziwan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-10-2012, 01:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1