VBA, Print The Sheets That Were Selected in the UserForm into 1 PDF

[morgoyo]

Hi everyone,

I am trying to make a userform where each sheet has its own corresponding checkbox. The goal is to print the selected sheets into a pdf upon clicking OK button.

I have managed to somewhat achieve my goal. But it feels like it is far from being optimal. I had to assign 2 different arrays and for loops. One to get the number and name of the sheets to be printed. A second one to assign the data received from the first for loop. And use that second array as the main array for the print function. I had to hardcode the range of the first array as I don't know how to do it more dynamically. As of right now my code is as follows:

Private Sub OKButton_Click()
    
    Dim names(20) As String, isheets() As String
    Dim iCount As Long, i As Long
    Dim checkbox        As Control
    
    iCount = 0
        
        For Each checkbox In Me.Controls
            If checkbox.Value = True Then
            iCount = iCount + 1
            names(iCount - 1) = checkbox.Caption
            End If
        Next checkbox
    
    ReDim isheets(1 To iCount)
    
        For i = 1 To iCount
        isheets(i) = names(i - 1)
        Next i
    
    ThisWorkbook.Sheets(isheets).PrintOut

End Sub

What I ask is how can I make it so that I don't need a second array and a second for loop? Do I have to actually hardcode the first array's range? Or is above code ideal or foolproof in terms of what I am trying to do?
Honestly, I have only a very basic understanding of VBA. I had to google and check every function and how to use them for everything on this code until I was able to do what I needed. But, I don't trust myself with the code, it returned all sorts of errors in each and every step until I finally got to above state. It feels like it will end up giving some errors in a way that I don't know how

Thanks in advance,
Michael

 

[John_w]

Try this:

Private Sub OKButton_Click()

    Dim c As Control
    Dim sheetsList As String
  
    sheetsList = ""
    For Each c In Me.Controls
        If TypeName(c) = "CheckBox" Then
            If c.Value Then sheetsList = sheetsList & c.Caption & "/"
        End If
    Next
  
    If sheetsList <> "" Then
        ThisWorkbook.Sheets(Split(Left(sheetsList, Len(sheetsList) - 1), "/")).PrintOut
    Else
        MsgBox "Nothing to print because no sheets selected", vbExclamation
    End If

End Sub

 

[morgoyo]

Try this:

Private Sub OKButton_Click()

    Dim c As Control
    Dim sheetsList As String
 
    sheetsList = ""
    For Each c In Me.Controls
        If TypeName(c) = "CheckBox" Then
            If c.Value Then sheetsList = sheetsList & c.Caption & "/"
        End If
    Next
 
    If sheetsList <> "" Then
        ThisWorkbook.Sheets(Split(Left(sheetsList, Len(sheetsList) - 1), "/")).PrintOut
    Else
        MsgBox "Nothing to print because no sheets selected", vbExclamation
    End If

End Sub

I didn't know I can refer to the checkboxes like that. I have tried everything that I can think of to do that and it was exhausting This works great, thanks!

 

[morgoyo]

Upon John's answer I have further worked on the code. I have tried to make sure that the user is prompted with a message to get a final confirmation and that the procedure stops when a cancel button is clicked. I am attaching it here in case anyone else needs something like that. I would also appreciate it if anyone has anything to add or correct.

Private Sub OKButton_Click()
    
    Dim names           As String
    Dim checkbox       As Control
    Dim fileSave        As Variant
    Dim msg             As Integer
    Dim actvsheet       As String
    
Application.ScreenUpdating = False

'Get the active sheet name to return to the current sheet after the task is done.
    actvsheet = ThisWorkbook.ActiveSheet.name
    
'Let's the user choose the path to save the file
    Set fileSave = Application.FileDialog(msoFileDialogSaveAs)

'The UserForm has checkboxes. Each checkbox has a caption after a sheetname. This for loop checks which sheets are selected by the user to be printed.
    For Each checkbox In Me.Controls
        If TypeName(checkbox) = "CheckBox" Then
    
            If checkbox.Value = True Then
                names = names & _
                        checkbox.Caption & ","
            End If
        End If
    Next
'Makes sure that if no boxes were selected the process stops.
    If Len(names) > 1 Then
        names = Left(names, Len(names) - 1)
        Else:

Application.ScreenUpdating = True
        Exit Sub
    
    End If
'Creates an array of the selected sheets and selects the corresponding sheets.
    Sheets(Split(names, ",")).Select
'Prompts user with an are you sure message. Shows the name of the selected sheets.
    msg = MsgBox("These documents will be printed as PDF " + Chr(10) + Replace(names, ",", Chr(10)), vbQuestion + vbOKCancel)
'If User choses ok proceeds with the printing.
    If msg = vbOK Then
'Let's the user choose a directory to save the file
        With fileSave
        
            .InitialFileName = "Desktop\*.pdf"
'FilterIndex for a PDF file is 26 (You can count which row is a file type at when you Save As to get the desired file type's Index number.
            .FilterIndex = 26
'If user choses OK on the Save as screen.
                If .Show = -1 Then

                    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=.SelectedItems(1), _
                    Quality:= _
                    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                    OpenAfterPublish:=True
                    
                    Sheets(actvsheet).Select
'If User choses cancel on the Save as screen.
                Else
'Returns to the sheet that was active when the code was started (Makes sure multiple sheets are not selected when the procedure is over.)
                    Sheets(actvsheet).Select
Application.ScreenUpdating = True
                    Exit Sub
                End If
        End With
'If the user chooses Cancel to the msgBox, cancels the printing.
    Else
    Sheets(actvsheet).Select
Application.ScreenUpdating = True
    Exit Sub
    End If

Application.ScreenUpdating = True
Unload Me
End Sub