Insert combo box in a cell

[Guest]

I want to add a combe box to a whole chart, each line should have a combo
box. How can I insert in the cell the combo box? I have a long list and I
need in each cell a combo box. It doesn't make sense to make in each cell
seperatly a combo. Isn't there an easier way?
Also how can I make, that after i pick an item from the combo box and press
enter, it should go to the next cell?
Please help.

 

[Dave Peterson]

Maybe you can use Data|Validation.

See Debra Dalgleish's site for lots of tips:
http://contextures.com/xlDataVal01.html

 

[Guest]

No. I can't use data validation, for a few reasons. One of them because it's
a long list, and I want to be able to start typing in the first few letters,
and the matching word should come up. please help.
Thank You.

 

[Dave Peterson]

Add a combobox from the control toolbox toolbar to your worksheet--set it up
exactly the way you like it. Set the .style, .listfillrange, all that stuff.

Then you can use a worksheet_selectionchange event to move it close to your
selected cell.

Then rightclick on the worksheet tab and select view code. Paste this into the
code window.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.ComboBox1.Visible = False
With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Me.Range("b:b")) Is Nothing Then Exit Sub

Me.ComboBox1.LinkedCell = .Address(external:=True)
With .Offset(0, 1)
Me.ComboBox1.Top = .Top
Me.ComboBox1.Left = .Left
End With
Me.ComboBox1.Visible = True
End With

End Sub

my combobox was named combobox1 and I showed it when I selected a single cell in
column B. And I showed it just to the right of that cell.