Save combo selection in different rows (Excel

September 27, 2010 at 16:26:07
Specs: Windows XP
I’m using MS Excel 2003 SP3.

My user has asked for a two column combobox in a regular spreadsheet list in order to lookup location codes to store in a column on each row. She wants to be able to see the name associated with the location code, so she does not have to refer to a separate list.

The reference data look like this, and there are 199 rows:
001 Commissioner and Board
002 Special Operations
003 Communications Center
004 Public Affairs
005 Budget Services

I have tried implementing this by placing a combobox at the top of the workbook and freezing the panes, so it is always visible. I’ve gotten the combobox working correctly, populated, and linked to a cell.

*** The problem is that I can’t figure-out how to send the selection from the combobox to different rows – the cell link is hard-coded as a setting on the combo.

One solution would seem to be placing a combo in each cell of a column, but there are too many rows and each combo would have to be reprogrammed.

Another possible solution would be through Data Validation (i.e., right-click on the cell and select “Pick from drop-down list…,” but that method appears not to allow a drop-down having more than just one column, when we need two columns.

Another thing I’ve considered is sending the selection to ‘the previous cell that was current,’ (like Screen.PreviousControl), but I don’t know if that is possible in Excel.

Another possibility would be if I could set the ‘cell link’ of the combo to something relative, like ‘store it in the cell just to the right of this one.’

I would appreciate any help I can get.

I am good with VBA in MS Access but not so much in Excel, so I could handle a code solution.

Thanks a lot.

See More: Save combo selection in different rows (Excel

Report •

September 28, 2010 at 12:21:06
Here is the solution:

Put this code in the ComboBox OnChange event:


Private Sub ComboBox1_Change()
ActiveCell.Value = ComboBox1.Value
End Sub


On the sheet, select the cell in which the code value is to be saved, then make a selection from the ComboBox. The code value will be placed in the active cell.

Report •

September 28, 2010 at 12:31:25
re: "...drop-down list...we need two columns

This won't address your ComboBox issue (hopefully Trip's solution did) but I'll toss this out anyway:

Are you aware that you can create Dependent Drop Downs so that each subsequent Drop Down changes what is available based what is chosen in the previous Drop Down?

For example, if Drop Down 1 contains Fruits and Meats, Drop Down 2 can offer either "Apples and Bananas" or "Beef and Pork", depending on what was chosen in Drop Down 1.

If that is something you are interested in, let us know.

Report •

October 2, 2010 at 22:11:06
Many thanks for your reply, DerbyDad03.

That IS something I would be interested in, but it does not help my original problem.

Note that I supplied my own resolution, once I figured it out, so anyone seeing the post might benefit from it. I am very unskilled with Excel programming, but I'm learning. And, thanks to you for helping me.

I did not realize that the 'activecell' was independent of using the ComboBox that I created, and that I could use the ComboBox without changing the ActiveCell. That is what I did to resolve the problem. I even figured-out how to use OffSet to make sure that I was in the correct column when I selected from the ComboBox.

I love this type of programming but just didn't know much about the Excel object model.

Thank you for helping me.


Report •
Related Solutions

Ask Question