Creating a watermark in Excel with VBA
First things first. I am a Visual Basic programmer and not a guru in Excel. This was learned the way most programmers are taught, the school of hard knocks and numerous errors. This may appear to be difficult and laborious but like most things learned, once you do it, it is easier later. Subjects like this are more difficult to explain than show. Unfortunately, you are not looking over my shoulder therefore you must read on.
The majority of this code was obtained by recording a macro while I went thru the application steps. This gave me a basis upon which to work.
This is an example of an output for the word “DRAFT”. The initial statement defines the font, text, and starting position on the page. It is just a piece of text at this point that is defined using WordArt. WordArt is part of the Microsoft Office package and is activated by selecting on the toolbar, ‘Insert’, ‘Picture’, ‘WordArt’.
ActiveSheet.Shapes.AddTextEffect(PresetTextEffect:=msoTextEffect1, _
Text:="DRAFT", FontName:="Arial Black", FontSize:=36, _
FontBold:=False, FontItalic:=False, Left:=10, Top:=10).Select
The most important item right now is defining the constant values so this code can be transported to other workbooks. For example if you pasted this code and changed the beginning of this statement from ‘ActiveSheet.’ To ‘Worksheets(“Sheet1”).’ Then you would get an error of ‘Variable not defined’ and the constants prefixed with ‘mso’ are highlighted. By the way, ‘mso’ stands for Microsoft Office. This is one of Microsoft’s not very well documented items. If you know the actual number value and what effect it has on the text then we could replace this constant with either a number or define this constant with a value. Here is what I found thru trial and error. The colors are part of the initial display effect. We will change them later with the second half of the code.
PresetTextEffect:=msoTextEffect1 ‘ Long integer
MsoTextEffect1 = 0
MsoTextEffect2 = 1
MsoTextEffect3 = 2
MsoTextEffect4 = 3
MsoTextEffect5 = 4
MsoTextEffect6 = 5
MsoTextEffect7 = 6
MsoTextEffect8 = 7
MsoTextEffect9 = 8
MsoTextEffect10 = 9
MsoTextEffect11 = 10
MsoTextEffect12 = 11
MsoTextEffect13 = 12
MsoTextEffect14 = 13
MsoTextEffect15 = 14
MsoTextEffect16 = 15
MsoTextEffect17 = 16
MsoTextEffect18 = 17
MsoTextEffect19 = 18
MsoTextEffect20 = 19
MsoTextEffect21 = 20
MsoTextEffect22 = 21
MsoTextEffect23 = 22
MsoTextEffect24 = 23
MsoTextEffect25 = 24
MsoTextEffect26 = 25
MsoTextEffect27 = 26
MsoTextEffect28 = 27
MsoTextEffect29 = 28
Upon further review of this piece of code, we see where the font is defined:
ActiveSheet.Shapes.AddTextEffect(PresetTextEffect:=msoTextEffect1, _
Text:="DRAFT", FontName:="Arial Black", FontSize:=36, _
FontBold:=False, FontItalic:=False, Left:=10, Top:=10).Select
The portion deals with the starting position on the sheet. The default value is in twips. (1.e. 1 inch = 1440 twips)
ActiveSheet.Shapes.AddTextEffect(PresetTextEffect:=msoTextEffect1, _
Text:="DRAFT", FontName:="Arial Black", FontSize:=36, _
FontBold:=False, FontItalic:=False,Left:=10, Top:=10).Select
Start 10 twips from the left side of the page and down 10. You can play with this at your leisure.
Now to the dressing up of the text. This portion of code is where the text is expanded, colored, and outlined.
Now, let’s get fancy using the above code. What did we change to get this type of display?
First, we decided on a style and then our starting position
PresetTextEffect:=2
Left:=50
Top:=150
Next, we decided on an outlined piece of text using the color blue. Of course, the size had to be increased to cover the data area.
And this is what we got. Our first watermark.
Now, let’s use the same layout but fill it in with light gray. Select Tools on the menu bar, Macros, record a new macro. Accept the default. We only want the code.
Right mouse click the curved word ‘DRAFT’ and select ‘Format WordArt…’ on the menu You should see this panel.
Change the fill color to a light gray by clicking on the down arrow.
Do not remove the checkmark from the semitransparent checkbox. If you do, the WordArt will overlay (hide) your data.
Move down to the color portion and click on the down arrow on the dropdown box labeled ‘Color’. This is the outline of the text. Select the same light gray.
Select OK button and this is what you get.
Let’s take a look at the macro code. Press Alt+F11 keys or go thru the previous steps to get to the macro display. Highlight your macro and select edit.
Here is the generated code.
Stop the macro recording at this time.
To make this code portable, make the following changes:
Are you starting to recognize some of the code. We have just learned that SchemeColor 22 is light gray and 12 is bright blue. Someone could go thru the color chart and document these values on a rainy day, if they wanted to. Everything else looks the same. Our new code, after formatting, looks like this:
Enter some data on the sheet and then go to the macro menu. Highlight your new macro and select Run. See below for an example of the output.
Here is what the finished product looks like:
As you can see, with Excel, even light colors can obscure the data. You will have better results using an outline and a line weight of 1#. See below.
Now it is time to go forth and experiment. Make your code as portable as possible so you do not have to reinvent the wheel each time. And above all, document everything in clear concise terms. Pretend you are a stranger reading your own code when you do this. You never know when you will get that phone call at two in the morning a year from now and you have to do some debugging or enhancements. Good documentation allows you to finish quicker, do turnover faster, and makes you appear to be the guru in the department.
|
Useful Links
|
|
