How do i make drop down menus with embedded values?

[kobadb]

Hello,

I’m trying to make a cost estimate program for a pipeline. As a basic feature I would like to use drop down menus to be able to select a pipe from a list of standard ones, and then to enter the length of pipe in another box and to have as a result the cost. So the drop down menu will need to have text to select from (pipe size) and have hidden behind this a cost per meter. This will then be multiplied by the length to give the total cost.

I am new to advanced use of excel (i.e. VB) and have been playing around with ListBox and ComboBox for a few days but can not even add data items to these in an excel sheet. How do I permanently populate a drop down list in an excel sheet and have each item have an embedded value. How do I then access this value to perform calculations with?

Ideally I would like to have the source data for the drop down menu in another sheet for easy updating i.e. the pipe sizes in one column, with the cost in another so that users can easily update these values as market prices change.

Could someone please help me out or point me in the right direction?

Many Thanks,
Kobad

[S@S]

You may try this...

In the cell(s) that you want the list of fixed values, select:

Data--> Validation.
Allow-->List
Source--> "your list of fixed values seperated by , or ;"
or
Source--> "select an area containg the fixed values in some other sheet"