Combo Boxes getting data from Work Sheet.

[Brian]

I have a User Form that has a combo Box on it. Is it possible for the Combo
Box to get it's Data from a Work Sheet in the same Work Book as the User Form
instead of doing in Code as follows:

With Me.Engineer_2
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
End With

The reason I ask is because I would like to be able to set it up as follows:
Combo Box 1 = Customer 1
Combo Box 2 = State
Combo Box 3 = Site ID

User Picks the Customer, then the State for that Customer, then the Site ID
for that Customer.

Each State (9-States) has about 450 Sites for each of the 4 Customers, so
writing it in Code would take forever. Plus I already have all the Data in a
Work Book, so all I have to do is add a Sheet for each customer and Copy &
Paste the Data.

I want to use the Comboxes as a process of elimanation to narrow it down.

 

[JLGWhiz]

The only advantage of using the List or AddItem methods is that you can add
and delete items from the ComboBox, whereas, if you use the RowSource or
ListFillRange methods to populate the ComboBox then you cannot add and
delete items from the ComboBox. When using RowSource and ListFill range,
the worksheet range has to be modified to change the ComboBox content. That
said, you can use the RowSource or ListFillRange, as applicable, to populate
the ComboBox from the same workbook.

In the initialize event for the form:

Me.ComboBox1.RowSource = "Sheet1!A2:A11"

The above code would load data from Range("A2:A11") of sheet 1 in the active
workbook into the ComboBox1.

To set up the three controls as you have described and use the RowSource
method, you would need to list your customers in a single colum, the states
in a single column and the site Id in a single column with empty cells
purged and each column sorted alpha/numerically for best performance of the
ComboBox.

If this does not answer the question, then provide more detail.

 

[Leung]

I think you can put below code so the combo box can add item of cell value
in the range of source_list.

For Each cell In Range("source_list").Cells
ComboBox1.AddItem cell.Value
Next

 

[Brian]

If you had to do it what would you do it? Would you type some 4000 Names,
Address, State, Zip, etc....?

Would it be better to give each customer there own Module?

 

[JLGWhiz]

I would definitely devise a means to avoid typying something that had
already been typed once. But I cannot see what you are working with, have
no idea why you are attempting to do what you are and do not know what
constraints or lattitudes apply, so it presents a problem in determining
exactly what I would do. But, I can nudge you aloing on the code, if you
keep feeding me data.