Clear the Input Cell Style for Printing

I really like to highlight input cells by applying the Input Cell Style. I also like to try to place all of the input cells for a workbook on a single worksheet. That way, it is easy to update the input cells, and since they aren’t on any of the sheets I need to print, the input cell style formatting isn’t an issue. However, sometimes it may be necessary to place an input cell in a worksheet that will be printed. So, this post provides a macro that instantly removes the Input Cell Style formatting so you can print. A second macro restores the default cell style formatting.

Objectives

Before we jump right in to the technical stuff and the VBA code, let’s just be sure we are on the same page. Let’s say we have some input cells that store the company name and contact name, as shown below.

Input cells by Jeff Lenning

We want to identify the input cells to make it easy for a user to know which cells require input. So, we select the input cells and apply the Input Cell Style (found in the Cell Styles gallery on the Home tab). The result is shown below.

Input Style by Jeff Lenning

Now the input cells are easy to see. When we apply the Input Cell Style on worksheets that don’t need to be printed, life is good. But, when we apply the Input Cell Style on sheets that are designed to be printed, we may not want the formatting to appear on the printed page.

So, this post walks through one way to approach this issue, which is to run a macro prior to printing that clears the Input Cell Style formatting. Then, you would print the worksheet. Then, you would run another macro that restores the Input Cell Style formatting.

Sound good? Alright, let’s get to it.

Details

Since we have previously discussed recording macros, the personal macro workbook, and setting up a macro toolbox, rather than rehash all of those details again here, I’ll provide links to those posts below, and proceed directly to the VBA code. You can copy the code and paste it into your VB Editor as needed. Or, if you prefer, just download the sample Excel file below which has the VBA code.

In practice, after applying the Input Cell Style, the basic idea is that you would:

  1. Clear the Input Style Formats
  2. Print the Worksheet
  3. Restore the Input Style Formats

Let’s walk through these steps.

Clear the Input Style Formats

So, assuming that you have already applied the Input Cell Style to various cells throughout the workbook, you’ll want to run this macro.

Sub InputStyleClear()
  With ActiveWorkbook.Styles("Input")
   .Interior.Pattern = xlNone
   .Font.ColorIndex = xlAutomatic
   .Borders(xlLeft).LineStyle = xlNone
   .Borders(xlRight).LineStyle = xlNone
   .Borders(xlTop).LineStyle = xlNone
   .Borders(xlBottom).LineStyle = xlNone
  End With
End Sub

As you look through the code, you can see it applies various formats to the Input style, namely, it removes the fill, sets the font color to automatic, and removes the borders.

Note: There are a variety of ways to run macros, including setting up a QAT button, a worksheet button, the Macros dialog, or even the VB editor. For more information, check out the additional resources below.

After you run the macro, the Input Cell Style is still applied to the input cells, but, the formatting is updated so that it appears as any normal cell, that is, without the fill, borders, or font color.

Print the Worksheet

Now, you can print the worksheet as needed, and the input cells won’t display the typical blue borders, blue font, and cell fill.

Once you are done printing, you are ready to restore the formats.

Restore the Input Style Formats

To restore the default Input Style formats, you would run this macro.

Sub InputStyleRestore()
  With ActiveWorkbook.Styles("Input")
    .Interior.Color = 10079487
    .Font.Color = -9027777
    .Borders(xlLeft).Color = -8421505
    .Borders(xlRight).Color = -8421505
    .Borders(xlTop).Color = -8421505
    .Borders(xlBottom).Color = -8421505
  End With
End Sub

As you look through the code, you can see that it updates the Input Style format, and essentially restores the built-in formatting.

If you have any other Style-related tips, please share by posting a comment below…thanks!

Additional Resources

 

 

This article was written by Jeff Lenning

2 comments:

  1. Dave Marriott
    Reply

    You can achieve a similar result by using the black and white checkbox in page setup.

    1. Jeff Lenning Post author
      Reply

      Thanks Dave!

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.