Solved: ActiveSheet Printing

[sooty8]

Hi All

I have the following code to print some Prize Cards I have to print 6 Cards



Private Sub Add12_Click()
Sheets("Card").Select
Application.Dialogs(xlDialogPrint).Show
End Sub

It actually prints what is selected by Cmb10 in Code Below, because it goes to the Sheet ("Card") I lose the ActiveSheet is there any way it can be made to go back to the ActiveSheet after printing Card number 1 without closing the UserForm2 and then selecting Card number2 from the ActiveSheet.


Private Sub Cmb10_Click()
Dim startrownum As Integer
startrownum = 8
UserForm2.Tb52.Value = ActiveSheet.Range("A" & Trim(Str(Cmb10.ListIndex + startrownum)))
UserForm2.Tb53.Value = ActiveSheet.Range("B" & Trim(Str(Cmb10.ListIndex + startrownum)))
UserForm2.Tb54.Value = ActiveSheet.Range("C" & Trim(Str(Cmb10.ListIndex + startrownum)))
UserForm2.Tb55.Value = ActiveSheet.Range("D" & Trim(Str(Cmb10.ListIndex + startrownum)))
UserForm2.Tb56.Value = ActiveSheet.Range("I" & Trim(Str(Cmb10.ListIndex + startrownum)))
UserForm2.Tb57.Value = ActiveSheet.Range("J" & Trim(Str(Cmb10.ListIndex + startrownum)))

End Sub

Any help much appreciated

Regards

Sooty 8

[mdmackillop]

Can you post a sanitised version of your workbook?

[sooty8]

Hi & Good Morning MD

Have attached a sanitised version as requested took a bit of time cutting and pasting etc to get rid of all the other stuff within the workbook - however this seems to work OK -- hope this explains my problem.

Regards

Sooty8

[Simon Lloyd]

Try this for the Print button click:

Private Sub Add12_Click()
Dim WkSht As String
WkSht = ActiveSheet.Name
Sheets("Card").Select
Application.Dialogs(xlDialogPrint).Show
Sheets(WkSht).Activate
End Sub

[Simon Lloyd]

Although this is much smarter!

Private Sub Add12_Click()
Dim IB As Long
IB = Application.InputBox("Enter number of copies to print", "Print Quantity", , , , , , 1)
Sheets("Card").PrintOut copies:=IB
End Sub

[sooty8]

Hi Simon

Thanks for the help both work a treat -- if anybody has the same problem in the future -- try both these examples and make your choice -- for me both worked perfectly.

Many - Many Thanks

Regards

Sooty8