2 column drop down lists

J

Jeff

I know that I have seen this before, but I can't recall
how to do it. I want to create a drop down list with 2
columns. e.g. column 1 would be state short forms and
column 2 would be the full name.

Any suggestions would be greatly appreciated.

Jeff
 
I

icestationzbra

as far as i know, you would have to use control toolbox list for this
purpose.
 
G

Guest

I'm using excel 2003 and seem to get some error message
when I open it becasue of the vba script. Any idea what
can be done.

Jeff
 
G

Guest

okay realized that security was set too high. It's fixed
now. this works, but I know that I also saw a solution
where in the drop down it was actually two columns, with
only the first column appearing in the cell when selected
(as oppeosed to showing one column that has been merged).

Any ideas?

Jeff
 
J

Jeff

thank you for the quick response on this.

I know that I also saw a solution
where in the drop down it was actually two columns, with
only the first column appearing in the cell when selected
(as oppeosed to showing one column that has been merged).

Any ideas?
 
C

Chip Pearson

You can set the ColumnCount property to 2 and use a 2 column
range as the ListFillRange.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
G

Guest

Chip,

could you give me an example of how I would use the
ColumnCount and ListFillRange or where I can find an
example?

thank you,

Jeff
 
D

Dave Peterson

Show the Control Toolbox Toolbar (not the forms toolbar)

Add the combobox

You'll still be in design mode (a icon on that same control toolbox toolbar)
that you can toggle while you're developing.

Rightclick on that combobox and select properties.

You'll see a bunch of them--including the two that Chip mentioned.
 
P

peterm

Hi Jeff
If you want eg Column 1 NY column 2 New York use a vlookup.
If this is want you want let me know & I will send it.
Peterm
 
B

Bob Phillips

Use a combobox from the controls toolbox, set the data up in a table and set
the ListFillRange property of the combo to that table, and set the
columncount property to 2.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
J

Jim May

Bob:
experimenting with this I also had to change "Bound Column" to 2.
I suppose that only one of the 2 columns in the FillListRange can be
pasted in...
Also, don't suppose that one could (/??? - however ??) have Linked Cell
advance by 1 - Originally set to say B1 and with each select/paste
the data move downward B2, B3, B4 etc,,,
TIA,
Jim
 
B

Bob Phillips

Hi Jim,

The boundcolumn property identifies which column is selected when the
combobox is clicked. So if you want column 1 value, you don't need to set
that property as that is the default. If you want the second, set it to 2,
etc. If you set the BoundColumn value greater than the ColumnCount value,
you get #N/A.

If you want to pick up multiple columns from the combobox, you will need to
manage it within the click event, it only links back to 1 even if you
specify multiples.

There is nothing to automatically do the second part, but you could drive it
yourself in the click event of the combobox (remember this is a control
toolbox combobox) with code like this

Private Sub ComboBox1_Click()
Dim target As String

With Me.ComboBox1
target = .LinkedCell
.LinkedCell = Me.Range(target).Offset(1, 0).Address
End With
End Sub


Regards

Bob
 
J

Jim May

Thanks Bob for the code - I've printed it out so as to review
it between stop-lights (as I now leave for work).
Jim
 
Top