Drop Down Box
 

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.

Drop Down Box
 

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 wrote:

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

Drop Down Box
 

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.

"Dave Peterson" wrote:

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 wrote:

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

Drop Down Box
 

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


"amit" wrote:

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.

"Dave Peterson" wrote:

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 wrote:

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

Drop Down Box
 

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.

amit wrote:

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.

"Dave Peterson" wrote:

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 wrote:

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


--

Dave Peterson

Drop Down Box
 

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.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote:

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 wrote:

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

Drop Down Box
 

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.

Tom Ogilvy wrote:

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.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote:

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 wrote:

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


--

Dave Peterson

Drop Down Box
 

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.

"Dave Peterson" wrote:

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.

Tom Ogilvy wrote:

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.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote:

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 wrote:

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


--

Dave Peterson