I have a vba code for a form needing to print out only some specific information. This code is a "before print" code and will change the cell content's font to the background color so the information will not appear. Everything seems to work perfectly except, for some reason, the print screen does not appear when I click print and the form prints directly to my default printer. Since many people will be using/printing this form from different computers, I need this print screen to appear so they may choose which printer to print to. Strangely enough, I am able to get to this print screen when I go to print preview first, but not when I select print directly. I have copied my current code below for editing. Since I was able to find this code online, and I am very bad at vba programming, I was hoping someone could make edits in the code for me. Thanks!
Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ary_lFontColorIndex_Old(1 To 18, 1 To 2) As Long
Dim x As Long
Dim y As Long
Dim bolSaved As Boolean
If ActiveSheet.CodeName = "Sheet2" Then
'// Retain old cell colors one-at-a-time, in case any odd ball variations. This //
'// would be slow if the range was very big... //
bolSaved = ThisWorkbook.Saved
For x = 1 To 17
For y = 1 To 1
ary_lFontColorIndex_Old(x, y) = Sheet2.Range("O32:O48").Cells(x, y).Font.ColorIndex
Next
Next
'// When referring to the sheet by its CodeName, there are no quote marks. //
'// Just start out with the object's name, like Sheet2, ThisWorkbook, UserForm1.//
Sheet2.Range("O32:O48").Font.ColorIndex = 2
For x = 1 To 17
For y = 1 To 1
ary_lFontColorIndex_Old(x, y) = Sheet2.Range("P32:P48").Cells(x, y).Font.ColorIndex
Next
Next
Sheet2.Range("P32:P48").Font.ColorIndex = 15
For x = 1 To 17
For y = 1 To 1
ary_lFontColorIndex_Old(x, y) = Sheet2.Range("S32:S48").Cells(x, y).Font.ColorIndex
Next
Next
Sheet2.Range("S32:S48").Font.ColorIndex = 2
For x = 1 To 17
For y = 1 To 1
ary_lFontColorIndex_Old(x, y) = Sheet2.Range("T32:T48").Cells(x, y).Font.ColorIndex
Next
Next
Sheet2.Range("T32:T48").Font.ColorIndex = 15
For x = 1 To 18
For y = 1 To 1
ary_lFontColorIndex_Old(x, y) = Sheet2.Range("U32:U49").Cells(x, y).Font.ColorIndex
Next
Next
Sheet2.Range("U32:U49").Font.ColorIndex = 15
'// Cancel the print and kill events so that we don't recurse. //
Cancel = True
Application.EnableEvents = False
Sheet2.PrintOut
'// Reset the font color for each cell, and flag .Saved to what is was before //
'// we changed the colors. //
For x = 1 To 17
For y = 1 To 1
Sheet2.Range("O32:O48").Cells(x, y).Font.ColorIndex = ary_lFontColorIndex_Old(x, y)
Next
Next
For x = 1 To 17
For y = 1 To 1
Sheet2.Range("P32:P48").Cells(x, y).Font.ColorIndex = ary_lFontColorIndex_Old(x, y)
Next
Next
For x = 1 To 17
For y = 1 To 1
Sheet2.Range("S32:S48").Cells(x, y).Font.ColorIndex = ary_lFontColorIndex_Old(x, y)
Next
Next
For x = 1 To 17
For y = 1 To 1
Sheet2.Range("T32:T48").Cells(x, y).Font.ColorIndex = ary_lFontColorIndex_Old(x, y)
Next
Next
For x = 1 To 18
For y = 1 To 1
Sheet2.Range("U32:U49").Cells(x, y).Font.ColorIndex = ary_lFontColorIndex_Old(x, y)
Next
Next
ThisWorkbook.Saved = bolSaved
Application.EnableEvents = True
End If
End Sub
Bookmarks