I'm not sure I'd use the combobox from the control toolbox toolbar for so many
cells. Too many of these controls can cause nasty things to happen to the
workbook. Lots of them scare me.
But if you want...
Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim OLEObj As OLEObject
With Worksheets("Sheet1")
Set myRng = .Range("b1:B20")
End With
For Each myCell In myRng.Cells
With myCell
Set OLEObj = .Parent.OLEObjects.Add _
(ClassType:="Forms.ComboBox.1", _
Link:=False, displayAsIcon:=False, _
Left:=.Left, Top:=.Top, _
Width:=.Width, Height:=.Height)
OLEObj.LinkedCell = .Cells
'hide the value in the cell??
.Cells.NumberFormat = ";;;"
OLEObj.ListFillRange _
= Worksheets("sheet99").Range("a1:a10").Address(external:=True)
OLEObj.Object.Style = fmStyleDropDownList
OLEObj.Object.MatchEntry = fmMatchEntryComplete
End With
Next myCell
End Sub
====================
Instead, I'd use on combobox and show/hide it whenever you select a cell in that
range.
Create a combobox from the control toolbox toolbar and set it up exactly the way
you want it (size, listfillrange, etc). Call it Combobox1.
Then rightclick on the worksheet tab and select view code. Then paste this code
into the code window that you see:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'hide it to start
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
End If
Me.ComboBox1.Top = .Offset(0, 1).Top
Me.ComboBox1.Left = .Offset(0, 1).Left
Me.ComboBox1.Visible = True
Me.ComboBox1.LinkedCell = .Address(external:=True)
End With
End Sub