Populate combobox from table if offset cell contains certain data

[Diving_Dan]

Hi all,

I've got the following code that is working fine for me but I need to adapt it and not quite sure how. At present it puts all data from column 8 of a table into a combobox.

What I would like it to do is only pull data from column 8 of the table if column 2 of that row contains "A", "B" or "C".

Private Sub cmdViewAmendStaff_Click()
        
    With frmSelectStaff
    .OptionButton1.Value = True
    .cboStaff.List = Sheets("A Team").ListObjects("Table1").ListColumns(8).DataBodyRange.Value
    .Show
    End With
    
End Sub

Any help is appreciated as always.

 

[Fluff]

Are the values in col 8 unique?

 

[Diving_Dan]

The values in column 8 are all unique. They contain staff names and. Column 2 contains their pay grade, I only want to populate the combobox with staff who has a paygrade of A, B or C.

 

[Fluff]

OK, how about

Private Sub cmdViewAmendStaff_Click()
   Dim Cl As Range
      
   frmSelectStaff.OptionButton1.Value = True
   With CreateObject("scripting.dictionary")
      For Each Cl In Sheets("A Team").ListObjects("Table1").ListColumns(8).DataBodyRange
         Select Case Cl.Offset(, -6).Value
            Case "A", "B", "C"
               .Item(Cl.Value) = Empty
         End Select
      Next Cl
      frmSelectStaff.cboStaff.List = .Keys
    End With
    frmSelectStaff.Show
End Sub

 

[Diving_Dan]

Thanks Fluff, that works great. However I've just realised that the code actually needs to go into when the userform initializes. I was going to adapt that code for a slightly different job.

I've tried putting your code into the userform Initialize sub but the combobox remains empty, not sure where I'm going wrong.

 

[Fluff]

How about

Private Sub UserForm_Initialize()
   Dim Cl As Range
      
   Me.OptionButton1.Value = True
   With CreateObject("scripting.dictionary")
      For Each Cl In Sheets("A Team").ListObjects("Table1").ListColumns(8).DataBodyRange
         Select Case Cl.Offset(, -6).Value
            Case "A", "B", "C"
               .Item(Cl.Value) = Empty
         End Select
      Next Cl
      Me.cboStaff.List = .Keys
    End With
End Sub

 

[Diving_Dan]

Thanks as always Fluff, works perfectly

 

[Fluff]

You're welcome & thanks for the feedback.