VBA List Box Question

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
365
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!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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!
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Well I'll say again - I don't really know the relevance of these textboxes, so I can't really make any suggestions.:)
 
Upvote 0
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...
 
Upvote 0
But what relevance do have they in the code?

Where should there contents go/come from?
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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