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/...ternative.html
"Johnny" wrote:
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