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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,222,181
Messages
6,164,426
Members
451,894
Latest member
480BOY

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
Back
Top