Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Boxes getting data from Work Sheet.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Boxes getting data from Work Sheet.
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. "Brian" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Boxes getting data from Work Sheet.
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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Boxes getting data from Work Sheet.
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" wrote: 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. "Brian" wrote in message ... 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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Boxes getting data from Work Sheet.
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. "Brian" wrote in message ... 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" wrote: 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. "Brian" wrote in message ... 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Combo boxes to change options based on other Combo boxes. | New Users to Excel | |||
If excel sheet protected, combo boxes won't work | Excel Discussion (Misc queries) | |||
Clear a set of combo boxes after a Submit of data to a new sheet. | Excel Worksheet Functions | |||
Flaky combo boxes. Work around? | Excel Programming | |||
Flaky combo boxes. Work around? | Excel Programming |