How do I Keep a Combo Box "Focused" on difrferent cells.

[scottia]

Is there anyway to select a cell, say B1, then click a selection from a Combo
Box and have it populate B1 with that value. I know how to add one Combo Box
and associate it with 1 cell but I want to select say cell B2, select an
entry from the same Combo Box nd have that value populate B2 with the new
entry vs. changing B2 to the B1 Combo Box value.

I basically want 1 Combo Box on a sheet, select different cells in the same
sheet and have those values populate the cell based on the Combo Box
selection.

I don't think I can do it but wanted to ask.

 

[Héctor Miguel]

hi, !

embed a combobox from "controls toolbar" (populate it as needed)
and use the '_selectionchange' event in "that" sheet to set it's "LinkedCell" property

hth,
hector.

__ OP __

 

[scottia]

Hi,

I am not familiar with the coding for a Combo Box from "Controls Toolbar".
Can you give me some generic code I can modify with the tab names, etc.?

 

[Héctor Miguel]

hi, !

I am not familiar with the coding for a Combo Box from "Controls Toolbar".
Can you give me some generic code I can modify with the tab names, etc.?

assumptions:
- there is a list-range in any worksheet named "TheList" as the source for a ComboBox1 control embedded in your worksheet
- you want the combo linked only if activecell is over column A (otherwise the combo has nothing to select from)
- also, if you type something *out of* the list, activecell will accept this entry (but will be left out of the list)

hth,
hector.

copy/paste (or tpe) the following in "the sheet" code module where the combo is embedded

' fill the combo only if activecell is in column A
Private Sub ComboBox1_GotFocus()
If ActiveCell.Column = 1 Then
ComboBox1.ListFillRange = "TheList"
ComboBox1.LinkedCell = ActiveCell.Address
Else: ComboBox1.LinkedCell = ""
End If
End Sub

' empty the combo once a selection is made and/or activecell "moved"
Private Sub ComboBox1_LostFocus()
ComboBox1.ListFillRange = ""
ComboBox1.LinkedCell = ""
ComboBox1 = ""
End Sub

' use {enter} key to accelerate the selection from the combo
Private Sub ComboBox1_KeyDown( _
ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then SendKeys "{Esc}"
End Sub

__ previous __

 

[scottia]

Perfect !! Working great. I really appreciate your help.

 

[Héctor Miguel]

hi, !

Perfect !! Working great. I really appreciate your help...

thanks for your feed-back

regards,
hector.

p.s. don't ever think again that "something" can not be done in excel...
exceptional exceptions can be made, but no so much (afaik) )