Printing Worksheets from Userforms

Merryn

New Member
Joined
Oct 17, 2007
Messages
22
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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:
<table width="100%" border="1" bgcolor="White" style="filter:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>

  <font color="#0000A0">Sub</font> Userform_Initialize()
       LBox1_Fill
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> CommandButton1_Click()
       Print_Job
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> CommandButton2_Click()
       Unload Me
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> CommandButton3_Click()
       Application.Dialogs(xlDialogPrinterSetup).Show
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Function</font> LBox1_Fill()
       <font color="#0000A0">Dim</font> sht <font color="#0000A0">As</font> Worksheet, Wb <font color="#0000A0">As</font> Workbook

       <font color="#0000A0">Set</font> Wb = Workbooks("Insurance Questionnaire Answers.xls")
       Wb.Activate

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

  <font color="#0000A0">Function</font> Print_Job()
       <font color="#0000A0">Dim</font> i <font color="#0000A0">As</font> <font color="#0000A0">Integer</font>

       Application.DisplayAlerts = <font color="#0000A0">False</font>
       <font color="#0000A0">With</font> UserForm1
           .Hide
           Application.Visible = 1
           <font color="#0000A0">For</font> i = 0 <font color="#0000A0">To</font> .ListBox1.ListCount - 1
               <font color="#0000A0">If</font> .ListBox1.Selected(i) <font color="#0000A0">Then</font>
                   Application.ScreenUpdating = 0
                   Sheets(.ListBox1.List(i)).PrintOut
               <font color="#0000A0">End</font> <font color="#0000A0">If</font>
           <font color="#0000A0">Next</font> i
       <font color="#0000A0">End</font> <font color="#0000A0">With</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Function</font>
</FONT></td></tr></table><button onclick='document.all("1114200781752344").value=document.all("1114200781752344").value.replace(/<br \/>\s\s/g,"");document.all("1114200781752344").value=document.all("1114200781752344").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("1114200781752344").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="1114200781752344" wrap="virtual">
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</textarea>
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top