For Loop not passing caption from a listbox to an array

[boweryreserve]

Greetings,

I’m having an issue wherein a for loop isn’t passing the caption from a listbox to an array. When I add the variable SheetsFound to the Watch window, the value is always empty yet when the loop evaluates whether listbox value is check or unchecked, it verifies which listbox value is checked. I feel the issue resides in Private Sub CommandButton2_Click().

Does anyone know what’s happening here? Many thanks.

Private Sub UserForm_Initialize()
Dim sSheet As Variant
Dim ListItem As Variant
Dim WorksheetCount As Long
Dim iCounter As Integer
    
'For every worksheet in this workbook add the worksheet name as an item in ListBox1.
    For Each sSheet In Sheets
            ListBox1.AddItem sSheet.Name
    Next sSheet
'Count of items in ListBox1 (puts output in Immediate Window)
    Debug.Print ListBox1.ListCount
'##################
'Counts the number of worksheets
    WorksheetCount = ActiveWorkbook.Worksheets.count
    Debug.Print WorksheetCount
'Message box with name of each item in ListBox1 (i.e. the name of each worksheet in workbook)
'    For i = 0 To (ListBox1.ListCount - 1)
'        MsgBox ListBox1.List(i)
'    Next
                                
End Sub
Private Sub CheckBox1_Click()
'CheckBox1 refers to the "Select/Deselect All" check box.
Dim iloop As Integer
    For iloop = 1 To ListBox1.ListCount
        ListBox1.Selected(iloop - 1) = CheckBox1.Value
    Next
End Sub
Private Sub CommandButton1_Click()
    
    Unload Me
End Sub
Private Sub CommandButton2_Click()
  Dim fName As String
  Dim myPath As String
  Dim myOutput As String
  Dim FileNameWithoutExtension As String
  Dim ThisWorkbookPath As String
  Dim FullFileName As String
  Dim SheetsFound() 'This is an array
 
 'Message box with name of each item in ListBox1 (i.e. the name of each worksheet in workbook)
    For i = 0 To (ListBox1.ListCount - 1)
'Asks if the listbox if checked: it it returns false then unchecked; if it returns true, it is checked.
        Debug.Print ListBox1.Selected(i) = True
        Debug.Print Me.Controls(ListBox1.Selected(i)).Caption
    
    Next
  
'Resizes the array to 0 which corresponds to 1. Redim/resize the array to 0, so it will be able to hold 1 item in it.
  ReDim SheetsFound(0)
'For each item in list (starting at 1 [i=0] and ending at the number based on the list count).
  For i = 0 To ListBox1.ListCount
'If the ListBoxItemNumber is checked, then...
    If Me.Controls(ListBox1.Selected(i)).Value = True Then
'What is the Caption of the ListBoxItem?
                Debug.Print Me.Controls(ListBox1.Selected(0)).Caption
                
        SheetsFound(UBound(SheetsFound)) = Me.Controls(ListBox1.Selected(i)).Caption
        ReDim Preserve SheetsFound(UBound(SheetsFound) + 1)
    Else: On Error Resume Next
    End If
  Next i
  ReDim Preserve SheetsFound(UBound(SheetsFound) - 1)
  Sheets(SheetsFound).Select
'########################
'Retrieve ActiveWorkbook's File Path (Displayed in Immediate Window [ctrl + g])
  myPath = ActiveWorkbook.FullName
  Debug.Print "File Path: " & myPath
   
'######### Change Path to Actual Path ############
'    ThisWorkbookPath = ThisWorkbook.Path & "\"                    'Notice the trailing \
    FullFileName = Left(myPath, InStrRev(myPath, ".") - 1) & ".pdf"
    Debug.Print FullFileName
If Not FileFolderExists(FullFileName) Then  'See if the Filename already Exists
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                    FileName:=FullFileName, _
                                    quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                                    OpenAfterPublish:=True
  Else
    MsgBox FullFileName & " already exists"
  End If
End Sub

 

[boweryreserve]

I finally got it to work exactly as I wanted thanks to your help. Thanks very much.