chem101
01-27-2011, 01:08 PM
Hello Everyone,
I'm having a problem printing. A user enters data on wksNOS1. On another worksheet in this workbook are about 20 different print ranges that have been setup. This is wksTemp1. All the ranges are also named. The range that prints is determined by the info input by the user on wksNOS1. I also have a msgbox in the code asking the user if they would like to print a different form after the page they selected prints. This form, (there's only one form that can print), is on a third page in this same workbook called wksNOS2. The problem I'm having is when the user presses the button with the printing code. After the user presses the button, Excel shows the small window indicating the print job has been sent to the printer. Just as this window disappears (about 2 seconds) the screen goes blank, turns light blue in color, and stays that way until the msgbox appears. If the users chooses yes the screen stays blue, prints the add'l form then goes back to the worksheet. If the user chooses no then the blue screen changes back to the worksheet. My question is how do I eliminate the blue screen? Here's the code I have in place in a module:
Sub PrintShopForm()
If Range("IbS").Value = "Top" And Range("IpJ").Value = "Green3" Then
Application.ScreenUpdating = False
wksTemp1.Unprotect Password:="fire"
wksNOS1.Select
wksTemp1.Visible = True
Worksheets("Temp1").PageSetup.Orientation = xlLandscape
Worksheets("Temp1").PageSetup.PrintArea = "Area1"
Worksheets("Temp1").PrintOut _
Copies:=1, _
Preview:=False, _
ActivePrinter:="HP LaserJet P6000"
wksTemp1.Protect _
Password:="fire", _
UserInterfaceOnly:=True, _
AllowFormattingCells:=True
wksTemp1.Select
ActiveWindow.SelectedSheets.Visible = False
wksNOS1.Select
Application.ScreenUpdating = True
Range("A1").Select
Range("e8").Select
If MsgBox("Print QS Form?", vbYesNo) = vbYes Then
Application.ScreenUpdating = False
wksNOS2.Unprotect Password:="fire2"
wksNOS1.Select
wksNOS2.Visible = True
Worksheets("NOS2").PageSetup.Orientation = xlPortrait
Worksheets("NOS2").PageSetup.PrintArea = "QSForm"
Worksheets("Back Of WH Master").PrintOut _
Copies:=1, _
Preview:=False, _
ActivePrinter:="HP LaserJet P6000"
wksNOS2.Protect _
Password:="fire2", _
UserInterfaceOnly:=True, _
AllowFormattingCells:=True
wksNOS2.Select
ActiveWindow.SelectedSheets.Visible = False
wksNOS1.Select
Range("A1").Select
Range("e8").Select
Else
wksNOS1.Select
Range("A1").Select
Range("e8").Select
End If
End If
End Sub
Any help you can provide will be greatly appreciated!
Thank you!
I'm having a problem printing. A user enters data on wksNOS1. On another worksheet in this workbook are about 20 different print ranges that have been setup. This is wksTemp1. All the ranges are also named. The range that prints is determined by the info input by the user on wksNOS1. I also have a msgbox in the code asking the user if they would like to print a different form after the page they selected prints. This form, (there's only one form that can print), is on a third page in this same workbook called wksNOS2. The problem I'm having is when the user presses the button with the printing code. After the user presses the button, Excel shows the small window indicating the print job has been sent to the printer. Just as this window disappears (about 2 seconds) the screen goes blank, turns light blue in color, and stays that way until the msgbox appears. If the users chooses yes the screen stays blue, prints the add'l form then goes back to the worksheet. If the user chooses no then the blue screen changes back to the worksheet. My question is how do I eliminate the blue screen? Here's the code I have in place in a module:
Sub PrintShopForm()
If Range("IbS").Value = "Top" And Range("IpJ").Value = "Green3" Then
Application.ScreenUpdating = False
wksTemp1.Unprotect Password:="fire"
wksNOS1.Select
wksTemp1.Visible = True
Worksheets("Temp1").PageSetup.Orientation = xlLandscape
Worksheets("Temp1").PageSetup.PrintArea = "Area1"
Worksheets("Temp1").PrintOut _
Copies:=1, _
Preview:=False, _
ActivePrinter:="HP LaserJet P6000"
wksTemp1.Protect _
Password:="fire", _
UserInterfaceOnly:=True, _
AllowFormattingCells:=True
wksTemp1.Select
ActiveWindow.SelectedSheets.Visible = False
wksNOS1.Select
Application.ScreenUpdating = True
Range("A1").Select
Range("e8").Select
If MsgBox("Print QS Form?", vbYesNo) = vbYes Then
Application.ScreenUpdating = False
wksNOS2.Unprotect Password:="fire2"
wksNOS1.Select
wksNOS2.Visible = True
Worksheets("NOS2").PageSetup.Orientation = xlPortrait
Worksheets("NOS2").PageSetup.PrintArea = "QSForm"
Worksheets("Back Of WH Master").PrintOut _
Copies:=1, _
Preview:=False, _
ActivePrinter:="HP LaserJet P6000"
wksNOS2.Protect _
Password:="fire2", _
UserInterfaceOnly:=True, _
AllowFormattingCells:=True
wksNOS2.Select
ActiveWindow.SelectedSheets.Visible = False
wksNOS1.Select
Range("A1").Select
Range("e8").Select
Else
wksNOS1.Select
Range("A1").Select
Range("e8").Select
End If
End If
End Sub
Any help you can provide will be greatly appreciated!
Thank you!