Combobox populate

[gibbo1715]

i have a toolbox combobox on my worksheet and want to populate it with the names of certain worksheets

Im trying the following but getting errors, any ideas please


For Each WS In ActiveWorkbook.Worksheets
If WS.Range("D1").Value = "CRO Number" Then
ComboBox1.AddItem = WS.Name
End If
Next

[CBrine]

This works with the combobox, I had to set a explict reference to the combobox, so you may need to change the Set cb = to point to your control.


Private Sub CommandButton1_Click()
Dim ws As Worksheet, cb As ComboBox
Set cb = ActiveSheet.ComboBox1
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("D1").Value = "CRO Number" Then
cb.AddItem ws.Name
End If
Next
End Sub


HTH
Cal

[johnske]

Hi gibbo,

And here's another way of doing it:


Private Sub ComboBox1_DropButtonClick()
Dim N%
Dim MyList(10, 1) '< set from 10 to however many you want
For N = 1 To Worksheets.Count
If Sheets(N).Range("D1") = "CRO Number" Then
MyList(N - 1, 0) = Sheets(N).CodeName
End If
Next
ComboBox1.List = MyList
End Sub

[gibbo1715]

hey cal

Thanks for that works a treat

[gibbo1715]

Thanks also Johnski for your version, both work great