rowsourse for specific comboboxes for the same column



I  try  making  the  rousource  for  specific comboboxes (cob1,cob4,cob7)  should populate  from column A for  each comboboxes individually    . so  far  it  gives  error  could  not  fined the specified object in this  line 

 Me.Controls("cbo" & varName).AddItem Range("A" & i)
Const cstrNames As String = "cbo1,cbo4,cbo7"
Dim varName As Variant
Private Sub UserForm_Initialize()
For Each varName In Split(cstrNames, ",")
For i = 2 To 200
If WorksheetFunction.CountIf(Range("A2:A" & i), Range("A" & i)) = 1 Then
 Me.Controls("cbo" & varName).AddItem Range("A" & i)
End If
Next i
Next varName
End Sub

any  suggestion to  fix  it,please?



Selected Answer


I believe the error lies in the declaration:

Const cstrNames As String = "cbo1,cbo4,cbo7"
since Me.Controls("cbo" & varName) will give ComboBox names like cbocbo1, cbocbo4  and cbocbo7. If you have ComboBox names like cbo1, cbo4 and cbo7 then those objects won't exist and you should change the declaration to:
Const cstrNames As String = "1,4,7"
.That said, each ComboBox seems to get the same list of unique values (via the Countif function) currently I think.


Hi John,
thanks for your clarification and answering .
speed (rep: 26) Oct 9, '21 at 5:31 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login