I recently had the opportunity to work on a great Excel project, accomplishing several cool tasks to bring together a cohesive application. Not too long ago, I would have told you that Excel isn’t the place to try to pull together a real application. It’s too loosy-goosey, I would have said, without enough control to establish a robust, reliable application. After this project, I’ve changed my mind.
Some of the things that were unique to this particular project:
A control panel that is always on top of all sheets
Selectively applying page numbers across multiple worksheets
Drag/Drop on a User Form
Dynamic Drop-down lists
And so much more
I made a list of 28 topics that I’d like to blog about on this one project alone. 28!
In today’s post, I’d like to talk about just one of those topics: a watermark that appears on the printed copy of the document, but is not visible when viewing the document in Excel.
In this project, the customer prints a packet of worksheets – anywhere from 5 to 175 worksheets at a time. They print a preliminary version of this packet, for internal use only, and then a final version, which is sent to the customer. On the preliminary version, they wanted a watermark in the header, like so:
The watermark was only to appear on the Preliminary report, not on the Final Report. They also didn’t want the watermark to show in the Excel window – only on the report.
I’m sure there are lots of ways to accomplish this task. Before I talk about the process I used, let me fill you in on a couple of relevant details:
Since the client wants to print multiple worksheets in a single document, I created a process that allows them to choose which sheets to print (via a UserForm), and then include all of those in the same report. So, rather than using the built-in “Print” functionality, they use a custom macro that I built for them.
I use the term “print” in the modern sense – rather than sending the document to a physical printer, I create a PDF, which the user can then choose to print, email, or save to disk.
So, given those caveats, how did I create the now-you-see-it, now-you-don’t watermark?
When the user runs the “Print Preliminary” macro, here’s what I do:
Prompt the user to identify the worksheets they want to print, using a UserForm.
Loop through all of the selected worksheets, and run a simple bit of code (shown below) that adds a Text Effect to the document, in the header section.
Generate the PDF file
Loop through all of the selected worksheets, running a second simple bit of code that removes the Text Effect.
Getting into the details, the UserForm looks like this:
The logic in for the “Create Report” button does three things:
Loop through the check boxes, and for each one that is checked off, add the watermark to the corresponding worksheet.
Generate the PDF, including all selected sheets.
Loop through the check boxes once again, and for each one that is checked off, remove the watermark.
I’ll spare you the entire code, and focus just on the lines that are related to the watermark. To add the watermarks, my code looks like this:
For Each chk In Me.Controls
If Left(chk.Name, 3) = "chk" Then ' Only process the checkboxes.
If chk.Value <> 0 Then ' Only process those that are checked off.
AddWatermark chk.Caption
End If
End If
NextTo summarize:
The “for each” statement loops through all controls on the UserForm, including the checkboxes, labels, and command buttons.
The first “if” statement checks to see if the control’s name starts with “chk”. If it does, I know it’s a checkbox. I ignore everything else, which would be the labels and command buttons.
The second “if” statement checks to see if the check box is checked. A checkbox has a value of 0 when it’s not checked, and some other value when it’s checked. Sometimes it uses 1, sometimes -1, sometimes another number. If it’s anything other than 0, I know it’s checked off.
Finally, I call the “AddWatermark” procedure (shown below), passing it the checkbox’s caption, which corresponds to the name of a worksheet.
With me so far? OK, then, let’s move on to the “AddWatermark procedure. Here’s the entire procedure, including error handling and object clean-up:
Public Sub AddWatermark(wsName as string)
On Error GoTo ErrHandler
Dim ws as Worksheet
Set ws = Worksheets(wsName)
With ws.Shapes.AddTextEffect(msoTextEffect3, "Preliminary", _
"Arial Black", 28#, msoFalse, msoFalse, 270, 5)
.Name = "shpWatermark"
End With
Set ws = nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, vbCritical + vbOKOnly, "ERROR: AddWatermark"
End SubHere’s the synopsis of that one:
The “On Error” statement implements error handling. In a customer-facing VBA application, always include effective error handling.
The “set ws…” statement gets a reference to the worksheet in question.
The “With” statement adds a Text Effect. This is probably something you’ve seen in either Word or Excel, often referred to as Word Art. In a nutshell, Word Art adds text with fancy formatting. In this case, I used effect #3.
Next, I give the shape a name. I’ll need this so that I can remove it later.
Finally, I clean up after myself (“set ws = nothing”) and exit the sub procedure before my error handling code.
After adding the watermarks, I’ve got code to generate the PDF file. I won’t include that code here, since I’m focusing on the watermark. If you’re interested in that one, it’s not hard to find examples online, or drop me a line if you’d like to see my solutions.
Now, the only thing left to do is to remove the watermarks, so that the user won’t see them in the Excel doc. The code here is almost exactly the same as the code from above, except that it calls “RemoveWatermark” instead of “AddWatermark”:
For Each chk In Me.Controls
If Left(chk.Name, 3) = "chk" Then ' Only process the checkboxes.
If chk.Value <> 0 Then ' Only process those that are checked off.
RemoveWatermark chk.Caption
End If
End If
Next
And, to close the loop, the RemoveWatermark procedure looks like this:
Public Sub RemoveWatermark(wsName As String)
On Error Resume Next
Dim ws As Worksheet
Set ws = Worksheets(wsName)
ws.Select
ws.Shapes("shpWatermark").Delete Set ws = Nothing
End SubAs you can see, its very similar to the AddWatermark, but even simpler.
The "On Error Resume Next" statement instructs the program to ignore errors. If I'm not able to remove the watermark, it's not a big deal. So rather than throwing an error, I ignore it.
Once again, the “set ws…” statement gets a reference to the worksheet in question.
“ws.select” selects that worksheet. This is usually something I avoid in VBA code, but in this case, the delete statement, which comes next, doesn’t work without it.
Finally, I delete the shape that’s called “shpWatermark”.
So, there you have it. As I stated earlier, I’m sure there are other ways to accomplish this task. There’s always more than one way to do everything in VBA. For this application, the method I described above is working reliably and efficiently, so win-win!
I hope you found this useful. If you have questions or comments on this code, I’d love to hear from you. Don’t hesitate to drop me a line!
Until next time, happy coding.
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of business experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© 2021 North Port Solutions, LLC. All right reserved.
Opmerkingen