Combo box?
 

I am trying to creat a calculator of U values (heat loss) and would like
to use a pull down menu (like in a combo box) for thicknesses of a
composite material to give differing values in another cell. For
example, if I pull down to 20 I would like 0.34 to appear in another
cell and if I pulled down to 30 I would like 0.37 to appear etc. Does
anyone have any ideas or know where I might look? I am a complete
novice on this side of excel so any help much appreciated!


--
MWP
------------------------------------------------------------------------
MWP's Profile: http://www.excelforum.com/member.php...o&userid=34159
View this thread: http://www.excelforum.com/showthread...hreadid=539221

Combo box?
 

On Fri, 5 May 2006 07:03:33 -0500, MWP
wrote:


I am trying to creat a calculator of U values (heat loss) and would like
to use a pull down menu (like in a combo box) for thicknesses of a
composite material to give differing values in another cell. For
example, if I pull down to 20 I would like 0.34 to appear in another
cell and if I pulled down to 30 I would like 0.37 to appear etc. Does
anyone have any ideas or know where I might look? I am a complete
novice on this side of excel so any help much appreciated!

One way

Build a table of values. e.g.

A1=20, B1=.34
A2=30, B2=.37

etc., and name this range say "data"

Now with your combobox set the following properties.

ControlSource: Sheet1!D1
BoundColumn: 2
ColumnCount: 1

When you invoke the combobox you should now see the values in A1:A2 in
the drop down list. Choosing one and dismissing the control will put
the appropriate value from B1:B2 in cell D1.

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Combo box?
 

Oops - sorry, forgot to add the most important property.

set the RowSource property of the combobox to Sheet1!Data

Assuming of course that the table is on Sheet1


Rgds

On Fri, 05 May 2006 14:11:01 +0100, Richard Buttrey
wrote:

On Fri, 5 May 2006 07:03:33 -0500, MWP
wrote:


I am trying to creat a calculator of U values (heat loss) and would like
to use a pull down menu (like in a combo box) for thicknesses of a
composite material to give differing values in another cell. For
example, if I pull down to 20 I would like 0.34 to appear in another
cell and if I pulled down to 30 I would like 0.37 to appear etc. Does
anyone have any ideas or know where I might look? I am a complete
novice on this side of excel so any help much appreciated!

One way

Build a table of values. e.g.

A1=20, B1=.34
A2=30, B2=.37

etc., and name this range say "data"

Now with your combobox set the following properties.

ControlSource: Sheet1!D1
BoundColumn: 2
ColumnCount: 1

When you invoke the combobox you should now see the values in A1:A2 in
the drop down list. Choosing one and dismissing the control will put
the appropriate value from B1:B2 in cell D1.

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Combo box?
 

Thanks for your help, I have (I think) done as you said but in the
properties window I don't have a 'control source' box or a 'Row Source'
box to input those properties....so I don't know where to go? I don't
suppose you have an example you might be willing to mail me I could
look over and figur out from there do you? I know its a big ask!! if
possible my addy is
Thanks again
Matt


--
MWP
------------------------------------------------------------------------
MWP's Profile: http://www.excelforum.com/member.php...o&userid=34159
View this thread: http://www.excelforum.com/showthread...hreadid=539221

Combo box?
 

On Fri, 5 May 2006 08:44:15 -0500, MWP
wrote:


Thanks for your help, I have (I think) done as you said but in the
properties window I don't have a 'control source' box or a 'Row Source'
box to input those properties....so I don't know where to go? I don't
suppose you have an example you might be willing to mail me I could
look over and figur out from there do you? I know its a big ask!! if
possible my addy is
Thanks again
Matt

Yes sure. I'll email it over shortly.

These are properties of the ComboBox object in the VBA window.

Make sure you've got both the Project Explorer WIndow open and also
the Properties window, which is usually stacked under the Explorer
window to the left of the big code window on the right.

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________