boweryreserve
New Member
- Joined
- Feb 10, 2014
- Messages
- 19
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.
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.
Code:
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
