Select Row based upon selection in Multiselect ListBox using VBA

[pmpatel189]

I have Part IDs in Column C and my userform has Listbox containing Part IDs.

If I select Part ID in Listbox then whole row containing that PartID should be selected.

My Listbox is multiselect. So if I select multiple IDs then it should select corresponding multiple rows

 

[MickG]

Try this:-
Nb:- Do not use the "RowSource" property, the code will fill the listbox with column "C".
Nb:- Place this code in the Userform Module.

Option Explicit
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
Private [COLOR="Navy"]Sub[/COLOR] ListBox1_Change()
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] ListBox1
[COLOR="Navy"]For[/COLOR] n = 0 To .ListCount - 1
    [COLOR="Navy"]If[/COLOR] .Selected(n) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Rng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] Rng = _
        Dic(.List(n)) Else [COLOR="Navy"]Set[/COLOR] Rng = Union(Rng, Dic(.List(n)))
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] With
 [COLOR="Navy"]If[/COLOR] Not Rng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    Rng.EntireRow.Select
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("C1", Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = Dn
[COLOR="Navy"]Next[/COLOR]
 [COLOR="Navy"]With[/COLOR] Me.ListBox1
    .List = Application.Transpose(Dic.Keys)
    .MultiSelect = fmMultiSelectMulti
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[pmpatel189]

Option Explicit
Dim Dic As Object


Private Sub CommandButton1_Click()


Dim Rng As Range, Dn As Range, n As Long, cpart As Range, ws As Worksheet






Set Rng = Range("C1", Range("C" & Rows.Count).End(xlUp))
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
For Each Dn In Rng
Set Dic(Dn.Value) = Dn
Next
With Me.ListBox1
.List = Application.Transpose(Dic.Keys)
.MultiSelect = fmMultiSelectMulti
End With




End Sub


Private Sub ListBox1_Change()
Dim n As Long, Rng As Range


With ListBox1
For n = 0 To .ListCount - 1
If .Selected Then
If Rng Is Nothing Then Set Rng = Dic(.List)
Else
Set Rng = Union(Rng, Dic(.List))
End If
Next n
End With
If Not Rng Is Nothing Then
Rng.EntireRow.Select
End If
End Sub
Private Sub UserForm_Initialize()
Dim cpart As Range, ws As Worksheet


Range("D8", Range("D" & Rows.Count).End(xlUp)).Name = "Dynamic"
Me.ListBox1.RowSource = "Dynamic"




End Sub


Mick I modified code to work with button. But shows Run-time error Object variable or with block variable not set

 

[MickG]

You don't need to do that, please see my original notes

Nb:- Do NOT use the "RowSource" property, the code will fill the listbox with column "C".
Nb:- Place this code in the Userform Module.

The code is written to load columns "C" data to the listbox , and by using "The dictionary", it creates a reference to the address of those cells.
You specifying a "Rowsource" will not let that happen.
I also see your "Rowsource" is shown as column "D" not column "C",as you previously specified ?????

 

[pmpatel189]

I thought i can correct myself but i am totally new to listbox so i couldnt.

Actually i want to fetch p/ns from sheet1 and there are labels for p/ns in sheet2. So basically what i want is list items should fetch p/ns. data dynamically from sheet1 and when i select p/ns. from list box and press button it should select row
For that p/ns.

 

[MickG]

Try this example:-
https://app.box.com/s/lirty7o33z28histtrdnfd5gaqi9rbxt

 

[My Aswer Is This]

I would like to see the answer to this question also. But I do not like clicking on links. Can you not put the code here in this post.

Try this example:-
https://app.box.com/s/lirty7o33z28histtrdnfd5gaqi9rbxt

 

[MARK858]

I would like to see the answer to this question also. But I do not like clicking on links. Can you not put the code here in this post.

The code is the same in the link as in post #2.

 

[pmpatel189]

Thank you very much...MickG . Sorry for the late respond. Code worked !!

 

[MickG]

You're welcome