Often, I have a few data entry cells or blocks on multiple worksheets. While this follows good spreadsheet design principles, it does have a bit of a side effect: When you print the worksheets, the green backgrounds print as well. While they're great for telling the user where to input data manually, it's distracting when you are looking at a printed (presentation version) of your data or report. So the question you may be asking is what can we do about it?
We could create a full new report that essentially duplicates what we've got, but then it's both a maintenance headache and performance hit. Instead, we can use a conditional format to hide the colour on the cells when we go to print the sheet.
This is actually really easy, if it weren't for the fact that you probably want to do it automatically. That involves dipping into some VBA. If you're not comfortable working with VBA though, read on anyway. You can still do the setup for this and control it manually.
This article illustrates three things:
•If you don't know VBA, you should learn it
•There should be a Workbook_AfterPrint event in VBA
The first thing to do is create a cell to control what mode the workbook is in. i.e. Printing Mode or Working mode. There's lots of ways to do this. In our files we always set up a worksheet dedicated to being a "Control Panel", and it's there that I would set a cell on that worksheet to hold a "Yes" or "No" value:
As you can see, this approach can be used to drive a few other things as well, such as dates for column headers, the type of data pulled from a PivotTable and more.)
If you're wondering why not True/False values, we used Yes/No as it's more readable to others if they have to work with the spreadsheet.
Step 2: Name the Range
The next thing to do is assign a name to the PrintMode value. In this case, We used the name rngPrintMode to the cell B11 on the control panel worksheet. While this isn't truly required to make this work, it is much easier to work with the named range later, rather than try to remember the cell reference. It also becomes key when you move to the VBA side, as VBA doesn't update its reference to the cell when a line is inserted above. Named ranges, on the other hand, do update automatically.. For this reason alone, it is highly recommended that you use named ranges when referring to worksheet ranges in VBA.
Step 3: Set up the Conditional Format
The steps shown below are for Excel 2007, but with a little ingenuity, you can make this work in 2003. We're assuming that you already have a green (or other) background on your data entry cell at this point.
- Select the data entry cell(s) that you want to hide the colour on
- On the Home Tab--> Conditional Formatting--> New Rule then create a formula as shown below:
- Click Format
- From the "Fill" tab, click "No Color"
- Click OK twice
As far as the setting up the conditional formatting, you're actually done. Change the value on the rngPrintMode cell to "Yes", and your conditional formats should kick in, removing any applied background colours. Now you can print without the data entry cells conspicuously marked! Change it back to "No", and the colours re-appear.
Just a hint here: if you want to leave this as a manual control, set up a data validation list to control the field. Debra Dalgleish has a great article on doing that which you can find right here.
Step 4: Adding Automation
To make this automatically kick in when you hit print, and set itself back once it's done, you'll neede to add a couple of VBA procedures to your project.
In a STANDARD MODULE, add the following:
Public Sub PrintingActive(Optional bStatus = False) 'Author : Ken Puls (www.excelguru.ca) 'Macro Purpose: Toggle the printing mode variable to control ' conditional format set Dim rngInfo As Range '!!! Set Your Worksheet Here !!! Set rngInfo = Worksheets("Control Panel").Range("rngPrintMode") 'Check the status and set the cell accordingly If bStatus = True Then rngInfo.Value = "Yes" Else rngInfo.Value = "No" End If End Sub
Next, go to the THISWORKBOOK module, and put the following in it:
Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Author : Ken Puls (www.excelguru.ca) 'Macro Purpose: Toggle the conditional formats for printing Call PrintingActive(True) Application.OnTime Now + TimeValue("00:00:01"), "PrintingActive" End Sub
The unfortunate part about this approach is that we have to rely on using OnTime, which kicks off a routine based on time, not necessarily when the existing routine completes. Theoretically, if you had a really huge print job, the call to PrintingActive could be triggered before the job is complete. While unlikely, you can always just add a few seconds to the TimeValue to work around this issue if you experience it. (I.e. "00:00:15" will turn it into a 15 second delay.)
The ideal solution would have been to leverage a Workbook_AfterPrint event, but sadly one does not exist.
That's it! Just click the print button and the green cells change their backgrounds to white (transparent), the file will print, and then the colour(s) are reintstated. And it all happens without even having the change the cell value manually.
This article was orignally published on my blog on May 11th, 2009. The original can be found here: http://www.excelguru.ca/blog/2009/05...fore-printing/