George said:
Is it possible to place a watermark in Excel? I need "Draft" to show up in
the center of every sheet I print for a project. Know how to do it in Word
but can find a way in Excel.
The idea is to put a picture (possibly a text object, something like "Draft"
ou "Projet") as a "Background..." (from the "Insert" menu) in your Worksheet.
This can (with some adjustments) look OK. But, as it is stated in the Help
(see "Watermark"), it *won't* print. So here come the workarounds...
1 - the classic workaround is to copy the screen as a snapshot and then
print it out. But this needs some cleaning on the Mac side, so I made some
adjustments to a macro by Denny Campbell. It seems to give the expected
results:
----------------------------------
Sub PrintMyWatermark()
Dim MyPrintArea As Range
If ActiveWorkbook.Saved <> True Then
temp = MsgBox("Do you want to save the Workbook first?", _
vbOKCancel, "Save")
If temp = 1 Then ActiveWorkbook.Save
End If
On Error GoTo NoPrintArea
Set MyPrintArea = Range(ActiveSheet.PageSetup.PrintArea)
On Error Resume Next
MyPrintArea.CopyPicture
MyPrintArea.Clear
ActiveSheet.Paste Destination:=MyPrintArea
ActiveWindow.DisplayGridlines = False
ActiveWindow.SelectedSheets.PrintPreview ' or .Print Out
ActiveWorkbook.Close Saved = True
Exit Sub
NoPrintArea:
temp = MsgBox("No print area has been defined!", _
vbCritical, "Cancel Macro")
Exit Sub
End Sub
------------------------------------
The macro first offers to save the workbook in order to back its current
situation if wanted, as it then modifies the workbook and closes it to make
an end.
Note that this prints the worksheet as it is displayed, including the
background picture if there is, but it can't be considered as a genuine
watermark.
2 – another option is to create a template workbook in which one can keep
one sheet for a "Draft" watermark, another for "Project", "Confidential",
whatever… In the following example, I made a "Draft" template, saved on a
"Draft" sheet of a "MyWatermarks" workbook, but you can easily adjust it, I
guess.
The first step is the making of the sheet that will be used as a watermark.
I made up a quick macro to create it, but typing the text in cell A1 and
adjusting it would bring up the same result. Save the Workbook as
"MyWatermarks.xls"
------------------------------------------------------------
Sub CreateWatermark()
MyWatermark = "Draft"
' this can be modified as needed...
ThisWorkbook.Worksheets.Add
ActiveSheet.Name = MyWatermark
With ActiveSheet.Range("A1")
.Value = MyWatermark
.Orientation = 45
.Font.FontStyle = "Bold"
.Font.Size = 112
.Font.ColorIndex = 48
.Columns.AutoFit
.Rows.AutoFit
End With
End Sub
----------------------------------------------------------------
Now comes a second macro that should be added to the same macro module. This
one will add the watermark in the upper left corner of every page of any open
worksheet, make sure its transparency is set to 80% and then print it out.
----------------------------------------------------------------
Sub PrintWithDraftWatermark()
Application.ScreenUpdating = False
' copy the picture that will be used as a watermark
ThisWorkbook.Sheets("Draft").Range("A1").CopyPicture
MyShapes = ActiveSheet.Shapes.Count
' find out how many pagebreaks are to be dealt with
HorizBreak = ActiveSheet.HPageBreaks.Count
VerticBreaks = ActiveSheet.VPageBreaks.Count
' paste the picture on every page
On Error Resume Next
For i = 0 To HorizBreaks
MyRow = ActiveSheet.HPageBreaks(i).Location.Row
If MyRow = "" Then MyRow = 1
MyColumn = ""
For j = 0 To VerticBreaks
MyColumn = ActiveSheet.VPageBreaks(j).Location.Column
If MyColumn = "" Then MyColumn = 1
ColumnName = Chr(64 + MyColumn)
MyCell = ColumnName & MyRow
ActiveSheet.Paste Destination:=ActiveSheet.Range(MyCell)
' set transparency to 80%
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Fill.Transparency = 0.8
Next j
Next i
Application.ScreenUpdating = True
ActiveSheet.PrintPreview ' or .PrintOut '- for direct printing
Application.ScreenUpdating = False
' cleaning things up
For k = MyShapes + 1 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(k).Delete
Next k
Application.ScreenUpdating = True
End Sub
--------------------------------------------------------------------
When you want to print a document with the "Draft Watermark", you'll have to
run this macro. The "MyWatermarks" should obviously be open in the background
and you can launch it from the "Tools" > "Macro" (pick the "MyWatermarks.xls!
PrintWithDraftWatermark" line and click on "Run". If you do this on a
regular, you can attach the macro to a custom toolbar button or menu item.
This is a "Mac only" macro, as the Windows version can't handle the
"Transparency" property on pictures, as far as I could find out.
There is just one point I remain clueless: the last part of the macro is
supposed to "clean" the worksheet, taking the extra watermark pictures out.
For some mysterious reason, it only deletes the odd numbered pictures, the
even numbered ones remaining untouched. I can't figure out what's going on –
if anybody has a clue… Until then, you'll have to finish the cleansing by
hand!
If you're not familiar with the Visual Basic Editor, feel free to post back,
or have a look at JE McGimpsey's page:
http://www.mcgimpsey.com/excel/modules.html