Drop Down Box

[amit]

hi - I want to use a combo box from form control in excel, ive done combo
boxes in userforms and i'm comfortable with vba when working with userforms
(me.combobox.value = 1 etc), but using a combo box seperately in excel is
something new to me.

I've got a drop down box, which feeds off a range, what i'm looking to do is
put a change event on the drop down and pick the selected value from drop
down and place it into a cell in a sheet.

With Worksheets("sheet1")

..Range("B3") = .DropDowns("Drop Down7").Value

End With

any help would be miuch appreciated.

 

[Dave Peterson]

You may want to drop the dropdown and use a combobox from the Control toolbox
toolbar.

Then you can assign the linked cell to what you want. You won't need any code
at all.

 

[amit]

hi Dave - thx for the quick response - i'm using a combo box.

i'm able to reference to a range cell for the combo box, this is straight
forward, what i then want to do is put a change event sub that will take the
current selection from the drop down and place the value to a particular cell.

 

[JLGWhiz]

This would be the general idea for what you want. You might want to narrow
the target range down a bit more. The way it is written, any change other
than cell A2 will put the CB value into A2.

Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("A2") Then
Sheets(1).Range("A2") = Sheets(1).ComboBox1.Value
End If
End Sub

 

[Dave Peterson]

Why use code at all?

If you don't want to change the linked cell's location, you could use a formula
to retrieve the value from the linked cell.

=if(sheet99!a1="",sheet99!a1)

where sheet99!a1 is the linked cell.

Somethings are easier than code.

 

[Tom Ogilvy]

Dave,

Are you saying you can't link a forms dropdown control to a cell. I don't
recall having a problem doing that (xl2003 and earlier). I guess I am
missing the advantage of an ActiveX control in the situation described.

 

[Dave Peterson]

If the OP wanted to see the value displayed in the combobox (or dropdown), then
using code seems to be overkill to me.

And I thought that the OP would have an easier time just using a linked cell for
that combobox instead of using a linked cell and formula in another cell.

And quite honestly, I'm kind of confused about what control the OP used. In the
original post, it sounded like it was a dropdown from the Forms toolbar.

In the followup, it sounds like a combobox from the control toolbox toolbar.

ps.

Welcome back!

pps. To Amit:

If you used a dropdown from the Forms toolbar with the input range assigned to
A1:A10 and the linked cell B1, you could use this in C1 to get the value shown
in the dropdown:

=if(b1="","",index(a1:a10,b1))

In any case, I still think using code is adding a layer of complexity that isn't
required.

 

[amit]

Thx Tom, Dave for your help.

I choose to use a Combo Box from the ActiveX control, good thing abt this si
that i was able to pull the current selection from the combo box and drop it
into a cell in a different worksheet.

I found that Active Control vba is a lot more familiar territory becos i use
them in Forms.

THanks again for your responses, really do appreciate it.