Text box to List box

[dcwinter]

Hi,

I'd like to set up a text box which will transfer an entry into a list box after the user selects an 'Add' button. Can you do this within a Userform?

Any help would be greatly appreciated.

 

[Lewiy]

Hi and welcome to the board!!

You certainly can and the code will look something like this:

Private Sub CommandButton1_Click()
ListBox1.AddItem (TextBox1.Value)
End Sub

 

[dcwinter]

Thank you very much! Worked first time!

And to remove an item from a list box? (Should have added that to the first question!

 

[Lewiy]

Personally what I would do is rather than add an item to the ListBox itself, I would add the item to a worksheet somewhere and then link the RowSource property of the ListBox to the range on the worksheet where you add these items. As an example, let’s say you make Column A in Sheet1 your place for putting the list and you start with a heading in cell A1 (which won’t appear in the list):

To add an item to the list:

Private Sub CommandButton1_Click()
Dim Limit As Long
Limit = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Sheet1").Cells(Limit, 1) = TextBox1.Value
ListBox1.RowSource = "Sheet1!A2:A" & Limit
End Sub

To remove an item from the list (note this is based on you selecting an item in the ListBox before pressing the button:

Private Sub CommandButton2_Click()
Dim DeleteRow As Long
Dim Limit As Long
DeleteRow = ListBox1.ListIndex + 2
If DeleteRow < 2 Then Exit Sub
Sheets("Sheet1").Rows(DeleteRow).Delete shift:=xlUp
Limit = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
ListBox1.RowSource = "Sheet1!A2:A" & Limit
End Sub

Hope that helps!

 

[dcwinter]

Brilliant!

Thanks very much. I'll come here more often!!

There's a lot to learn with VBA!