Combobox in each wks

[teresa]

I have 12 worksheets, I need to create a combobox in each one with
ListFill Range From A1:A12 in "Utlities" worksheet (separate ws)
Each worksheet should also be named from the A1:A12 range,
e.g.A1 = Thomas
Therefore wks 1 should be called Thomas

the code below doesnt quite work...

Sub combo()
Dim Wks As Worksheet
For Each Wks In Worksheets
Range("a1").Select
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=59.75, Top:=5.25, Width:=114, Height:= _
20.25).Select

Next
End Sub

 

[Tom Ogilvy]

With 13 sheets in the workbook, 1 named Utilities and on Utilities, I had
the names of the month in A1:A12, this worked for me:


Sub combo()
Dim Wks As Worksheet
Dim i As Long
Dim ole As OLEObject
i = 0
For Each Wks In Worksheets
If LCase(Wks.Name) <> "utilities" Then
Wks.Activate
i = i + 1
Range("a1").Select
Set ole = ActiveSheet.OLEObjects.Add(ClassType:= _
"Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=59.75, _
Top:=5.25, Width:=114, Height:=20.25)
ole.ListFillRange = "Utilities!A1:A12"
Wks.Name = Worksheets("Utilities").Cells(i, 1).Value
End If
Next
End Sub