Populating ComboBox with criteria

[jmpatrick]

My search did not come up with a UserForm example, so here I am.

I need to fill a ComboBox with data from a Sheet (Subdivisions) in a Range (SubdivisionsSubNames), but ONLY if there is a Yes in Column 17.

Here's what I have. I'm getting a run-time 424 error. I suspect that With Me.SubName.List is wrong.

Private Sub UserForm_Initialize()

    Set wsSubdivisions = ThisWorkbook.Worksheets("Subdivisions")
    
    Dim c As Range
    With Me.SubName.List
    .Clear
    For Each c In Subdivisions.Range("SubdivisionsSubNames", Subdivisions.Range("SubdivisionsSubNames" & Rows.Count).End(3))
    If c.Offset(0, 17).Value = "Yes" Then
    .AddItem c.Value
    End If
    Next
    End With

End Sub

 

[DanteAmor]

Try this:

Private Sub UserForm_Initialize()
  Dim sh As Worksheet
  Dim c As Range
  
  Set sh = ThisWorkbook.Worksheets("Subdivisions")
  
  With Me.SubName
    .Clear
    For Each c In sh.Range("SubdivisionsSubNames")
      If c.Offset(0, 17).Value = "Yes" Then
        .AddItem c.Value
      End If
    Next
  End With
End Sub

 

[jmpatrick]

Try this:

Private Sub UserForm_Initialize()
  Dim sh As Worksheet
  Dim c As Range
 
  Set sh = ThisWorkbook.Worksheets("Subdivisions")
 
  With Me.SubName
    .Clear
    For Each c In sh.Range("SubdivisionsSubNames")
      If c.Offset(0, 17).Value = "Yes" Then
        .AddItem c.Value
      End If
    Next
  End With
End Sub

No error, but the ComboBox is empty.

 

[DanteAmor]

In which column does the Range (SubdivisionsSubNames) start, in which column are the cells with the word "Yes"?

 

[Akuini]

Assuming Range("SubdivisionsSubNames") refer to col A, the offset should be 16 instead of 17.

 

[jmpatrick]

Assuming Range("SubdivisionsSubNames") refer to col A, the offset should be 16 instead of 17.

That's it! Thanks to both of you. have a great weekend.

 

[DanteAmor]

In which column does the Range (SubdivisionsSubNames) start, in which column are the cells with the word "Yes"?

It is always important to give an example, use the XL2BB tool to paste a minishet of your data:

Dante Amor
	A																Q
1	A																Q
2	1																17
3	a3																q3
4	a4																Yes
5	a5																q5
6	a6																Yes
7	a7																q7
8	a8																Yes
9	a9																q9
10	a10																q10
Hoja5

If the above is correct, then the code:

Private Sub UserForm_Initialize()
  Dim sh As Worksheet
  Dim c As Range
  
  Set sh = ThisWorkbook.Worksheets("Subdivisions")
  
  With Me.SubName
    .Clear
    For Each c In sh.Range("SubdivisionsSubNames")
      If c.Offset(0, 16).Value = "Yes" Then
        .AddItem c.Value
      End If
    Next
  End With
End Sub