Community archive

Ask something
Discussion Locked

Excel 2000: Combo Box for Data Entry

0 helpful
3 replies

by zeepzip

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,

I am doing adding combo boxes for data entry purposes to save time.
There will be afew columns:
1. Mode of Shipment (column A)
2. Port (column B)
3. Country Code (column c)

etc
etc.

I would like to know if it is possible to have a combo box for each field so that the user can use click on the list from the combo box to select the data needed.
For eg. for Mode of Shipment, "AIR, SEA" will be the option and the user will click the option accordingly.

However, I find it ridiculous to have so many combo boxes in the worksheet.
imagine I have about 11 columns that have designated options and so 11 x (estimated 500 shipments) = 5500 combo boxes !!!

I was testing 2 combo boxes in the Mode of shipment column and I found that when i click AIR, the next combo box below (in the same column) also switch to AIR automatically. > Why is this so ?

QUESTION:
1. What is the best solution for this ? I mean if I can solve for column A B C, then I will use the same soln/concept for the rest of the columns.
2. I am an idiot VBA. can i solve this without having to use any programming ? (if there is no alternative, then I am willing to learn, just tell me then/or recommend any good books)
3. Can solution in (1.) be exported to Access to generate reports ? or I shld use pivot tables ?
4. I have tried using access but access do not allow copy and paste of data simultaneously - need to be done row by row which is very time consuming give that there may be some similar details - such as flight/shipment details but on a different occasion.

Thanks !!!

3 replies

by peteris

Can't you use Validation under Data menu to determine the values that a user may input into a cell?

On a different worksheet (you can also make it hidden) specify data lists for all three columns. When in the main worksheet, specify "List" in the Validation criteria and point to the data lists in the "Source" box. I think that this way is more flexible.

You can also set up a warning dialog box that says, for example, "Shipping method can be AIR or SEA only", and that pops up if a user types in something else, like "CAR" in the cell.

If you use Combo boxes, you have to have a separate combo for each cell because there's a formula which calculates the cell value based on what you click on the combo so, naturally, if you copy the formula to other cells, it will calculate the same value and you will get the same in the whole column.

I think there's no need for VBA, but once you set up the worksheet in a correct way,it can be linked in an Access database and you can use the data from Excel to do Access forms, reports, queries, etc.

Hope this helps.

by bob.jeffery

1) I would ask if yourself, will ever exceed 65536 rows of information, if so, then use Access.

2) Sounds like you might have to do a little programming, you might check out www.j-walk.com that is who I get my books from.

3) You could create a link table to the spreadsheet, then create reports in Access over the link table.

4) Open the table or query you?re trying to copy and paste, then go to tools, office links, then analyze in Excel.

Good Stuff!

by krishn_75

It is true that the Access is not compatible with database storage media. However, Excel is far better than the Access. I would like to storage the mass text/TMO in Excel.

This discussion is closed to new replies.