codetoad.com
  ASP Shopping CartForum & BBS
  - all for $20 from CodeToad Plus!
  
  Home || ASP | ASP.Net | C++/C# | DHTML | HTML | Java | Javascript | Perl | VB | XML || CodeToad Plus! || Forums || RAM 
Search Site:



Home » Visual Basic » Article

Creating a watermark in Excel with VBA

Article by: Kenneth Ives (3/30/2003)
Bookmark us now! Add to Favourites
Email a friend!Tell a friend
Summary: Adding a watermark to an Excel spreadsheet using VBA.
Viewed: 105783 times Rating (36 votes): 
 4.7 out of 5
 Rate this Article  Read Comments  Post Comments

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.

Select All Code


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.

Select All Code


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.

Select All Code


To make this code portable, make the following changes:

Select All Code


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:

Select All Code


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.




CodeToad Experts

Can't find the answer?
Our Site experts are answering questions for free in the CodeToad forums
Rate this article:     Poor Excellent
View highlighted Comments
User Comments on 'Creating a watermark in Excel with VBA'
RELATED ARTICLES
Generate License Keys (such as CD keys on Microsoft software)
by Brian Gillham
This class allows you to generate license keys, such as the CD keys on the case of most Microsoft software
Communicating with the Database (Using ADO)
by Chaudhary Pradeep K. Roy
Using ADO to get connected with the database.
Visual Basic Read and Modify the Registry
by Kenneth Ives
Perform the four basic functions to the Windows registry. Add, change, delete, and query. Allows you to to read registry values, and modify both keys and values.
Generate an Array of Unique Random Numbers
by Brian Gillham
This function will generate an ARRAY of TRULY random numbers.
Beginning Resource Files in Visual Basic
by Kenneth Ives
Have you ever wanted to use graphics, such as icons, bitmaps, cursors, and AVI files? How about sound or even message box text? This can be an enormous amount of overhead. These are all examples of more files to keep track of when you distribute an application, DLL, or OCX. Lets find out how resource files can help....
Visual Basic Compact/Repair Access Database Utility
by Kenneth Ives
This utility will compact and repair the access database you select. It is actually more convenient to use this utility than do the same task with Access, because you don't have to save the database with a different name and then delete the old database when you are done: here, a temporary copy of the database is automatically created and deleted.
Change Cursor to Hour Glass
by Brian Gillham
A simple script to change the cursor to an hour glass.
Creating a watermark in Excel with VBA
by Kenneth Ives
Adding a watermark to an Excel spreadsheet using VBA.
Generate your own Random Numbers
by Thaha Hussain
This simple program generates random numbers using the basic mathematical methods.
Analog Clock
by Thaha Hussain
This program demonstrates Thaha Hussain's Clock Work Formula to paint an Analogue Clock.








Recent Forum Threads
• Run a program both on windows and linux
• VERO.SurfCAM.v2014
• Schlumberger.Petrel.V2013.2
• Petrel.V2013.2
• Altair.HyperWorks.v12
• VoluMill.v6.1
• VoluMill.NEXION.6
• VERO.SurfCAM.v2014
• Schlumberger.Petrel.V2013.2


Recent Articles
ASP GetTempName
Decode and Encode UTF-8
ASP GetFile
ASP FolderExists
ASP FileExists
ASP OpenTextFile
ASP FilesystemObject
ASP CreateFolder
ASP CreateTextFile
Javascript Get Selected Text


© Copyright codetoad.com 2001-2014