Application.SendKeys & Printing



I am having an issue using SendKeys! (I know, I know they are rubbish)
I have a requirement to print to specific trays for a specific worksheet and
I therefore have set up 3 buttons on a sheets:

Labels Only
Band Only
Labels & Band

As i'm sure you all aware Excels abailty to send to a particular tray is
somewhat lacking leaving me two options:

1. Use sendkeys
2. Set up the printer on the network for each tray.

Unfortunately for reasons I shall not go into option 2 is not available so I
am left with the first option.

Here is the code I have (which incidently works very well)

Sub Print_Labels()
'Application.ScreenUpdating = False
Dim laba As Variant
laba = 1
Printing (laba)
End Sub
Sub Print_Wristbands()
'Application.ScreenUpdating = False
Dim laba As Variant
laba = 2
Printing (laba)
End Sub
Sub Print_1Each()
'Application.ScreenUpdating = False
Dim laba As Variant
laba = 3
Printing (laba)
End Sub

Sub Printing(laba As Variant)

'Application.ScreenUpdating = False

Dim y, x, w, v, p, o, sh As Variant

Sheets("Patient Wristbands").Select
Application.ScreenUpdating = False

Sheets("Patients").Visible = False
Sheets("Wristbands").Visible = True
Sheets("Labels").Visible = True

Sheets("Patient Wristbands").Range("F1:M500").ClearContents
Sheets("Patient Wristbands").Range("G2").Value = "The following
LaserBand wristbands have been successfully printed."
Sheets("Patient Wristbands").Range("G15").Value = "If there is a problem
with this report please contact (e-mail address removed)"

Sheets("Patient Wristbands").Columns("E:R").EntireColumn.Hidden = False

Select Case laba
Case 1 'Print Labels Only
sh = ("Labels")
Case 2 'Print Wristbands Only
sh = ("Wristbands")
Case 3 'Print 1 Label, 1 Wristband
sh = ("Wristbands")
End Select

End Sub
Sub label_printing()
Application.SendKeys "%FP%R+^{PGDN}{TAB 3}{UP 10}{DOWN
10}{UP}~%R+^{PGUP}~~", True
End Sub
Sub wristband_printing()
Application.SendKeys "%FP%R+^{PGDN}{TAB 3}{UP 10}{DOWN 10}~%R+^{PGUP}~~", True
End Sub

Now Case 1 & 2 are great and run off as expected however Case 3 will only
print the label and halt (without error) after completing that!

Also if I add any code after the Case Select then the pages do not print
either (again without error!)

I do not understand why this would happen as just running the send keys
works fine, and running just the send keys within the Case is fine also.

I need the code to run within a do until as I would like it to run multiple
prints at once!

Any help would be gratefully received!



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question