combobox selection posts to active cell

[BobDen]

Friends, I am a newbie. I have spent 3 hours on MrExcel and MS Excel help with no answer to what should be a simple question, so I suspect I just don't understand enough.

I have an Excel 2010 spreadsheet where I want to enter data in individual cells by selecting a value in a combo box located in the header section above the freeze pane.

I have figured out how to insert a combo box and link to the lookup range. I cannot figure out how to set the properties to enter the output into the current/active cell.

The use case is: User selects a cell (e.g. B5)in the spreadsheet, scrolls down in the combo box to the desired item, picks the desired item (e.g. the third item in the list), the sequence number of the selected item (e.g. 3 in this case) is entered into the /current/active cell (e.g. B5). User moves to another cell in the spreadsheet (e.g. B6) and repeats the process.

It appears obvious that combo box properties should allow me to specify the cell to receive the combo box output and I can get it to enter into a specific, pre defined cell location, but I do not know how to set the properties to enter the output into whatever cell is current/active.

Thanks in advance for your assistance. BobDen

 

[tlowry]

1. Assign a macro to the listbox, if there isn't one already
2. Copy the line of code below into the macro
3. Change DDBox1 in the line of code to the name of the DropDown control
4. Test

   ActiveCell.Value = Range(ActiveSheet.Shapes("[COLOR=green][B]DDBox1[/B][/COLOR]") _
                        .DrawingObject.LinkedCell).Value

 

[BobDen]

Thanks. Can you point me to information about assigning a macro to the combobox. I can make a macro, but don't know how to link it to the combobox. Thanks...BobDen

 

[tlowry]

There are two basic ways to create a ComboBox. How a macro is assigned depends on how the ComboBox was created.

1. From the "Forms" toolbox:

Right click ComboBox
Assign Macro

​

2. From the "Control Toolbox" toolbox:

View "Control Toolbox"
Go into "Desighn mode" (first icon)
Right click ComboBox
View Code


​

 

[BobDen]

Thank you. I used an activex combobox because I needed to include 2 columns. I will follow your instructions and will report my success! BobDen

 

[BobDen]

Thanks. I have solved my issue. Thank you for your help. BobDen