Populating a combo box

[easybpw]

Hi everyone! I appreciate all the help everyone offers. This is by far the best forums around!

My question is I am trying to populate a combo box with a list of customers that are found in column A of sheet 2 of the workbook. I have a userform set up already but do not know the first thing about populating the combo box. I have read many posts on here and even though they seem very similiar I can not understand what to do. What is suppose to happen is from the userform I select the customer I want, then along with the customer and the data I have inputed it would go to the appropriate cells in sheet 1. Well most of that works for me but I can't get the combo box to populate with my customer list. Any ideas?

Bill

 

[atmospheric]

Create your combo box (I'm assuming from the Forms Tollbar), Right Click it, select Control Tab. Populate the box from your list of customers (input range), enter the Cell Link (Where you want the answer to end up) and decide the number of line in the box. The combo box will place the POSITION of the selected customer in the Cell link.

Is this what you want, or would a simple drop down box be better, whereby the customer name is actually placed in the cell link?

 

[atmospheric]

OK, just re-read your post, looks like you need the drop down list:

Select the cell where you want the list to appear. Click Data/Validation/Settings select List from the "Allow" option.

The "Source" is your list of customers.

Don't think you can select your data for the customers as well without using VBA

 

[Tom Urtis]

Maybe atmospheric sees something I don't; it looks like you want to populate a combobox from a userform because you wrote "I have a userform set up already but do not know the first thing about populating the combo box."

Here are 4 ways, among several:

In the VBE, right click on your combobox control, left click on Properties, and for the RowSource property, enter Sheet2!A1:A30 or whatever your range is.

The RowSource approach works for set ranges but it is not flexible and it's generally a good idea to define the population in the userform's Initialization event. So 3 more procedures, either one of which (but only one at a time) you'd place in the userform's module, while KEEPING THE ROW SOURCE PROPERTY EMPTY. Assuming your combo box's name is ComboBox1, this will populate the combobox from data that resides in sheet2 from A1:A30:

Private Sub UserForm_Initialize()
Dim i As Integer
For i = 1 To 30
ComboBox1.AddItem Worksheets("Sheet2").Cells(i, 1).Value
Next i
End Sub


If your source range is dynamic, that is if the last row of data changes from time to time on sheet 2 but begins in cell A1 and goes to A whatever, this code would accommodate that:

Private Sub UserForm_Initialize()
Dim x As Worksheet, i As Integer
Set x = Worksheets("Sheet2")
i = 1
With ComboBox1
.Clear
Do Until IsEmpty(x.Cells(i, 1))
.AddItem (x.Cells(i, 1))
i = i + 1
Loop
End With
End Sub



If your source data is not contiguous, that is if there are blank cells among A1:A30, this would accommodate that, by only populating for occupied cells:

Private Sub UserForm_Initialize()
Dim cell As Range
For Each cell In Sheets("Sheet2").Range("A1:A30")
If Not IsEmpty(cell) Then ComboBox1.AddItem cell
Next cell
End Sub

Maybe these ideas can give you a start. If you get stuck post back. In each initialization example, add
ComboBox1.ListIndex = 0
at the end of the procedure to show the first item in the combobox when you open the userform.

 

[atmospheric]

Maybe I simplified the question, or perhaps I didn't understand it at all!!

What does easybpw think?

 

[easybpw]

Atmospheric and Tom,

Thanks to both of you for your replies. Tom, you are correct in your thinking. I indeed am using a userform and am having huge difficulties populating the box. I used Toms code for a dynamic range but got another error in another part of my code so now I am struggling mightly to figure that out. I am very green at this and this really is my first project I am attemting to do on my own. The code I have is this:

Private Sub UserForm1_Initialize()
Dim x As Worksheet, i As Integer
Set x = Worksheets("Customer List")
i = 1
With ComboBox1
.Clear
Do Until IsEmpty(x.Cells(i, 1))
.AddItem (x.Cells(i, 1))
i = i + 1
Loop
End With
TextBox1.Value = myDate
ComboBox1.Value = myName
OptionButton1.Value = myLocation
TextBox3.Value = myRemnant
TextBox4.Value = myEndLot
TextBox5.Value = myInvoice
ToggleButton1.Value = myExempt
TextBox6.Value = myPayment
TextBox7.Value = myPrice


End Sub




As you can see I tried to implement what Tom said into my coding but it is not working. Any suggestions would be great!

Bill

 

[Tom Urtis]

My code populates a combobox, period. In it, you added all this other stuff...

TextBox1.Value = myDate
ComboBox1.Value = myName
OptionButton1.Value = myLocation
TextBox3.Value = myRemnant
TextBox4.Value = myEndLot
TextBox5.Value = myInvoice
ToggleButton1.Value = myExempt
TextBox6.Value = myPayment
TextBox7.Value = myPrice

...that apparently deals with what other controls should equal based on what looks like corresponding variables. None of those variables are declared in the Initialization Sub, so I have no idea what you are attempting to achieve with all that. If you have not declared those variables then Excel won't know what you mean with including it in the code.

I suggest you explain what controls you have on your userform, what you want them to equal, or what purpose they are meant to serve, and then also explain under what conditions - - that is, what would trigger - - when those controls are supposed to act the way you want them to, or do what you want them to do.

As a start, if you simply use the code I posted, it will work if your source data is indeed in Sheet2 column A starting in A1, and if your combobox is indeed named ComboBox1. After that, each control will need to be considered. Take a look at Excel's on-line Help while in the VBE for keywords such as your control names, and post back with specific questions if you get stuck.

 

[mjbeam]

Bill,

Before you add extra code to the UserForm_Initialize event, make sure that what you have there now doesn't cause an error. Try just the code that Tom recommended first. Then if that is working for you with no errors, then start adding your other code a line or two at a time. If you get an error, solve that first before adding any additional code.

Also, if yu get an error, post the error code and description as well as the line that the error occured on so that we can have a better idea of what the problem is.


-Mike (at work)

 

[easybpw]

Everyone,

Thanks to all. Tom, I did what Mike suggested and removed what I had and just applied what you gave me and yes, it worked as you knew it would. I guess I was under the assumption that I had tell what each box represented. Again, I don't know much about certain things and am trying to learn about them. I just thought that by entering "ToggleButton.Value = myExempt" for instance meant I was telling it that the button represented myExempt. Is there a place that I must do something like this? My userform has 7 text boxes, a toggle button, an option button, and a combo box.

Thanks for taking the time to help. It is appreciated!

Bill

 

[nisht]

There are different ways and means to populate combobox in excel.. i tried to find a solution for populating combobox and ended with several methods how a combobox can be populated.. i have file named "Different ways to populate combobox" at www.pexcel.com/download.htm

It is really interesting i hope it can help you..