Combo Box

B

bdehning

I have followed some examples but have not seen what I am looking for.

If I have the following on a worksheet. How do I code or set up a combo box
to show this text in combo box but when selected show the appropriate number
code in the cell?.

Employee Training/ASSE Lunch - 7040000
Business Meetings/Food/Personnel Costs - 7090000
Fee Service/Resources - 7110000
Postage - 7300000
Telephone/Cell - 7310000
Office Supplies - 7330000
Cable/DSL/Publications - 7350000
Auto Maintanance - 7410010
Airfare - 7410015
Lodging - 7410016
Parking, Tolls - 7410017
Entertainment - 7410020
Organization Dues - 7410030
Business Meals - 7410040
Minor Purchase under $500. - 7420020

Help Please
 
D

Dave Peterson

I put a combobox from the controltoolbox toolbar (not the Forms toolbar) on my
worksheet.

I went into design mode and rightclicked on that combobox.
I chose properties

I changed the columncount to 2
I changed the boundcolumn to 2
I changed the listfillrange to sheet1!a1:b15
I changed the linkedcell to A25

I could also change the columnwidths to something like: "15 pt; 0 pt"
to hide those numbers from the combobox.
 
B

bdehning

Dave, I need the ability to have the number show up in the cell that is
clicked to select the description. You way will work if a person wants to
select an item somewhere other than the cell to place the code which due to
the amount of items on the form would be tough.

Know of any way to place into the same cell as the combo box?
 
D

Dave Peterson

Make the linked cell the cell under the combobox?

But then it'll be hidden by the combobox--is that ok?

I'd use an adjacent cell in another column.


Dave, I need the ability to have the number show up in the cell that is
clicked to select the description. You way will work if a person wants to
select an item somewhere other than the cell to place the code which due to
the amount of items on the form would be tough.

Know of any way to place into the same cell as the combo box?
Click to expand...
 
B

bdehning

Dave thanks for the help you did give.

I need the cell to be visible with the code but not show the description. I
have to keep working on the code as I know there is code that can do it.

Thanks again as I did learn something from you on this!
 
D

Dave Peterson

I'm not sure I understand the why's, but you could show the combobox when the
cell is selected, get the information and when the user clicks on another cell,
hide the combobox--so that the cell underneath shows.

I put a combobox (combobox1) from the control toolbox on a worksheet and set it
up as before. I assigned the linked cell the cell beneath the combobox.

Then I rightclicked on the worksheet tab and selected view code. I pasted this
into the code window.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.ComboBox1.Visible _
= Not CBool(Intersect(Target, Me.ComboBox1.TopLeftCell) Is Nothing)
End Sub

I went back to the worksheet--got out of design mode and tested it out.
Dave thanks for the help you did give.

I need the cell to be visible with the code but not show the description. I
have to keep working on the code as I know there is code that can do it.

Thanks again as I did learn something from you on this!
Click to expand...
 
Top