How can I hide hidden worksheets in print dialgue check box

if you can post the code that populates the print dialogue box user form then we can elaborate on that.

this will get you the answer you are looking for faster than a trial & error process to fit a general solution to your setup.

Here is what I am using.

Option Explicit

Sub SelectSheets()
    Dim i As Integer
    Dim TopPos As Integer
    Dim SheetCount As Integer
    Dim PrintDlg As DialogSheet
    Dim CurrentSheet As Worksheet
    Dim cb As CheckBox
    Application.ScreenUpdating = False

'   Hides the intro requiring macros and development pages

    Worksheets("Intro").Visible = False
    Worksheets("DEVELOPMENT - ERASE").Visible = False
   
'   Add a temporary dialog sheet
    Set CurrentSheet = ActiveSheet
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add

    SheetCount = 0

'   Add the checkboxes
    TopPos = 40
    For i = 1 To ActiveWorkbook.Worksheets.Count
        Set CurrentSheet = ActiveWorkbook.Worksheets(i)
'       Skip empty sheets and hidden sheets
        If Application.CountA(CurrentSheet.Cells) <> 0 And _
            CurrentSheet.Visible Then
            SheetCount = SheetCount + 1
            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
                PrintDlg.CheckBoxes(SheetCount).Text = _
                    CurrentSheet.Name
            TopPos = TopPos + 13
        End If
    Next i

'   Move the OK and Cancel buttons
    PrintDlg.Buttons.Left = 240

'   Set dialog height, width, and caption
    With PrintDlg.DialogFrame
        .Height = Application.Max _
            (68, PrintDlg.DialogFrame.Top + TopPos - 34)
        .Width = 230
        .Caption = "                     SELECT WORKSHEETS TO PRINT"
    End With

'   Change tab order of OK and Cancel buttons
'   so the 1st option button will have the focus
    PrintDlg.Buttons("Button 2").BringToFront
    PrintDlg.Buttons("Button 3").BringToFront

'   Display the dialog box
    CurrentSheet.Activate
    Application.ScreenUpdating = True
    If SheetCount <> 0 Then
       
       If PrintDlg.Show Then
            For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOn Then
                    Worksheets(cb.Caption).Select Replace:=False
                End If
            Next cb
            ActiveWindow.SelectedSheets.PrintOut
            ActiveSheet.Select
    End If
 
    Else
        MsgBox "All worksheets are empty."
    End If

'   Delete temporary dialog sheet (without a warning)
    Application.DisplayAlerts = False
    PrintDlg.Delete

'   Reactivate original sheet
    Application.GoTo Sheet1.Range("A1"), True
End Sub

strange, the lines

 If Application.CountA(CurrentSheet.Cells) <> 0 And CurrentSheet.Visible Then
            [...]
End If

Select allOpen in new window

do work at my workplace (excel 2010).

There are multiple levels of invisibility however, so you could try changing

[...] and currensheet.visible then [...]

Select allOpen in new window

to

[...] and currentsheet.visible = xlVisible then [...]

Select allOpen in new window

I tried replacing that line.  There was no difference.

Remember that the check box program does work, and only shows the worksheets that were not specifically hidden.  

The problem is with ones that are only "temporarily" invisible based on the command buttons.  The tabs for those worksheets will not show, but the check box is still seeing them.

Can you post the code of the command buttons that 'temporarily' make the sheets invisible ?
If the above does not work then we'll have to figure out what these command buttons are doing to the worksheets in order to make the printbox feature work for those sheets as well.

Here is where they go.  The buttons were just an inserted shape that I assigned a macro to.

There are actually 40 pages to hide and 20 to show based on the button.  I cut the number down here to make it easy - you can see the pattern.



Option Explicit

Public Sub OpenForm()

Dim objFrm As Object

    Set objFrm = New frmHide
   
    objFrm.Show
   

End Sub
'This is the module that will allow sheets to be hidden or visible based on the command button'

