Adding ListBox selections to Column

minimart

New Member
Joined
Apr 30, 2013
Messages
4
Heya!

I am a self-taught newbie to VBA and needing some assistance with a UserForm that is being a bit cranky.

So I have a UserForm that has a number of different objects that will then add those values to Sheet1 (like a database would).

The only piece I am having trouble with is my ListBox. My ListBox auto-populates with values from another sheet, and then the User would be able to select a number of those options.

My problem lies is taking those selections and populating them in Column F.

What I have right now is a mess of nonsense that I've tried to piece together from a variety of resources. Everything but the ListBox text is populating. I'll highlight in red the absolute crazy that I have for that piece, and any tips at all would be absolutely wonderful!

Thanks in advance! :)

Code:
Private Sub CommandButton2_Click()
    Dim x As Long
    Dim i As Integer
    Dim varSelected() As String
    Worksheets("Sheet1").Activate
    LastRow = Sheets("Sheet1").Range("A65535").End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & LastRow).Value = UserForm1.ComboBox1.Text
    Sheets("Sheet1").Range("B" & LastRow).Value = UserForm1.ComboBox2.Text
    Sheets("Sheet1").Range("C" & LastRow).Value = UserForm1.TextBox1.Text
    If UserForm1.OptionButton1 = True Then
        Sheets("Sheet1").Range("D" & LastRow).Value = "Pass"
    ElseIf UserForm1.OptionButton2 = True Then
        Sheets("Sheet1").Range("D" & LastRow).Value = "Fail"
    End If
    If UserForm1.OptionButton3 = True Then
        Sheets("Sheet1").Range("E" & LastRow).Value = "Remote"
    ElseIf UserForm1.OptionButton4 = True Then
        Sheets("Sheet1").Range("E" & LastRow).Value = "In Cube"
    End If
    Sheets("Sheet1").Range("G" & LastRow).Value = UserForm1.TextBox2.Text
    Sheets("Sheet1").Range("H" & LastRow).Value = UserForm1.TextBox3.Text
 [COLOR=#ff0000]   i = i - 1
    With ListBox1
        For x = 0 To .ListCount - 1
        If .Selected(x) Then
            i = i + 1
            ReDim Preserve varSelected(i)
            varSelected(i) = .List(x)
        End If
    Next x
    End With
    Range (Cells(1, 2), Cells("F" & LastRow) = _
    WorksheetFunction.Transpose(varSelected)[/COLOR]
    Range("A1").Select
End Sub
 

Some videos you may like

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.

minimart

New Member
Joined
Apr 30, 2013
Messages
4
Okay! Never mind! That was a pile of nonsense.

I have fixed it to an extent, however, when I select MULTIPLE items from the ListBox, it only outputs the first to column F. Any ideas?

Code:
Private Sub CommandButton2_Click()
    Dim x As Long
    Dim i As Integer
    Dim varSelected() As String
    Worksheets("Sheet1").Activate
    LastRow = Sheets("Sheet1").Range("A65535").End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & LastRow).Value = UserForm1.ComboBox1.Text
    Sheets("Sheet1").Range("B" & LastRow).Value = UserForm1.ComboBox2.Text
    Sheets("Sheet1").Range("C" & LastRow).Value = UserForm1.TextBox1.Text
    If UserForm1.OptionButton1 = True Then
        Sheets("Sheet1").Range("D" & LastRow).Value = "Pass"
    ElseIf UserForm1.OptionButton2 = True Then
        Sheets("Sheet1").Range("D" & LastRow).Value = "Fail"
    End If
    If UserForm1.OptionButton3 = True Then
        Sheets("Sheet1").Range("E" & LastRow).Value = "Remote"
    ElseIf UserForm1.OptionButton4 = True Then
        Sheets("Sheet1").Range("E" & LastRow).Value = "In Cube"
    End If
    Sheets("Sheet1").Range("G" & LastRow).Value = UserForm1.TextBox2.Text
    Sheets("Sheet1").Range("H" & LastRow).Value = UserForm1.TextBox3.Text
    [COLOR=#ff0000]For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then
            Sheets("Sheet1").Range("F" & LastRow).Value = UserForm1.ListBox1.List(i)
        End If
    Next i[/COLOR]
    Range("A1").Select
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

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