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
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