Public Sub WJSheetVisible()

Worksheets("KAPs").Visible = False
Worksheets("KACH ").Visible = False
Worksheets("KAAp").Visible = False
Worksheets("WIPs").Visible = False
Worksheets("WICH ").Visible = False
Worksheets("WIAp").Visible = False
   
Worksheets("WJPs").Visible = True
Worksheets("WJCH ").Visible = True
Worksheets("WJAp").Visible = True

End Sub

Public Sub WISCSheetVisible()
   
Worksheets("KAPs").Visible = False
Worksheets("KACH ").Visible = False
Worksheets("KAAp").Visible = False
Worksheets("WJPs").Visible = False
Worksheets("WJCH ").Visible = False
Worksheets("WJAp").Visible = False

Worksheets("WIPs").Visible = True
Worksheets("WICH ").Visible = True
Worksheets("WIAp").Visible = True

End Sub

Public Sub KABCSheetVisible()

Worksheets("WJPs").Visible = False
Worksheets("WJCH ").Visible = False
Worksheets("WJAp").Visible = False
Worksheets("WIPs").Visible = False
Worksheets("WICH ").Visible = False
Worksheets("WIAp").Visible = False

Worksheets("KAPs").Visible = True
Worksheets("KACH ").Visible = True
Worksheets("KAAp").Visible = True

End Sub

Would it make sense to change these to some type of very hidden sheet?

Worksheets("WJPs").Visible = False



I tried doing it, but I was not having any luck.

I used the following sub in one attempt, but since I am still new at this stuff I don't know if modifying it would work correctly (maybe a sub for each sheet?).  I did use it and managed to hide stuff I couldn't find (but fixed that) :-)


Sub Hide_WS2()
    Worksheets(2).Visible = xlVeryHidden
End Sub

I tried the

Visible = xlSheetVeryHidden

It did not make any difference

So the idea is like this:

WJSheetVisible makes the kaps sheet invisible

Worksheets("KAPs").Visible = False

Select allOpen in new window

but SelectSheets finds it visible

 If [...] And CurrentSheet.Visible Then [...]

Select allOpen in new window

then in between something must have set the visibility of the sheets to "true"

by 'something' i mean either one of the macro code routines inside of the worksheet, or a macro code routine inside add-ins or attached modules.

I am thinking I may not have given you all of the code to see what is happening.  Here are two more where Visible / Hidden shows up in a couple of spots.  I tried doing some changes, but no effect.


Here is the code for the button that brings up the print check box

Option Explicit

Private Sub chk1_Click()
    Call CheckEm(Me.chk1.Value)
End Sub
Sub CheckEm(bVal As Boolean)
'Adapted from Source kgerb (Kyle) : https://www.experts-exchange.com/blogs/dlmille/B_6619-Print-only-certain-sheets-to-PDF-using-ExportAsFixedFormat.html
Dim i As Long
    With Me
        For i = 0 To .lbSheets.ListCount - 1
            .lbSheets.Selected(i) = bVal
        Next i
        .lbSheets.ListIndex = -1
    End With
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cmdPrint_Click()
Dim wkb As Workbook
Dim wks As Worksheet
Dim i As Long
Dim bPrinted As Boolean

    Set wkb = ThisWorkbook
       
    With Me
        For i = 0 To .lbSheets.ListCount - 1
            If .lbSheets.Selected(i) Then
                wkb.Worksheets(.lbSheets.List(i)).PrintOut
                bPrinted = True
            End If
        Next i
    End With
       
    If Not bPrinted Then
        MsgBox "No Worksheets to Print"
    Else
        Unload Me
    End If
End Sub

