VBA List Box Question

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
360
Hi All,

I have this code that I have pulled from "VBA and Macros for Microsoft Excel" and it works in the context that it is supposed to, however I do have a question.

Private Sub CommandButton1_Click()

Dim lastrow As Long, i As Integer
lastrow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1
Cells(lastrow, 1).Value = TextBox1.Value
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Cells(lastrow, 2).Value = Cells(lastrow, 2).Value & _
ListBox1.List(i) & ", "
End If
Next i
Cells(lastrow, 3).Value = TextBox2

End Sub

My question is this...

Rather than have all of the selected items placed in one cell, separated by a comma, can I actually have each item placed in it's own separate cell, preferably in list format or horizontally if not?

Thanks!
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Short answer - yes.:)

Here's one way.
Code:
Private Sub CommandButton1_Click()
Dim NextRow As Long
Dim I As Long
Dim J As Long

    LastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1
    
    For I = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(I) Then
            
            Worksheets("Sheet1").Cells(LastRow + J, 2).Value = ListBox1.List(I)
            J = J + 1
        End If
    Next I

End Sub
 

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
360
Hi Norie,

It does work...thanks!

It doesn't include the textboxes as in the original so it only adds the text from the listbox.

Also, do you know how to do that way but horizontally? I would like to compare to see how they both operate and look.

Thanks!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
I know it doesn't include the textboxes.:)

That's mainly because I couldn't quite figure out there actual purpose.

They don't actually seem to have any relevance to the listbox, they are pulling and putting values on a worksheet.

As for filling across.
Code:
Worksheets("Sheet1").Cells(LastRow, 2 +J).Value = ListBox1.List(I)
 

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
360

ADVERTISEMENT

The textboxes, when completed, will contain text that a user will manually enter. They are very important to be included in the same format as the original code. My big task was being able to have the listbox separated as you have done for me. I am not sure how to reintegrate the textbox code into what you have provided which is why I need the help.

Thanks
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Like I said I didn't include the textboxes because I was unsure of their purpose, also you didn't mention them in your original code.

Can't you just add the code for them before and after the loop just like in the original code?
 

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
360

ADVERTISEMENT

Actually I did figure out that I could readd the code as before however this causes a problem for the code that has been written for horizontal placement.

It will always place text from textbox2 in the 3 column, however I would like textbox2 to show up at the end of everything from the listbox when placed horizontally.

That is my issue.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Well I'll say again - I don't really know the relevance of these textboxes, so I can't really make any suggestions.:)
 

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
360
The relevance of the textboxes is that they are part of a survey. The listboxes contain a variety of skillsets and competencies that a person may have and they need to be able to select more than one. The various textboxes will include information like their name or other miscellaneous skills or competencies that aren't included in the survey and can only be typed in. I want the entire survey completed in the userform and the results will be stored in a hidden sheet.

Hopefully that provides a little more insight...
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
But what relevance do have they in the code?

Where should there contents go/come from?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

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