Results 1 to 5 of 5

Printing Worksheets from Userforms

This is a discussion on Printing Worksheets from Userforms within the Excel Questions forums, part of the Question Forums category; I am completely stuck on a VBA problem that I need some help with. Im designing a survey using VBA ...

  1. #1
    New Member
    Join Date
    Oct 2007
    Posts
    22

    Default Printing Worksheets from Userforms

    I am completely stuck on a VBA problem that I need some help with.

    Im designing a survey using VBA Excel.

    Workbook1 is where the questions are, and Workbook2 is where I will capture the survey respondant's answers. The reason why I have done this is to ensure that the clients can email their answers back to us in a much smaller file size than the workbook that the questions are in.

    I have created a print manager form so that the clients can print their answers as they go through the questionnaire.

    This code is launched by a commandbutton in a menu userform where the clients can choose from several different surveys, and once the printing of the document has been executed, I would like it to be able to return to the original "menu" userform.

    Answers in workbook2 are formatted so that they can be printed out on single pages. The problem that I have is that once the printing of the document has been executed, the userforms in workbook1 are unloaded, and the user is returned to the workbook1 worksheet view.

    My question is how do you print an excel worksheet without the userforms in workbook1 being unloaded by the print dialog box?

    In the Userform:



    Option Explicit

    Sub Userform_Initialize()

    LBox1_Fill

    End Sub

    Private Sub CommandButton1_Click()

    Dim i As Integer

    Application.DisplayAlerts = False

    With UserForm1
    .Hide

    Application.Visible = 1

    For i = 0 To .ListBox1.ListCount - 1

    If .ListBox1.Selected(i) Then

    Application.ScreenUpdating = 0

    Sheets(.ListBox1.List(i)).PrintOut

    End If

    Next i



    End With



    End Sub



    Private Sub CommandButton2_Click()

    Unload Me

    End Sub


    Private Sub CommandButton3_Click()

    Application.Dialogs(xlDialogPrinterSetup).Show

    End Sub



    Function LBox1_Fill()

    Workbooks("Insurance Questionnaire Answers.xls").Activate

    Dim sht As Variant

    With UserForm1.ListBox1
    For Each sht In Sheets
    If sht.Visible Then
    .AddItem sht.Name
    .MultiSelect = 1
    If sht.Name = ActiveSheet.Name Then
    .Selected(.ListCount - 1) = True
    .ListIndex = .ListCount - 1
    End If
    Err.Clear
    Next sht
    If .ListCount = 0 Then
    UserForm1.CommandButton1.Visible = 0
    .AddItem "No Sheets found to Print."
    Else
    .TopIndex = .ListIndex
    End If
    End With

    End Function


    Function Print_Job()

    Dim i As Integer

    Application.DisplayAlerts = False
    With UserForm1
    .Hide
    Application.Visible = 1
    For i = 0 To .ListBox1.ListCount - 1
    If .ListBox1.Selected(i) Then
    Application.ScreenUpdating = 0
    Sheets(.ListBox1.List(i)).PrintOut
    End If
    Next i
    End With

    End Function



    In ThisWorkbook:



    Option Explicit

    Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Application.EnableEvents = False
    Cancel = True
    UserForm1.Show
    Application.EnableEvents = True

    End Sub

    My second question which falls into the "nice to have" but isnt really crutial to the whole project, as without it, I will simply hide the worksheets that I dont want to be visible on the fly. Is it possible to add individual worksheet names to a listbox as opposed to just all of the visible worksheets, or the activeworksheets?

    I would be extremely grateful for any assistance that anyone can give me,

    Many Thanks

    Merryn

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824

    Default

    Hi Merryn.

    Your form is not being unloaded when printing but simply hidden. Why must you hide it while printing to begin with?

    I also do not understand this bit of code...

    Code:
    If sht Is ActiveSheet Then
        .Selected(.ListCount - 1) = True
        .ListIndex = .ListCount - 1
    End If
    Or this either?

    Code:
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
        Cancel = True
        UserForm1.Show
    End Sub
    Which workbook contains this code? What is the purpose?

    I edited some of this...
    UserForm1 code:
    **Option Explicit

    **Sub Userform_Initialize()
    ****** LBox1_Fill
    **End Sub

    **Private Sub CommandButton1_Click()
    ****** Print_Job
    **End Sub

    **Private Sub CommandButton2_Click()
    ****** Unload Me
    **End Sub

    **Private Sub CommandButton3_Click()
    ****** Application.Dialogs(xlDialogPrinterSetup).Show
    **End Sub

    **Function LBox1_Fill()
    ****** Dim sht As Worksheet, Wb As Workbook

    ****** Set Wb = Workbooks("Insurance Questionnaire Answers.xls")
    ****** Wb.Activate

    ****** With UserForm1.ListBox1
    ********** For Each sht In Wb.Worksheets
    ************** If sht.Visible = xlSheetVisible Then
    ****************** .AddItem sht.Name
    ****************** .MultiSelect = 1
    ****************** If sht Is ActiveSheet Then
    ********************** .Selected(.ListCount - 1) = True
    ********************** .ListIndex = .ListCount - 1
    ****************** End If
    ************** End If
    ************** Err.Clear
    ********** Next sht
    ********** If .ListCount = 0 Then
    ************** UserForm1.CommandButton1.Visible = 0
    ************** .AddItem "No Sheets found to Print."
    ********** Else
    ************** .TopIndex = .ListIndex
    ********** End If
    ****** End With
    **End Function

    **Function Print_Job()
    ****** Dim i As Integer

    ****** Application.DisplayAlerts = False
    ****** With UserForm1
    ********** .Hide
    ********** Application.Visible = 1
    ********** For i = 0 To .ListBox1.ListCount - 1
    ************** If .ListBox1.Selected(i) Then
    ****************** Application.ScreenUpdating = 0
    ****************** Sheets(.ListBox1.List(i)).PrintOut
    ************** End If
    ********** Next i
    ****** End With
    **End Function

  3. #3
    New Member
    Join Date
    Oct 2007
    Posts
    22

    Default

    Hi Right Click -

    Thanks for your response. I dont want to hide it - that's the point.

    Imagine that you are a user, you are completing a survey, and you go to print, and the survey suddenly disappears! That is exactly what I want to avoid from happening.

    Im trying to find a method to avoid that. I didnt see the

    With UserForm1
    .Hide

    Part before - ironically have been staring at it for hours... anyway, I will remove that bit, but I dont think that it will solve my whole problem yet.

  4. #4
    New Member
    Join Date
    Oct 2007
    Posts
    22

    Default Have solved it - this code will let you print from worksheet

    This code is for a print manager, which produces a print preview, allows the user to select which sheets they wish to print preview, as well as the printer and, when the print has been completed, or the print preview window is closed will return the user back to the original userform.

    Option Explicit

    Sub Userform_Initialize()

    LBox1_Fill

    End Sub

    Private Sub CommandButton1_Click()

    Dim i As Integer
    Application.DisplayAlerts = False

    With UserForm1
    .Hide
    Application.Visible = 1
    For i = 0 To .ListBox1.ListCount - 1
    If .ListBox1.Selected(i) Then
    Application.ScreenUpdating = 0
    frmPeople.Hide
    Sheets(.ListBox1.List(i)).PrintPreview
    Workbooks("My New VBA Project version 4.9.xls").Activate
    frmPeople.Show
    End If
    Next i

    End With

    End Sub

    Private Sub CommandButton2_Click()

    frmPeople.Show
    Unload Me

    End Sub

    Private Sub CommandButton3_Click()

    Application.Dialogs(xlDialogPrinterSetup).Show

    End Sub

    Function LBox1_Fill()

    Workbooks("Insurance Questionnaire Answers.xls").Activate
    Dim sht As Variant
    With UserForm1.ListBox1
    For Each sht In Sheets
    If sht.Visible Then
    .AddItem sht.Name
    .MultiSelect = 1
    If sht.Name = ActiveSheet.Name Then
    .Selected(.ListCount - 1) = True
    .ListIndex = .ListCount - 1
    End If
    End If
    Err.Clear
    Next sht
    If .ListCount = 0 Then
    UserForm1.CommandButton1.Visible = 0
    .AddItem "No Sheets found to Print."
    Else
    .TopIndex = .ListIndex
    End If
    End With
    End Function

    Function Print_Job()
    Dim i As Integer
    Application.DisplayAlerts = False
    With UserForm1
    .Show
    Application.Visible = 1
    For i = 0 To .ListBox1.ListCount - 1
    If .ListBox1.Selected(i) Then
    Application.ScreenUpdating = 0
    Sheets(.ListBox1.List(i)).PrintOut
    End If
    Next i
    End With
    End Function

  5. #5
    New Member
    Join Date
    Oct 2007
    Posts
    22

    Default

    To solve the listbox problem that I had also, where I wanted to add in only a selection of worksheets in the workbook, not the whole thing, I changed the following parts of the code:

    Sub Userform_Initialize()

    Dim oSheets As Variant

    oSheets = Array("Sheet2", "Sheet4")
    Worksheets(oSheets).Visible = False
    LBox1_Fill

    End Sub



    Private Sub CommandButton2_Click()

    Dim oSheet As Object

    For Each oSheet In Sheets
    oSheet.Visible = True
    Next
    Unload Me

    End Sub

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
  •  


DMCA.com