Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 683
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,565
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 119
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 683
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,565
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Getting Combo boxes to change options based on other Combo boxes. Ancient Wolf New Users to Excel 1 March 27th 09 07:29 PM
If excel sheet protected, combo boxes won't work kiwimark Excel Discussion (Misc queries) 2 November 21st 08 02:18 AM
Clear a set of combo boxes after a Submit of data to a new sheet. Jonah Excel Worksheet Functions 0 March 3rd 08 01:05 AM
Flaky combo boxes. Work around? rjamison Excel Programming 0 June 14th 05 12:14 AM
Flaky combo boxes. Work around? Don Wiss Excel Programming 1 April 20th 05 02:44 PM


All times are GMT +1. The time now is 08:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"