Application.SendKeys & Printing

[vba_dan]

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
ActiveSheet.Unprotect

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")
Sheets(sh).Select
label_printing
Case 2 'Print Wristbands Only
sh = ("Wristbands")
Sheets(sh).Select
wristband_printing
Case 3 'Print 1 Label, 1 Wristband
sh = ("Wristbands")
Sheets(sh).Select
label_printing
wristband_printing
End Select

End Sub
Sub label_printing()
Sheets("Labels").Select
Application.SendKeys "%FP%R+^{PGDN}{TAB 3}{UP 10}{DOWN
10}{UP}~%R+^{PGUP}~~", True
End Sub
Sub wristband_printing()
Sheets("Wristbands").Select
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!

Thanks
Dan

 

[Jan Karel Pieterse]

Hi Vba,

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

Try adding DoEvents immediately after the sendkeys command.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[vba_dan]

No joy I'm afraid - exact same results!

Hi Vba,


Try adding DoEvents immediately after the sendkeys command.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

.

 

[Jan Karel Pieterse]

Hi Vba,

No joy I'm afraid - exact same results!

In that case I'm out of idea's. SENDKEYS is impossible to troubleshoot when you
do not have the same setup unfortunately. Keep trying, maybe you've missed a
key or two in the problem one. Sometimes it helps to change the way you access
the property you need using the keyboard (use a different sequence I mean).

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com