vba macro to insert wordart "watermark"

[ajf3]

Hi all,

What I want to do is find a macro that will prompt for a word and insert a word art representation of that word as a "watermark" - centered on the current page (as in printed page # x that contains the cell with focus).

I've done a little reasearch on this and have found some vba scripts which do it for predefined #'s of pages in a worksheet - ie, hardcode it to do all 14 pages when you know there are 14 horizontal pages, etc.

Is there a way to simply do it to the printed page which will contain the cell currently in focus?

Thanks!

[ajf3]

Any help?

Thx!

[Damon Ostrander]

Hi Ajf3,

I think the reason why no responses is because, at least through Excel 2000, there is no "watermark" capability. Are you referring to a new feature in Excel XP? Or are you perhaps referring to a feature that is sort of like a watermark, such as a semi-transparent graphic that resides on top of the worksheet?

There is a feature in Excel 2000 to have a background graphic, but this is not a watermark (i.e., it is displayed with full contrast).

If you are referring to any of these, they can be placed on the worksheets using a macro.

[ajf3]

Hi,

Thanks - you're right - I didn't word that correctly. I'm wondering if there's a macro out there that would :

1. Prompt me for a word
2. Insert said word as a wordart pseduo-watermark centered on the _printed_ page that will contain the cell currently in focus.

I have seen macros out there that approach this somewhat, but none deal with only the current page - you have to hard configure them to go 6 pages over horizontally based upon the pixel size of each page, or 10 pages vertically, etc...

Thanks - and sorry for the poor wording.

[Jack in the UK]

reading this righjt remember watermarks do not print if you wanted then to that is....

[Damon Ostrander]

Hi again ajf3,

Give this code a try. I think it does what you want:


Sub AddWatermark()

' Inserts a wordart "watermark" onto the current sheet with its
' upper left corner positioned on the upper left corner of the
' selected cell.

Dim StrIn As String
StrIn = InputBox("Enter watermark text")
If StrIn = "" Then Exit Sub

With ActiveSheet.Shapes.AddTextEffect(msoTextEffect9, StrIn, _
"Arial Black", 36#, msoFalse, msoFalse, _
10, 10)
.ScaleWidth 2, msoFalse, msoScaleFromTopLeft
.ScaleHeight 2, msoFalse, msoScaleFromBottomRight
.Fill.Visible = msoTrue
.Fill.Solid
.Fill.ForeColor.SchemeColor = 26
.Fill.Transparency = 0.5
.Shadow.Transparency = 0.5
.Line.Visible = msoFalse
'position at cell corner
.Top = Selection.Top
.Left = Selection.Left
End With

End Sub

[bam12]

Try this site out it may help you with what you are asking about

http://office.microsoft.com/en-us/as...138021033.aspx

[psycho802]

Originally Posted by Damon Ostrander

Hi again ajf3,

Give this code a try. I think it does what you want:


Sub AddWatermark()

' Inserts a wordart "watermark" onto the current sheet with its
' upper left corner positioned on the upper left corner of the
' selected cell.

Dim StrIn As String
StrIn = InputBox("Enter watermark text")
If StrIn = "" Then Exit Sub

With ActiveSheet.Shapes.AddTextEffect(msoTextEffect9, StrIn, _
"Arial Black", 36#, msoFalse, msoFalse, _
10, 10)
.ScaleWidth 2, msoFalse, msoScaleFromTopLeft
.ScaleHeight 2, msoFalse, msoScaleFromBottomRight
.Fill.Visible = msoTrue
.Fill.Solid
.Fill.ForeColor.SchemeColor = 26
.Fill.Transparency = 0.5
.Shadow.Transparency = 0.5
.Line.Visible = msoFalse
'position at cell corner
.Top = Selection.Top
.Left = Selection.Left
End With

End Sub

hi!

ab0ut y0ur c0de, h0w can y0u d0 it in excel 2007?

thanks =)

[Damon Ostrander]

It took me several years to get around to it, but I finally checked the code in Excel 2007, and it worked fine for me.

Damon

[slirfeldt]

Hello!

Thanks! It worked perfectly!
I can manually delete the watermark, but want to automate that also
So I need another macro that removes the watermark, but I fail to figure out how to.

Anyone?
Regards