Private Sub cmdPrintPDF_Click()
Dim wkb As Workbook
Dim wks As Worksheet
Dim fname As String
Dim i As Long
Dim strSheetsToPrint As String

    Set wkb = ThisWorkbook
   
    With Me
        For i = 0 To .lbSheets.ListCount - 1
            If .lbSheets.Selected(i) Then
                If strSheetsToPrint = vbNullString Then
                    strSheetsToPrint = .lbSheets.List(i)
                Else
                    strSheetsToPrint = strSheetsToPrint & "," & .lbSheets.List(i)
                End If
            End If
        Next i
    End With
   
    fname = wkb.Worksheets("INFORMATION").Range("B3").Value
    If strSheetsToPrint <> vbNullString Then
        Call printSheetsToPDF(strSheetsToPrint, fname)
        Unload Me
    Else
        MsgBox "No Worksheets to Publish"
    End If
End Sub


Private Sub UserForm_Initialize()
Dim wkb As Workbook
Dim wks As Worksheet
Dim vWks As Variant
Dim myDict As Object


    Set wkb = ThisWorkbook
    Set myDict = CreateObject("Scripting.Dictionary")
   
    curSheet = wkb.ActiveSheet.Name
   
    For Each vWks In Split(strSheetsNoPrint, ",")
        If Not myDict.exists(vWks) Then
            myDict.Add vWks, Nothing
        End If
    Next vWks
   
    For Each wks In ThisWorkbook.Worksheets
        If Not myDict.exists(wks.Name) Then
            Me.lbSheets.AddItem wks.Name
        End If
    Next wks
   
    myDict.RemoveAll
    Set myDict = Nothing
End Sub

Private Sub UserForm_Terminate()
Dim wkb As Workbook
Dim wks As Worksheet
Dim vWks As Variant

    Set wkb = ThisWorkbook
       
    wkb.Worksheets(curSheet).Activate 'revert to active sheet when userform was initialized
End Sub






Here is the other with hidden - visible related to the print function.


Option Explicit
Public Const strSheetsNoPrint = "Information"
Public curSheet As String
Sub showForm()
    UserForm1.Show

End Sub

Public Function getFileNameOnly(fname As String) As String
    getFileNameOnly = Left(fname, Len(fname) - Len(getFileExt(fname)))
End Function
Public Function getFileExt(fname As String) As String
Dim i As Integer

    i = InStr(StrReverse(fname), ".")
    getFileExt = StrReverse(Left(StrReverse(fname), i))

End Function

Sub printSheetsToPDF(strSheetsToPrint As String, pdfFile As String)
Dim wkb As Workbook
Dim wks As Worksheet
Dim vSheets As Variant
Dim myDict As Object
Dim myDictVisible As Object
Dim i As Long

    Set wkb = ThisWorkbook
    Set wks = ActiveSheet
    Set myDict = CreateObject("Scripting.Dictionary")
    Set myDictVisible = CreateObject("Scripting.Dictionary")
   
    vSheets = Split(strSheetsToPrint, ",")
   
    'capturing visibility
    For Each wks In wkb.Sheets
        myDictVisible.Add (wks.Name), wks.Visible
    Next wks
   
    'make PDF sheets visible
    For i = LBound(vSheets) To UBound(vSheets)
        wkb.Sheets(vSheets(i)).Visible = xlSheetVisible
        myDict.Add vSheets(i), Nothing
    Next i
   
    'make all other sheets not visible
    For Each wks In wkb.Sheets
        If Not myDict.exists(wks.Name) Then
            wks.Visible = xlSheetHidden
        End If
    Next wks
       
    'select sheets to print (not really necessary, as all visible sheets will be printed with the ExportAsFixedFormat method
    'wkb.Sheets(vSheets).Select
   
     
    If Dir(pdfFile) <> vbNullString Then
        Kill pdfFile
        If Err.Number <> 0 Then
            MsgBox "Cannot delete PDF File: " & pdfFile & " You may have it open - close it and try again"
            Exit Sub
        End If
    End If
   
    Err.Clear
    wkb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFile, quality:=xlQualityStandard, includedocproperties:=True, _
                            ignoreprintareas:=False, openafterpublish:=True
                           
    If Err.Number <> 0 Then
        MsgBox "Could not successfully create PDF file, perhaps you need to ensure you're running Excel 2007 with latest patches or Excel 2010"
    End If
    On Error GoTo 0
   
    'restore to prior visibility
    For Each wks In wkb.Sheets
        wks.Visible = myDictVisible(wks.Name)
    Next wks
   
    myDict.RemoveAll
    myDictVisible.RemoveAll
    Set myDict = Nothing
    Set myDictVisible = Nothing
