variables in footers

[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

 

[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))