PDA

View Full Version : Assigning macro to print button code help



Usethaschwar
05-29-2012, 09:23 AM
I need help creating a code that will hide the contents in the cost ea, labor rate, and $ change impact columns when anybody goes to print out the form. I need this code to automatically blank these columns when printing so people don't need to manually do this. Thanks!

GTO
05-29-2012, 11:32 AM
Hi there,

In a junk copy of you workbook, try:
Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ary_lFontColorIndex_Old(1 To 18, 1 To 3) 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 18
For y = 1 To 3
ary_lFontColorIndex_Old(x, y) = Sheet2.Range("L31:N48").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("L31:N48").Font.ColorIndex = 2

'// 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 18
For y = 1 To 3
Sheet2.Range("L31:N48").Cells(x, y).Font.ColorIndex = ary_lFontColorIndex_Old(x, y)
Next
Next

ThisWorkbook.Saved = bolSaved
Application.EnableEvents = True
End If
End Sub

Hope that helps,

Mark

Usethaschwar
05-30-2012, 11:12 AM
Great! It worked perfectly! One last question: If I wanted to do this for 2 separate ranges would I simply copy the lines in the code where ranges are found and type in the other range? For example, I still need the range L31:N48, but now I also need range G31:H48 blanked out in the same way. Is there a simple way to enter this into the code? Perhaps you could repost your code with this change if possible. Thanks so much for all your help!

GTO
05-30-2012, 12:10 PM
If you step through the code you will see how it loops through the cells, picking up ea cell's .ColorIndex to store and later restore, after the actual printing is done. Try creating an additional array similar to:
For x = 1 To 18
For y = 1 To 3
ary_lFontColorIndex_Old(x, y) = Sheet2.Range("L31:N48").Cells(x, y).Font.ColorIndex
Next
Next
...the second dimension being 1 To 2, as we only have two columns from G to H.

Usethaschwar
05-30-2012, 12:14 PM
Hmmm that looks right, I've never done VBA programming before so I'm not quite sure how to correct enter that into the code. Do you mind showing me how I would enter this second array into the code?

Usethaschwar
05-30-2012, 01:07 PM
Nevermind, I was able to figure it out, guess my MATLAB class turned out useful afterall :)

Usethaschwar
06-25-2012, 05:39 AM
One last question about this code. When I go to print, the form prints directly to my default printer without the print screen showing up. Since many people will be using/printing this form from different computers, is there anyway to display this print screen before printing? Strangely enough, I am able to get to this print screen when I go to print preview first. Thanks for your help!

fredlo2010
06-25-2012, 06:29 AM
I am working on Excel 2010 and it shows the print dialog.

Usethaschwar
06-25-2012, 06:37 AM
Hmm, I'm using 2007. Is it possible to have a default printer saved to the file itself? For some reason the print button is basically acting as the quick print button. I'm not sure if this is the from the code or from something I saved to the file.