End Sub

is this old code that is related to old tries to get things working ?
if so, in general it would be best to move  it elsewhere for reference purposes, and keep the excel file as clean as possible.

anyway, this code does not interfere with the problem, it does indeed make sheets visible and hides them again, but will not cause problems when it is not called.
Even when the userform is activated and macro code is executed, as long as no error messages are presented to you the sheets that have been made visible are also hidden again.

i posted the code fragments from above in an excel workbook and (with some minor tweaks) everything seems to work as expected.
keep in mind though that the precise location of the code fragments may vary with your version:

ThisWorkbook - SelectSheets (checkboxes for only the visible pages, prints to default printer)
Thisworkbook  - WJSheetVisible displays only 'WJ' sheets (for simplicity this is my A selection)
Thisworkbook  - WISCSheetVisible displays only 'WISC' sheets (for simplicity this is my B selection)
Thisworkbook  - KABCSheetVisible displays only 'KABC' sheets (for simplicity this is my C selection)

Module1 - showForm displays the userform with all sheets (including the invisble ones) and prints out only the selected ones to default printer or pdf file.
working-solution.xlsm

That is the current code that is in the workbook.  Everything works fine (no messages) except for the hidden sheets showing in the check box.

I tried using the code from the solution, but still get the ability to check a box to print a hidden sheet.

The workbook has 12 modules in it.  I went back and deleted each module, then checked to see if it would make a difference to try and isolate where the glitch is.  Nothing made a difference, so I am guessing it must have something to do with the way the 3 check boxes make things visibile and hidden.  Of course when there was not the second and third check boxes (before the addition of the additional worksheets), specifying what was hidden worked fine.  So I am thinking that the macro hides at the beginning, then unhides later on.

