ListBox Object and Looping Through 1

[Ebes1099]

I'm having a problem in trying to loop through a listbox. I've never worked with the ListBox before so this is all new to me. I want to check if the value in a cell is found in the listbox, and if it's found, is that item in the list box selected? I get an error on the line If Range("Cert_Act_Hdr").Offset(rowNum, 0).Value = CA_LB.List(a).Value Then, it says Object Required. I can see that me Set statements for the ListBoxes must not be working because both those variables are Null.

Here's a snippet of the code (I haven't written it all yet, right now i'm just testing when Cert_Act is True and Region is False). When I run it, I get an error on the line Set CA_LB = Sheets("Control").Cert_Acty_LB

Sub UpdatePlanSelection()
    Dim rowNum As Integer, numPlans As Integer
    Dim Cert_Act As Boolean, Region As Boolean
    Dim CA_LB As MSForms.ListBox, Reg_LB As MSForms.ListBox
    
    Cert_Act = Range("Cert_Act_TF").Value
    Region = Range("Region_TF").Value
    
    Set CA_LB = Sheets("Control").Cert_Acty_LB
    Set Reg_LB = Sheets("Control").Region_LB
    
    numPlans = Range("total_Plans").Value
    
    For rowNum = 1 To numPlans
        If Cert_Act And Region Then
            'go through each line and match both for a 1, else a 0
        ElseIf Cert_Act And Not Region Then
            
            For a = 0 To CA_LB.ListCount - 1
                If Range("Cert_Act_Hdr").Offset(rowNum, 0).Value = CA_LB.List(a).Value Then
                    If CA_LB.Item(a).Selected = True Then
                        Range("Plan_yn").Offset(rowNum, 0).Value = 1
                    Else
                        Range("Plan_yn").Offset(rowNum, 0).Value = 0
                    End If
                End If
            Next a

 

[SkipVought]

hi,

I mocked up a quick test that runs, using an ActiveX ListBox control named Listbox1.

'
    Dim r As Range, a As Integer
    
    For Each r In Selection         'values on my sheet
        For a = 0 To ListBox1.ListCount - 1
            If r.Value = ListBox1.List(a) Then
                Debug.Print r.Value
            End If
        Next
    Next

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Ebes1099]

I figured it out, Skip. Looking at your code, you aren't doing a ListBox.List(a).Value, the .Value is unnecessary. That was my issue. Thanks!