Excel VBA, Userform, Listbox, Multi Select, Retrieve results

[waite02]

Good Afternoon -

Very new to VBA. I have managed to create an advanced userform with multiple values, text boxes, combo boxes, multi select list boxes.

I can copy this data into excel.

I can also retrieve the data for the Text Boxes, and Combo boxes back into the User form search for a unique value.

But I need help with the coding to retrieve the values for the Multi select List Boxes.

Spent most of the morning scouring the internet looking for something to give me a direction but just not getting there.

HELP!

 

[RickXL]

Hi and welcome to the MrExcel Message Board,

Here is a quick demo. It uses a UserForm, a Multi-Select ListBox and a Command Button.
The code for the CommandButton is:

Private Sub CommandButton1_Click()
    Dim i As Long
    Dim j As Long
    Columns("B").Clear
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then
            j = j + 1
            Cells(j, "B") = ListBox1.List(i)
        End If
    Next
End Sub

Basically, the listbox has an array of True/False flags that indicate which selections have been made. You need to use that to get the selected items.
I just put them in column B for ease.

 

[waite02]

I'm currently using a Vlookup to retrieve the other fields. It looks like this:

Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 6, False)

Could I still incorporate that?

 

[RickXL]

Maybe. I will need some more information, though.

What is curve?
What are you going to do with the value or values that the VLOOKUP has found?

 

[waite02]

Curve is the name of a Combobox that is the value I am searching for.

That value, is the previously selected listbox items.

The listbox (which is part of a larger userform) is the 7 days of the week. So the values CAN Be Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday.

When the user fills out the userform they can select any combination from that list.

I am trying to recall that specific entry and have those items selected when I recall it. I figured out how to do it for combo boxes and text boxes, but not successfully for listboxes.

 

[waite02]

Private Sub Load_Click()






'For Basic Info Tab
UserForm1.txtLngName.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 1, False)
UserForm1.txtShrtName.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 2, False)
UserForm1.cmbCountry.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 3, False)
UserForm1.cmbIssuer.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 4, False)
UserForm1.cmbCurrency.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 5, False)
UserForm1.TextBox7.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 29, False)
UserForm1.ComboBox28.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 28, False)

'For Des Tab
UserForm1.TextBox3.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 7, False)


'For Meta Data tab
UserForm1.TextBox4.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 8, False)
UserForm1.ComboBox1.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 9, False)
UserForm1.ComboBox3.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 11, False)
UserForm1.ComboBox4.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 12, False)
UserForm1.ComboBox5.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 13, False)
UserForm1.ComboBox7.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 15, False)
UserForm1.ComboBox8.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 16, False)

'For Calc Data Tab
UserForm1.ComboBox34.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 18, False)
UserForm1.ComboBox33.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 19, False)
UserForm1.ComboBox32.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 20, False)
UserForm1.ComboBox31.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 21, False)
UserForm1.ComboBox30.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 22, False)
UserForm1.ComboBox29.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 23, False)
UserForm1.ComboBox11.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 24, False)
UserForm1.ComboBox10.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 25, False)

'For Additional Info Tab
UserForm1.TextBox6.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 26, False)
UserForm1.TextBox7.Value = Application.WorksheetFunction.VLookup(Curve, Range("A1:AC300"), 27, False)




End Sub