Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Print multiple sheet dialog

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A friend of mine sent me an interesting procedure that brings up a dialog box displaying the non-hidden worksheets. You tick the box beside the worksheets you wish to print. He got it from one of the Excel tip sites (though he can't remember where).

    Upon running this procedure it seems to work very well except that it always prints last sheet despite being unticked. It also activates the last spreadsheet, instead of returning to the sheet the user had run the macro from.

    If some could provide any tips on how to fix this, I would be very grateful.

    Regards,

    Matthew Keyser

    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

    ' 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

    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
    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:=1
    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
    End Sub

  2. #2
    New Member
    Join Date
    Apr 2004
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default A great way to select sheets to print out!!!

    Hi!

    I'm sorry, i don't have any solution. But if you ever get the answer, please tell about it!!!! It is a great facility!!!!!

    Do you know if it is possible to pre-select all sheet, and let the user deselect the sheets that should not be printed???

    And do you know if it is possible to change the text in the dialogbox? I would like to have it in danish...

    Michael

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •