USER FORM : ListBox population based on another ListBox value selection [VBA]

[sachinns]

Hi,

User Form :

Worksheet :

In my User form there is listbox which consists of 3 values. Rated Current_Order set 1 , Rated Current_Order set 2 , Rated Current_Order set 3.

Based on the selection of these 3 order set , values should populate in the corresponding right side listbox , "Values for the selected attribute" .

The Values are populated from the worksheet "AttrOrderSetDefinition" .(Please refer the screenshot) .

If we select 1st orderset , values from "G3" to its end should populate in the next listbox. if we select 2nd , values from "H3" should populate and similar for the 3rd.

Someone please help me for this code.

westconn1 is my daily savior .

 

[theBardd]

This should do it

Dim rng As Range
    With ActiveSheet
    
        Select Case Me.ListBox1.Value
        
            Case "Rated Current_Order set 1": Set rng = .Range(.Range("G3"), .Range("G3").End(xlDown))
            Case "Rated Current_Order set 2": Set rng = .Range(.Range("H3"), .Range("H3").End(xlDown))
            Case "Rated Current_Order set 3": Set rng = .Range(.Range("I3"), .Range("I3").End(xlDown))
        End Select
    End With
    
    Me.ListBox2.List = rng.Cells.Value

 

[Fluff]

How about

Private Sub ListBox1_Click()
    Me.ListBox2.Clear
    Select Case Me.ListBox1.ListIndex
        Case 0
            Me.ListBox2.List = Range("G3", Range("G" & Rows.Count).End(xlUp)).Value
        Case 1
            Me.ListBox2.List = Range("H3", Range("H" & Rows.Count).End(xlUp)).Value
        Case 2
            Me.ListBox2.List = Range("I3", Range("I" & Rows.Count).End(xlUp)).Value
    End Select
End Sub

 

[dmt32]

Hi,
untested but another way (maybe)

Private Sub ListBox1_Click()
    Dim ws As Worksheet
    Set ws = Worksheets("AttrOrderSetDefinition")
    Me.ListBox2.Clear
    With Me.ListBox1
        Me.ListBox2.List = ws.Cells(3, .ListIndex + 7).Resize(ws.Cells(ws.Rows.Count, .ListIndex + 7).End(xlUp).Row - 2, 1).Value
    End With
End Sub

Dave

 

[sachinns]

@theBardd Thanks but Sorry its not working , throwing me some error. @Fluff Thanks its working . That 3 orderset is populating by selection on previous combobox , But In beginning if i press on orderset 1 it is showing some random values. And if i click on orderset 2nd and if i press again the orderset 1 the values are coming perfect. I will show in the photo attached.

And another one bug is that , That 3 orderset selection is not happening. Suppose if i press orderset 2 the values are changing but that orderset is not like selected. Its not highlighted in the listbox that order 2 is selected. I have a code for the the same listbox after update. The code also i will update below.

Private Sub lsbOrderSet4SelAttr_AfterUpdate()
Dim selVal As String
Dim lc As Long, lr As Long
If Me.txtSelectedAttribute.Text <> "" Then
    selVal = Me.txtSelectedAttribute.Text
    Call listOfSelOrderSet(selVal)
End If

Function listOfSelOrderSet(selVal As String)


Set ws = ThisWorkbook.Worksheets("AttrOrderSetDefinition")
If ws.Visible = False Then
    ws.Activate
End If
On Error Resume Next
ws.Activate
Dim i, lc, lr, j, k
Dim tecAttrVal As String, str As String
Dim rng As Range
Dim startRng As Range
Dim reqCell As Range
Dim Val, var
Dim valList As Collection
Set startRng = Range("A1")
lr = ws.Cells(ws.Rows.Count, startRng.Column).End(xlUp).Row
lc = ws.Cells(startRng.Row, ws.Columns.Count).End(xlToLeft).Column
Me.lsbOrderSet4SelAttr.Clear
For i = 1 To lc
    
    tecAttrVal = Cells(2, i).Value
        For j = 1 To lr
        If tecAttrVal <> "" Then
            If InStr(1, tecAttrVal, selVal, vbTextCompare) Then
                Me.Label3.Visible = True
                Me.lsbOrderSet4SelAttr.Visible = True
                Set rng = ws.Cells(j + 1, i)
                Set valList = New Collection
                If ws.Cells(j + 1, i).Value <> "" Then
                    On Error Resume Next
                    For Each reqCell In rng.Cells
                        If reqCell.Value <> "" Then
                            valList.Add reqCell.Value, CStr(reqCell.Value)
                        End If
                    Next reqCell
                    On Error GoTo 0
                    For Each Val In valList
                        Me.lsbOrderSet4SelAttr.AddItem Val
                    Next Val
                End If
            End If
        End If
        Next j
        tecAttrVal = ""
Next i
End Function

 

[Fluff]

Cross posted http://www.vbforums.com/showthread....-based-on-another-ListBox-value-selection-VBA

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Fluff]

You exlplanation of what sis happening is not very clear to me.
Also the code you have posted is totally incomprehensible, partly because I have no idea what your listboxes are called and partly bacuse I don't understand why you are clearing/repopulating the listbox.

 

[sachinns]

@Fluff I am really sorry for Cross posting. I was not knowing this rule. And the reason why i did is that some times i am not getting solution from mrexcel. So that's why i posted it other forum as well. Sorry for that. I will not do it again. (hoping that i will get solutions from this forum) .

 

[sachinns]

@dmt32 Worked Perfect. Thanks a lot.

 

[sachinns]

Almost everything is working fine in the code now. But small Bug i found is that , there are 3 values in 1st listbox based on these vales the 2nd list box values are populating . The Bug i found is that when i select the values in 1st listbox , values in the 2nd listbox is changing perfectly. But In 1st list box its not looking like selected (because that option is not highlighted) . I will show the photo as well.

You can see in the red circle , actually i selected order set 2 but it is not looking like selected/highlighted (refer Blue circle) but working fine