String array followed by a loop

[koolgoose]

Hi,

I have an excel worksheet which has 30-35 pages/page breaks. There are in all 4 sections spread across these pages. what i want to do here is insert a particular shape ("water_1") on only two of sections from the page section starts and ends. Below is the array code followed by loop which will be constant every time, but i am not able to run that on specific pages using array

Sub headers()


Dim pgname() As String


ReDim pgname(1 To 4) As String


pgname(1) = "Market Background"
pgname(2) = "Assets"
pgname(3) = "Glossary"
pgname(4) = "Let's Talk"


For i = 1 To 4


Call waterPosition


Next i










End Sub






Sub waterPosition()

Dim sh As Shape
Dim tsh As Shape
pgnum = 4

Set sh = ActiveSheet.Shapes("Water_1")
sh.Left = 20
sh.Top = (pgnum - 1) * 1300 + 600
sh.Rotation = -45
i = 2
pgnum = pgnum + 1

Do while pgnum<=10
Set tsh = sh.Duplicate
tsh.Name = "Water_" & i
tsh.Left = 20
tsh.Top = (pgnum - 1) * 1300 + 600
i = i + 1
pgnum = pgnum + 1

loop
End Sub

 

[sijpie]

hi koolgoose.

If you post code, put it between code tags (see example below in red) or use MrExcelHtml (which is what I used here)

I think this is what you want. Please read the comments as well

<font face=Calibri>Option Explicit<br><SPAN style="color:#007F00">' always have option exlicit as your first line in any module</SPAN><br><br><br><SPAN style="color:#00007F">Sub</SPAN> Headers()<br>    <SPAN style="color:#00007F">Dim</SPAN> pgName() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">ReDim</SPAN> pgName(1 <SPAN style="color:#00007F">To</SPAN> 4) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    pgName(1) = "Market Background"<br>    pgName(2) = "Assets"<br>    pgName(3) = "Glossary"<br>    pgName(4) = "Let's Talk"<br>    <br>    <br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 4<br>        waterPosition pgName(i)<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><SPAN style="color:#00007F">Sub</SPAN> waterPosition(sSheetName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)<br><SPAN style="color:#007F00">' Puts image Water_1 as watermark from page 5 to 10 _<br>  on the sheet passed as argument</SPAN><br>  <br>    <SPAN style="color:#00007F">Dim</SPAN> shSh <SPAN style="color:#00007F">As</SPAN> Shape<br>    <SPAN style="color:#00007F">Dim</SPAN> shT <SPAN style="color:#00007F">As</SPAN> Shape<br>    <SPAN style="color:#00007F">Dim</SPAN> iPgNum <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <br>    <SPAN style="color:#007F00">' speed up processing by not visually flipping to each sheet</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <br>    <SPAN style="color:#007F00">' activate sheet to get the watermark</SPAN><br>    Sheets(sSheetName).Select<br>    <SPAN style="color:#00007F">Set</SPAN> shSh = ActiveSheet.Shapes("Water_1")<br>    shSh.Left = 20<br>    shSh.Top = (pgnum - 1) * 1300 + 600<br>    shSh.Rotation = -45<br>    <br>    i = 2<br>    iPgNum = 5<br>    <SPAN style="color:#007F00">' place copies of the watermark every page (5-10)</SPAN><br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> iPgNum <= 10<br>        <SPAN style="color:#00007F">Set</SPAN> shT = shSh.Duplicate<br>        shT.Name = "Water_" & i<br>        shT.Left = 20<br>        shT.Top = (iPgNum - 1) * 1300 + 600<br>        i = i + 1<br>        iPgNum = iPgNum + 1<br>    <SPAN style="color:#00007F">Loop</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>