Combobox in each wks
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.Combo Box.1", Link:=False, _ DisplayAsIcon:=False, Left:=59.75, Top:=5.25, Width:=114, Height:= _ 20.25).Select Next End Sub |
Combobox in each wks
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 -- Regards, Tom Ogilvy "teresa" wrote in message ... 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.Combo Box.1", Link:=False, _ DisplayAsIcon:=False, Left:=59.75, Top:=5.25, Width:=114, Height:= _ 20.25).Select Next End Sub |
All times are GMT +1. The time now is 07:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com