Subscript out of range when printing a worksheet 1

[humbleprogrammer]

Hello,

I have a form with a combo box that allows you to select the worksheet you want to print. When choosing "ALL", it works correctly but when choosing a specific worksheet, it give a subscript out of range error. Below is my code. Any help is appreciated.

Private Sub btnPrint_Click()
    Dim PrintSheet As String
    PrintSheet = frmSelectSheet.cmbSelectSheet.Value
    If PrintSheet <> "ALL" Then
        Application.ScreenUpdating = False
        frmSelectSheet.Hide
        Sheets(" & PrintSheet & ").Activate
        Sheets(" & PrintSheet & ").PrintOut
        MsgBox PrintSheet & " was successfully printed.", vbInformation, "Successfully Printed"
        frmSelectSheet.Show
        Application.ScreenUpdating = True
    Else
        Application.ScreenUpdating = False
        frmSelectSheet.Hide
        Sheets("SURF WAX").Activate
        Sheets("SURF WAX").PrintOut
        Sheets("SURF ACCESS").Activate
        Sheets("SURF ACCESS").PrintOut
        Sheets("SNOW WAX").Activate
        Sheets("SNOW WAX").PrintOut
        Sheets("SNOW ACCESS").Activate
        Sheets("SNOW ACCESS").PrintOut
        Sheets("SOFTGOODS").Activate
        Sheets("SOFTGOODS").PrintOut
        Sheets("ALOE UP").Activate
        Sheets("ALOE UP").PrintOut
        Sheets("Solarez&ASSi").Activate
        Sheets("Solarez&ASSi").PrintOut
        MsgBox ("All worksheets were successfully printed."), vbInformation, "Successfully Printed"
        frmSelectSheet.Show
        Application.ScreenUpdating = True
    End If
    
End Sub

http://www.gosmart4u.com/

 

[TonyJollans]

Hi humbleprogrammer,

The lines ..

[purple]        Sheets(" & PrintSheet & ").Activate
        Sheets(" & PrintSheet & ").PrintOut[/purple]

.. are trying to Activate and Printout a Sheet with the name between the quotes - [red]" & PrintSheet & "[/red]; it is not recognising your variable because it is inside quotes. You do not have a literal for the sheet name, you have a variable and all you need is ..

[blue]        Sheets(PrintSheet).Activate
        Sheets(PrintSheet).PrintOut[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[humbleprogrammer]

Thanks Tony!!

http://www.gosmart4u.com/