I don’t thing you can use an Inputbox because you want multiple lines for your headers/footers. However, you could use a Form e.g. (exhibit limited to the header and left footer):
Headers and Footers - using a Form.xls |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J |
---|
1 | | | | | | | | | | |
---|
2 | | | | | | | | | | |
---|
3 | | Header | |
---|
4 | | | Line1 | | | | | | | |
---|
5 | | | | | |
---|
6 | | | | | | | | | | |
---|
7 | | | Line2 | | | | | | | |
---|
8 | | | | | |
---|
9 | | | | | | | | | | |
---|
10 | | LeftFooter | |
---|
11 | | | Line1 | | | | | | | |
---|
12 | | | | | |
---|
13 | | | | | | | | | | |
---|
14 | | | Line2 | | | | | | | |
---|
15 | | | | | |
---|
16 | | | | | | | | | | |
---|
17 | | | Line3 | | | | | | | |
---|
18 | | | | | |
---|
19 | | | | | | | | | | |
---|
20 | | | | | | | | | | |
---|
21 | | | | Go | | | Close | | | |
---|
22 | | | | | | | | |
---|
23 | | | | | | | | | | |
---|
24 | | | | | | | | | | |
---|
25 | | | | | | | | | | |
---|
|
---|
So, prepare the Form with Textboxes and labels, and 2 buttons.
Post back if you need help with doing a Form.
Form module:
Code:
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim msg1 As String
Dim msg2 As String
Dim msg3 As String
Dim msg4 As String
Dim msg5 As String
Dim msg6 As String
Dim msg7 As String
Application.ScreenUpdating = False
msg1 = TextBox1.Value 'Center header - line 1
msg2 = TextBox2.Value 'Center header - line 2
msg3 = TextBox3.Value 'Left footer - line 1
msg4 = TextBox4.Value 'Left footer - line 2
msg5 = TextBox5.Value 'Left footer - line 3
msg6 = TextBox6.Value 'Center footer
msg7 = TextBox7.Value 'Right footer
For Each ws In Worksheets
With ws.PageSetup
.CenterHeader = msg1 & vbLf & msg2
.LeftFooter = msg3 & vbLf & msg4 & vbLf & msg5
.CenterFooter = msg6
.RightFooter = msg7
End With
Next
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton2_Click()
End
End Sub
Standard module:
Code:
Sub myForm()
UserForm1.Show
End Sub
Notes
1. Formatting:
If you want the Center Footer to be say Arial font, bold, 10 pt and uppercase, then replace this line:
.CenterFooter = msg6
with:
.CenterFooter = "&""Arial,Bold""&10" & UCase(msg6)
For a list of VBA formatting codes, see the VBA help file – in the Search box, type in: “Formatting Codes for Headers and Footers” (no quotes).
2. Hard coding VBA formatting codes
It is likely that you do not want users to change certain footers e.g. the right footer must always show say the date or show “Page 1 of whatever”. If that is the case, then remove Textbox7 from the Form, and change the macro to the following:
With ws.PageSetup
.CenterHeader = msg1 & vbLf & msg2
.LeftFooter = msg3 & vbLf & msg4 & vbLf & msg5
.CenterFooter = msg6
.RightFooter = "Page &P of &N"
End With
Or, something like:
.RightFooter = Format(Now(), "dd-mmm-yy")
Alternatively, leave Textbox7 on the Form, but add this code to the Form module:
Code:
Private Sub UserForm_Initialize()
TextBox7.Text = "Format(Now(), ""dd-mmm-yy"")"
End Sub
When you open the Form, Textbox7 is already filled out (color fill Textbox7 say pale red and tell your users not to touch the red text boxes).
HTH
Mike