Use VBA to select items in a listbox depending on cell values

[CarolynS]

Hi

I have a listbox within a userform that contains 9 different locations. The user selects the relevant locations and when they submit the user form the the selected values are transferred to a spreadhseet. The layout of the spreadhseet is similar to the table below. The locations are pre-poluated in the spreadsheet as column headers and the value "True" is populated under the relevant header and in the relevant row when the form is submitted.

Now I want to be able to to open a blank version of the userform and pull through the location data in to the list box for a particular Request ID. For exampe, for NR1 I would want locations 1, 4,6 and 7 selected in the listbox. This is where I'm totally stuck! I have the following code to identify the relevant row based on the user entering the request ID in to a search box:

Rw = Application.Match(MyVar, WSi.Range("B:B"), 0)

(my variable is the requst Id entered by the user, WSI is the worksheet. Request ID in my spreadsheet is in column B not column A as it is shown below)

I don't know where to go after this! I was thinking along the lines of If functions to determine if the value is true or not blank for each location but I don't know how to write it in code. Alternatively, if it's not possible to select the list box items I considered populating a text box with the location names, again based on if the relevant cell contains "True" but again couldn't think how to write this

Any help greatly appreciated

Request ID	Location1	Location2	Location3	Location4	Location5	Location6	Location7	Location8	Location9
NR1	True			True		True	True
NR2									True
NR3	True		True
NR4
NR5		True	True			True
NR6								True	True

<tbody>
</tbody>

 

[CarolynS]

Anyone?

 

[MickG]

If I understand you correctly then:-
If you place a combobox (combobox1_) in your userform then place this code in the Userform Module, then on selection from the Combobox the related numbers in the Listbox will be selected.

Option Explicit
Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
ComboBox1.List = Rng.Value
ListBox1.Clear
ListBox1.List = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
ListBox1.MultiSelect = fmMultiSelectMulti
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Dn.Value = ComboBox1.Value [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]For[/COLOR] Ac = 1 To 9
    ListBox1.Selected(Ac - 1) = False
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, Ac).Value [COLOR="Navy"]Then[/COLOR]
        ListBox1.Selected(Ac - 1) = True
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[CarolynS]

Hi Mick

Thanks for your reply. I didn't fully understand what the soltion above was doing. Is it the reference nunber that's in the combo box?

I have however come up with an alternative solution that seems to be working:

        Rw = Application.Match(MyVar, WSi.Range("B:B"), 0)
        
        For x = 0 To 8
        
                If Application.Index(WSi.Range("A:Q"), Rw, x + 9) = "True" Then
                    NLS6.Selected(x) = True
                End If
                
        Next