Print Macro

Re: Print Macro (Additional Question)

smithj83 said:

How would you tweak this macro so that when you click the command button, your hidden worksheets are in the list of sheets that can be printed as well as the active non-hidden sheets?

Thanks in advance for any assistance.

Click to expand...

Probably better to start your own topic on this subject cross referencing to this thread if you want...

I realize this is a very old post, but I need help with a very similar problem. I too used the same from http://www.j-walk.com/ss/excel/tips/tip48.htm and encountered the same issue. In addition, the last worksheet prints even though it was not selected. I am having trouble incorporating the code listed here. I would like to include the fix as well as include the ability to select a printer, the number of copies, the ability to have all selected sheets print sequentially (and be numbered as such), and an option to check all of the checkboxes. I have included my attempt (some sections are duplicated/commented out as I tried to correct). Any help is appreciated. Thank you.

FYI - I am in no way a programmer. This is a crude attempt at pasting different code I have come across.

Private Sub CommandButton1_Click()

Application.Dialogs(xlDialogPrinterSetup).Show

' 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
Dim Numcop As Long
Application.ScreenUpdating = False

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

' Add a temporary dialog sheet
' Set CurrentSheet = ActiveSheet
' Set PrintDlg = ActiveWorkbook.DialogSheets.Add

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
x = CurrentSheet.Name
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 sheets 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

' get the number of print copies for each report
Numcop = Application.InputBox("Enter number of copies to print:", _
"How Many Copies?", 1, Type:=1)
If Numcop = 0 Then
ElseIf Len(Numcop) > 0 Then
End If

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 copies:=Numcop
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
' CurrentSheet.Activate

' Reactivate original sheet
Sheets(x).Select

End Sub

Welcome to MrExcel majopa!
To get a better answer to your question you should do the following:

1) This is a very old thread started 6 Years ago. You shouldn't reply to threads this old, but rather create your own thread.

2) When posting wrap your code like this:

I actually did start a new thread. I was unsure how to delete my previous post though.
Thank you

Perfect.
You can't delete threads here, but you can put a link to your new thread.

Example:
Click HERE to go to help with majopa's new thread.