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

waite02

New Member
Joined
Feb 25, 2016
Messages
4
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!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
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:
Code:
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

New Member
Joined
Feb 25, 2016
Messages
4
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

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
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

New Member
Joined
Feb 25, 2016
Messages
4
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

New Member
Joined
Feb 25, 2016
Messages
4
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,579
Messages
5,770,967
Members
425,653
Latest member
UNSING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top