Perhaps after each button section there needs to be a command along the lines of "don't see anything else in this macro until a differerent button is pushed."  Or maybe each button needs to be its own module?  (I don't know how to break them out to try that).

This is the code from above that is the 3 buttons.




Option Explicit

Public Sub OpenForm()

Dim objFrm As Object

    Set objFrm = New frmHide
   
    objFrm.Show
   

End Sub
'This is the module that will allow sheets to be hidden or visible based on the command button'

Public Sub WJSheetVisible()

Worksheets("KAPs").Visible = False
Worksheets("KACH ").Visible = False
Worksheets("KAAp").Visible = False
Worksheets("WIPs").Visible = False
Worksheets("WICH ").Visible = False
Worksheets("WIAp").Visible = False
   
Worksheets("WJPs").Visible = True
Worksheets("WJCH ").Visible = True
Worksheets("WJAp").Visible = True

End Sub

Public Sub WISCSheetVisible()
   
Worksheets("KAPs").Visible = False
Worksheets("KACH ").Visible = False
Worksheets("KAAp").Visible = False
Worksheets("WJPs").Visible = False
Worksheets("WJCH ").Visible = False
Worksheets("WJAp").Visible = False

Worksheets("WIPs").Visible = True
Worksheets("WICH ").Visible = True
Worksheets("WIAp").Visible = True

End Sub

Public Sub KABCSheetVisible()

Worksheets("WJPs").Visible = False
Worksheets("WJCH ").Visible = False
Worksheets("WJAp").Visible = False
Worksheets("WIPs").Visible = False
Worksheets("WICH ").Visible = False
Worksheets("WIAp").Visible = False

Worksheets("KAPs").Visible = True
Worksheets("KACH ").Visible = True
Worksheets("KAAp").Visible = True

End Sub

When I download and open the 'working solution.xlsm' from the previous post and execute the thisworkbook - selectsheets macro, the popup displays

- WJPS
- WJCH
- WJAP
- INFORMATION.

Those sheets contain information and are visible.
If you see more sheets than the 4 mentioned, the problem is to be found in one of the addins / templates that you have activated and not in the workbook itself.

You are right when I run from yours, but it did not do it when I put in mine.  I will try it in mine again and see if I did something wrong.

I did seperate the command buttons into seperate modules, and that did not change anything.

I see that you have a part in "this workbook", where I had that section in a module.  Would that make a difference?  I already have some code in "this workbook."  Can I just add that in or does that create an issue?

I'm confused.

I see that it works with your solution workbook.  

I pasted the code from ThisWorkBook in your solution to mine, deleted the module it was in, and replaced my related module code with yours.

I still get the extra boxes.

"If you see more sheets than the 4 mentioned, the problem is to be found in one of the addins / templates that you have activated and not in the workbook itself. "

I don't believe I have any addins or templates.  The worksheets are basically numbers and formulas with an object (arrow) inserted here and there.  Is there something I should look for here that could be a problem?

The location of the code will not be the problem, when you place the 'Thisworksheet' macro code in a module it will still work as it should.

When you open the VBA editor, you can enable the 'project explorer' tab by pressing [Ctrl-R]. I believe that it should already be opened by default however so it may already be present on the left-top-side of the VBA editor.

In the project explorer you can see the code sections of all excell workbooks that you have opened currently. When you close all workbooks, but leave excell running, there might be some projects left in the project explorer tab. These projects should be referenced as 'personal.xlsb' for the standard workbook/worksheets template and possibly a number of '[...].XLAM'  projects for active add-ins.

you could check all code sections in these projects for occurances of 'visible' to see if there is macro code that makes up for this problem.

on the other hand, when running the macro code from my example does seem to work correctly, this more or less indicates that something inside of your version of the workbook is screwing things up.

would it be a possibility to move all data to the example workbook and then macro by macro add in all the stuff that's not in it ?
As a side effect you can then test and double-check each macro fragment that you need to see what is wrong.

I worked on the issue some more.  I deleted all other modules to see if there was a conflict that was causing the issue, and there was no change.  All of the worksheets still were showing for checkboxes.

I also went into the module that was doing the visible / hidden changes from the command buttons and eliminated two of those subs so there was only one group of documents being worked on the assumption that maybe the visible / hide changes for the same worksheets had something to do with it, and again no difference.  All of the worksheets are visible on the print check box.

I seperated the three subs for worksheet display and put them in seperate modules.  No change.

Basically through eliminating everything else in the workbook, it seems that there has to be something in this module that is causing the issue.

Any suggestions?


Option Explicit
Public Const strSheetsNoPrint = "Information,Copyright,Intro,DEVELOPMENT - ERASE," 'put sheet names that don't get the print option, comma separated
Public curSheet As String
Sub showForm()
    UserForm1.Show

End Sub

Public Function getFileNameOnly(fname As String) As String
    getFileNameOnly = Left(fname, Len(fname) - Len(getFileExt(fname)))
End Function
Public Function getFileExt(fname As String) As String
Dim i As Integer

    i = InStr(StrReverse(fname), ".")
    getFileExt = StrReverse(Left(StrReverse(fname), i))

End Function

Sub printSheetsToPDF(strSheetsToPrint As String, pdfFile As String)
Dim wkb As Workbook
Dim wks As Worksheet
Dim vSheets As Variant
Dim myDict As Object
Dim myDictVisible As Object
Dim i As Long

    Set wkb = ThisWorkbook
    Set wks = ActiveSheet
    Set myDict = CreateObject("Scripting.Dictionary")
    Set myDictVisible = CreateObject("Scripting.Dictionary")
   
    vSheets = Split(strSheetsToPrint, ",")
   
    'capturing visibility
    For Each wks In wkb.Sheets
        myDictVisible.Add (wks.Name), wks.Visible
    Next wks
   
    'make PDF sheets visible
    For i = LBound(vSheets) To UBound(vSheets)
        wkb.Sheets(vSheets(i)).Visible = xlSheetVisible
        myDict.Add vSheets(i), Nothing
    Next i
   
    'make all other sheets not visible
    For Each wks In wkb.Sheets
        If Not myDict.exists(wks.Name) Then
            wks.Visible = xlSheetHidden
        End If
    Next wks
       
    'select sheets to print (not really necessary, as all visible sheets will be printed with the ExportAsFixedFormat method
    'wkb.Sheets(vSheets).Select
   
    On Error Resume Next 'this method must exist, and should in Excel 2007+.  Excel 2007 latest SP has this addin, otherwise, it can be downloaded at http://labnol.blogspot.com/2006/09/office-2007-save-as-pdf-download.html
   
    If Dir(pdfFile) <> vbNullString Then
        Kill pdfFile
        If Err.Number <> 0 Then
            MsgBox "Cannot delete PDF File: " & pdfFile & " You may have it open - close it and try again"
            Exit Sub
        End If
    End If
   
    Err.Clear
    wkb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFile, quality:=xlQualityStandard, includedocproperties:=True, _
                            ignoreprintareas:=False, openafterpublish:=True
                           
    If Err.Number <> 0 Then
        MsgBox "Could not successfully create PDF file, perhaps you need to ensure you're running Excel 2007 with latest patches or Excel 2010"
    End If
    On Error GoTo 0
   
    'restore to prior visibility
    For Each wks In wkb.Sheets
        wks.Visible = myDictVisible(wks.Name)
    Next wks
   
    myDict.RemoveAll
    myDictVisible.RemoveAll
    Set myDict = Nothing
    Set myDictVisible = Nothing
End Sub

Do you call the "printSheetsToPDF" function ?
that is the only code fragment from your last post that does influence the visibility of worksheets. You could make sure that this will not happen by temporarily changing

Sub printSheetsToPDF(strSheetsToPrint As String, pdfFile As String)
Dim wkb As Workbook

Select allOpen in new window

to

Sub printSheetsToPDF(strSheetsToPrint As String, pdfFile As String)
exit sub
Dim wkb As Workbook

Select allOpen in new window

but i guess that will not make the difference.
can you post the stripped down version of your worksheet that does not work correctly ?

Here is the workbook.

All of the other modules are out, and I still get the extra boxes.
Deleting-Modules-Example-2.xlsm

That all makes me now wonder if it is not in the code for the user form.

Is there a line here that needs to change?  Something that keeps all of the sheets visible?




Option Explicit

Private Sub chk1_Click()
    Call CheckEm(Me.chk1.Value)
End Sub
Sub CheckEm(bVal As Boolean)
ExportAsFixedFormat.html
Dim i As Long
    With Me
        For i = 0 To .lbSheets.ListCount - 1
            .lbSheets.Selected(i) = bVal
        Next i
        .lbSheets.ListIndex = -1
    End With
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cmdPrint_Click()
Dim wkb As Workbook
Dim wks As Worksheet
Dim i As Long
Dim bPrinted As Boolean

    Set wkb = ThisWorkbook
       
    With Me
        For i = 0 To .lbSheets.ListCount - 1
            If .lbSheets.Selected(i) Then
                wkb.Worksheets(.lbSheets.List(i)).PrintOut
                bPrinted = True
            End If
        Next i
    End With
       
    If Not bPrinted Then
        MsgBox "No Worksheets to Print"
    Else
        Unload Me
    End If
End Sub

Private Sub cmdPrintPDF_Click()
Dim wkb As Workbook
Dim wks As Worksheet
Dim fname As String
Dim i As Long
Dim strSheetsToPrint As String

    Set wkb = ThisWorkbook
   
    With Me
        For i = 0 To .lbSheets.ListCount - 1
            If .lbSheets.Selected(i) Then
                If strSheetsToPrint = vbNullString Then
                    strSheetsToPrint = .lbSheets.List(i)
                Else
                    strSheetsToPrint = strSheetsToPrint & "," & .lbSheets.List(i)
                End If
            End If
        Next i
    End With
   
    fname = wkb.Worksheets("INFORMATION").Range("B3").Value
    If strSheetsToPrint <> vbNullString Then
        Call printSheetsToPDF(strSheetsToPrint, fname)
        Unload Me
    Else
        MsgBox "No Worksheets to Publish"
    End If
End Sub


Private Sub UserForm_Initialize()
Dim wkb As Workbook
Dim wks As Worksheet
Dim vWks As Variant
Dim myDict As Object


    Set wkb = ThisWorkbook
    Set myDict = CreateObject("Scripting.Dictionary")
   
    curSheet = wkb.ActiveSheet.Name
   
    For Each vWks In Split(strSheetsNoPrint, ",")
        If Not myDict.exists(vWks) Then
            myDict.Add vWks, Nothing
        End If
    Next vWks
   
    For Each wks In ThisWorkbook.Worksheets
        If Not myDict.exists(wks.Name) Then
            Me.lbSheets.AddItem wks.Name
        End If
    Next wks
   
    myDict.RemoveAll
    Set myDict = Nothing
End Sub

Private Sub UserForm_Terminate()
Dim wkb As Workbook
Dim wks As Worksheet
Dim vWks As Variant

    Set wkb = ThisWorkbook
       
    wkb.Worksheets(curSheet).Activate 'revert to active sheet when userform was initialized
End Sub

I tried pasting your code and into this file, and imported your user form.

Even more confusing.  It works fine in yours, but the same code pasted in here gets an error code.

If I do the "showform" it shows all of the sheets.
Deleting-Modules-Example-3.xlsm

You were right about the extra sheet.  Did fix the one error.

What I think I am seeing after trying a number of things is that when the print check box showing "Print / PDF" options is up, all of the worksheets show.  But when it is only "Print" then just the active sheets show.

I am guessing the fact that there are two functions in there is the issue?  The "Print" is correct, but the "PDF" shows them all?  

It seems like it might start here - a conflict of visible and hidden

 'make PDF sheets visible
    For i = LBound(vSheets) To UBound(vSheets)
        wkb.Sheets(vSheets(i)).Visible = xlSheetVisible
        myDict.Add vSheets(i), Nothing
    Next i

Could you help me seperate them into two different modules?  I took a shot at it, but was not successful.

Here is the full module



Option Explicit
Public Const strSheetsNoPrint = "Copyright,Intro,DEVELOPMENT - ERASE,End" 'put sheet names that don't get the print option, comma separated
Public curSheet As String
Sub showForm()
    UserForm1.Show

End Sub

Public Function getFileNameOnly(fname As String) As String
    getFileNameOnly = Left(fname, Len(fname) - Len(getFileExt(fname)))
End Function
Public Function getFileExt(fname As String) As String
Dim i As Integer

    i = InStr(StrReverse(fname), ".")
    getFileExt = StrReverse(Left(StrReverse(fname), i))

End Function

Sub printSheetsToPDF(strSheetsToPrint As String, pdfFile As String)
Exit Sub
Dim wkb As Workbook
Dim wks As Worksheet
Dim vSheets As Variant
Dim myDict As Object
Dim myDictVisible As Object
Dim i As Long

    Set wkb = ThisWorkbook
    Set wks = ActiveSheet
    Set myDict = CreateObject("Scripting.Dictionary")
    Set myDictVisible = CreateObject("Scripting.Dictionary")
   
    vSheets = Split(strSheetsToPrint, ",")
   
    'capturing visibility
    For Each wks In wkb.Sheets
        myDictVisible.Add (wks.Name), wks.Visible
    Next wks
   
    'make PDF sheets visible
    For i = LBound(vSheets) To UBound(vSheets)
        wkb.Sheets(vSheets(i)).Visible = xlSheetVisible
        myDict.Add vSheets(i), Nothing
    Next i
   
    'make all other sheets not visible
    For Each wks In wkb.Sheets
        If Not myDict.exists(wks.Name) Then
            wks.Visible = xlSheetVeryHidden
        End If
    Next wks
       
    'select sheets to print (not really necessary, as all visible sheets will be printed with the ExportAsFixedFormat method
    'wkb.Sheets(vSheets).Select
   
    On Error Resume Next 'this method must exist, and should in Excel 2007+.  Excel 2007 latest SP has this addin, otherwise, it can be downloaded at http://labnol.blogspot.com/2006/09/office-2007-save-as-pdf-download.html
   
    If Dir(pdfFile) <> vbNullString Then
        Kill pdfFile
        If Err.Number <> 0 Then
            MsgBox "Cannot delete PDF File: " & pdfFile & " You may have it open - close it and try again"
            Exit Sub
        End If
    End If
   
    Err.Clear
    wkb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFile, quality:=xlQualityStandard, includedocproperties:=True, _
                            ignoreprintareas:=False, openafterpublish:=True
                           
    If Err.Number <> 0 Then
        MsgBox "Could not successfully create PDF file, perhaps you need to ensure you're running Excel 2007 with latest patches or Excel 2010"
    End If
    On Error GoTo 0
   
    'restore to prior visibility
    For Each wks In wkb.Sheets
        wks.Visible = myDictVisible(wks.Name)
    Next wks
   
    myDict.RemoveAll
    myDictVisible.RemoveAll
    Set myDict = Nothing
    Set myDictVisible = Nothing
End Sub

The function that does work is called when you click on the green circle with 'print/pdf' in it. This circle starts the 'SelectSheets' macro code.

When you open the userform in the example however, the code in the userform code section is called which something completely different !

when you also want this code to display only visible worksheets in the userform, change this code

    For Each wks In ThisWorkbook.Worksheets
        If Not myDict.exists(wks.Name) Then
            Me.lbsheets.AddItem wks.Name
        End If
    Next wks

Select allOpen in new window

to

    For Each wks In ThisWorkbook.Worksheets
        If Not myDict.exists(wks.Name) And wks.Visible = True Then
            Me.lbsheets.AddItem wks.Name
        End If
    Next wks

Select allOpen in new window

That didn't change anything for my real workbook.

I apologize, but I am still very basic at the VBA stuff.  But is there a section in the macro that that calls up all of the sheets for the PDF seperate from the print function?  I am still guessing that what is happening is that the print function is working, but then the PDF comes in after with a command to make all sheets visible which is then why we see them all.  I don't know, just a guess.

Again, can you help me seperate the two into seperate modules with the same function of the check box for visible sheets?  That may be the easiest since I can't make any progress with the existing module.

I am not sure I understand the difference.  The green button was just an inserted shape with the macro assigned.  I thought the macro it goes to is the one where the user form comes up.

The main thing I want is to have the check box so that specific visible sheets can be printed.  Again, there are only about 20 visible at a time of the 60, but all 60 show in the check box.
There will be a group of active sheets that basically will never be printed, but may be at some point.

Whatever is the easiest is fine.  I am probably more confused as we go along because the changes seem logical but don't carry over for some reason.

I am looking for the dialog with the 'print' and 'print pdf' options in which you select only visible sheets from a listbox.  The visible are the only sheets to print or publish, none of the hidden sheets.

The ability for PDF publishing of the selected sheets is necessary for my workbook.

As I said above, I really don't mind if it needs to be two seperate macros and buttons.  The single box with the button choice is nice, but simplicity is also ok if the combination is more trouble than it is worrth.

Can you help me split this into two seperate functions (modules) to make it easier?