variables in footers

D

dangles

I use the following macro at work all the time. (I put BLAH in place
of real work data.)
Now other people want to use my macros. The right footer contains an X
which changes according to whatever document gets worked on. I just go
into the macro and put what I want in there before I run it. It's a
variable and could be anything (TOC-page, Appendix A-page, III-A-page,
etc.)

Who knows how to make a dialog box that will prompt the user for
whatever data in place of the X? Telling people how to go into VBA
(Alt F11) and find the X and change it isn't working out quite well.

Sub ClearAllHeadersFooters_ApplyBLAHBLAH_AllWorksheets_XDashPageNum()
'

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With
Dim s As Worksheet
Application.ScreenUpdating = False
For Each s In ActiveWorkbook.Worksheets
With s.PageSetup
.LeftHeader = "&""Arial Narrow,Italic""&10" & Chr(10) & "BLAH"
.CenterHeader = "BLAH"
.RightHeader = "&""Arial Narrow,Italic""&10BLAH" & Chr(10) &
"BLAH"
.LeftFooter = _
"&""Arial,Bold""&10BLAH" & Chr(10) &
"&""Arial,Regular""&10BLAH"
.CenterFooter = ""
.RightFooter = _
"&""Times New Roman,Regular""&11X-&P" & Chr(10) & "&""Arial
Narrow,Italic""&7BLAH"
End With
Next s
End Sub
 
J

JE McGimpsey

One way:

Const sRight As String = _
"&""Times New Roman,Regular""&11$$-&P" & _
vbCr & "&""Arial Narrow Italic""&7BLAH"
Dim vResponse As Variant
vResponse = Application.InputBox( _
Prompt:="Enter the footer text:", _
Title:="Footer stuff", _
Default:="TOC", _
Type:=2)
If vResponse = False Then Exit Sub 'user cancelled
'...
.RightFooter = Replace(sRight, "$$", CStr(vResponse))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top