variables in footers; how to prompt user for input within macro
 

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

variables in footers; how to prompt user for input within macro
 

Sub ClearAllHeadersFooters_ApplyBLAHBLAH_AllWorksheets _XDashPageNum()
Dim BLAH as String
BLAH = InputBox("Enter value for footer")
if BLAH = "" then
msgbox "You entered nothing so formatting" &
vbnewline & "will not be performed"
Exit Sub
End if



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""&10" _
& BLAH & Chr(10) & BLAH
.LeftFooter = _
"&""Arial,Bold""&10" & BLAH & Chr(10) & _
"&""Arial,Regular""&10" & BLAH
.CenterFooter = ""
.RightFooter = _
"&""Times New Roman,Regular""&11X-&P" & _
Chr(10) & "&""ArialNarrow,Italic""&7" & BLAH
End With
Next s
End Sub

--
Regards,
Tom Ogilvy


"dangles" wrote in message
oups.com...
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