Populating a column from few textboxes (VBA)

Question

I need help with a VBA script. Our teacher has told us to use:

ListBox1.AddItem ComboBox1.Text And Val(TextBox4.text) And ListBox1.AddItem
FormatCurrency(price, 2)

It is supposed to show both the name from ComboBox1, the quantity from TextBox4 and price from TextBox5 (formatted into 'price' variable) but it gives me an error.

I decided to try to use:

ListBox1.AddItem ComboBox1.Text  
ListBox1.AddItem Val(TextBox4.Text)  
ListBox1.AddItem FormatCurrency(price, 2)

But the result is this:

enter image description here

Where as it is supposed to be one next to the other (mew 1 £3.50), instead of one above the other.

My friend helped me out, instead of 'And' I was supposed to use '&' — Gacsam
– Gacsam, Commented Nov 8, 2013 at 12:37
Yes it will work but it will not be aligned. when you have multiple rows :) — Siddharth Rout
– Siddharth Rout, Commented Nov 8, 2013 at 12:38
See the answer that I posted. I have updated with the & example. — Siddharth Rout
– Siddharth Rout, Commented Nov 8, 2013 at 12:46

Siddharth Rout · Accepted Answer · 2013-11-08 12:41:58Z

3

You need to specify the column count and the column width of the listbox in case you want it to behave like a multicolumn listbox.

Try this

Option Explicit

Private Sub UserForm_Initialize()
    ListBox1.ColumnCount = 3
    ListBox1.ColumnWidths = "50;50"
End Sub

Private Sub CommandButton1_Click()
    With ListBox1
        .AddItem
        .List(.ListCount - 1, 0) = ComboBox1.Text  
        .List(.ListCount - 1, 1) = Val(TextBox4.Text) 
        .List(.ListCount - 1, 2) = FormatCurrency(price, 2)
    End With
End Sub

enter image description here

EDIT: Followup from comments.

The reason why you shouldn't use & is because the data in columns will not be aligned if you have multiple rows. See this example

Private Sub CommandButton1_Click()
    With ListBox1
        .AddItem "Sid" & " " & "Rout" & " " & "Sample"
        .AddItem "Hello" & " " & "World" & " " & "Sample"
        .AddItem "Another" & " " & "Example" & " " & "Sample"
        .AddItem "Yet" & " " & "another" & " " & "Sample"
    End With
End Sub

And this is what you will get

enter image description here

edited Nov 8, 2013 at 12:41

answered Nov 8, 2013 at 12:36

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gacsam Over a year ago

I apologise for replying a year later, I could not register for some reason, and didn't actually need help anymore. The reason I had no problem using '&' was because my course required very basic VBA coding, so using '&' was okay. Either way, thank you for the help.

Collectives™ on Stack Overflow

Populating a column from few textboxes (VBA)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related