ComboBox Drop Down List

[Johnny]

I have 14 Combo Boxes on my Excel form that I want to populate with "Yes" and
"No" with the initialize event. The only way I know how to do it is to use
the following code for each ComboBox.

With ComboBox#
.AddItem "Yes"
.AddItem "No"
End With

Is there simpler code to accomplish this?

Thank you

 

[JLatham]

You can set up a pair of cells in a column in your workbook and give them a
name such as YNList and then your code can read:

Me.ComboBox#.RowSource="YNList"

(of course you can skip the Me. if you want).

The named list can be on any sheet in the workbook, even a hidden sheet.

 

[Dave Peterson]

If your comboboxes are named nicely:

Dim iCtr As Long
For iCtr = 1 To 14
With Me.Controls("Combobox" & iCtr)
.AddItem "Yes"
.AddItem "No"
End With
Next iCtr

 

[john]

Another way perhaps.

Private Sub UserForm_Initialize()
Dim ctl As Control
Dim CtrlType As String

For Each ctl In UserForm1.Controls

CtrlType = TypeName(ctl)

If CtrlType = "ComboBox" Then

ctl.AddItem "Yes"
ctl.AddItem "No"
ctl.ListIndex = 0

End If
Next ctl
End Sub

 

[Jacob Skaria]

'A one time loop will do

'if you are using a user form...
Dim intTemp As Integer
For intTemp = 1 To 14
Me.Controls("Combobox" & intTemp).AddItem "Yes"
Me.Controls("Combobox" & intTemp).AddItem "No"
Next

'If the comboboxes are in Activesheet
Dim intTemp As Integer
For intTemp = 1 To 14
ActiveSheet.OLEObjects("Combobox" & intTemp).Object.AddItem "Yes"
ActiveSheet.OLEObjects("Combobox" & intTemp).Object.AddItem "No"
Next

If this post helps click Yes

 

[r]

assuming the names of the combo as:
ComboBox1, ComboBox2 ... ComboBox14

'in worksheet module
Sub Inizialize_Combo()
Dim oC As Object
For Each oC In ActiveSheet.OLEObjects
AddList oC
Next
End Sub

'in userform module
Private Sub UserForm_Initialize()
Dim oC As Object
For Each oC In Me.Controls
AddList oC
Next
End Sub

'in standard module
Sub AddList(cnt As Object)
Dim v
Dim RE As Object

Set RE = CreateObject("vbscript.regexp")
RE.ignorecase = True
RE.Pattern = "^combobox([1-9]|1[0-4])$"
v = Array("Yes", "No")
If RE.test(cnt.Name) Then
If TypeName(cnt) = "ComboBox" Then
cnt.List = v
ElseIf TypeName(cnt) = "OLEObject" Then
cnt.Object.List = v
End If
End If
End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html