Text box to List box

dcwinter

Board Regular
Joined
Aug 10, 2007
Messages
118
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi and welcome to the board!! :)

You certainly can and the code will look something like this:
Code:
Private Sub CommandButton1_Click()
ListBox1.AddItem (TextBox1.Value)
End Sub
 
Upvote 0
Thank you very much! Worked first time!

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

:biggrin:
 
Upvote 0
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:
Code:
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:
Code:
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! :)
 
Upvote 0
Brilliant!

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

There's a lot to learn with VBA!
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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