In my last blog about printing in Excel, I explained how printing in whole can be blocked. However, I took a big assumption that if a sheet contains partially classified data, the whole printing is blocked. In real life situation, this is hardly the case. Sheet may contain classified data in a few cells or in a range or scattered among cells but not completely.
How to achieve hiding only classified data from printing
Blocking printing event partially only for a classified is not as easy as it may sound (as I did for the whole workbook). One must take extra care with hiding a classified range. Printing is an event which can be raised in multiple ways.
- User presses ctrl + p key combination from keyboard.
- User selects File, then Print from Menu.
In either case, a print dialog is shown by the underline application. In our case also, print dialog plays a crucial role. Therefore, we should somehow be able to hide our contents before they are put in to PrintStream and once printing is done we should be able to get contents in to their original state i.e. unhide.
How to hide data in cells
Excel provides NumberFormat as a property to range object. When NumberFormat property is set “;;;” (3 colons), contents of a cells are hidden and when set to nothing (“”), they become visible again. Having said that, we should before actual printing, hide our contents and later unhide them. At this point, it may sound an easy walk but hold on for a moment. Excel printing delegate controls the printing dialog completely and it is only shown when printing event handler exits i.e. at the end and it is not at all shown when Cancel ref variable is to true.
We shall make use of Cancel variable to the most and set it true when event handler exits because we don’t want Excel to show its own printing dialog. However, we shall show our own printing dialog during print event interception as show below:
- this.Application.Dialogs[Excel.XlBuiltInDialog.xlDialogPrint].Show();
We shall call show method when contents are hidden as in the following code snippet:
-
-
-
-
- this.Application.Dialogs[Excel.XlBuiltInDialog.xlDialogPrint].Show();
-
-
Well, we missed an important point here. When print dialog is shown and user presses printing button; Excel generates once again Printing event. As a result, Printing dialog is shown two times. We should take care of it by disabling application events in Excel before our custom print dialog is shown and later on enable them as shown below:
- this.Application.EnableEvents = false;
-
- this.Application.EnableEvents = true;
During the span events are disabled, excel will not generate any internal events. However, you must set them to true at the end so that once intended operation is completed, excel should take care of internal events generated with user interaction.
In my example project, I assumed that classified data lies in range “A1:B2”. However, in real life, this data is nowhere hardcoded. There are multiple ways the owner of the document can store this information.
- Store the classified range in a sheet and hide and protect it with password.
- Unlock it and read during printing operation.
- Store data on server and communicate with server at run time.