Input Box to Add Information into Page Header

sanityendshere

New Member
Joined
Aug 7, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I have been making macros for a specific table format, which will be used for multiple sites in a particular project. Is it possible to use an input message box to add the site name into the page header? If so, how do I properly reference the input box information in the center header section? I tried putting MyInput where I currently have Site Name but that did not work. Thanks in advance for any help you can provide.

Sub InsertHeaderFooter()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

' Variables

Dim ws As Worksheet
Dim WS_Count As Integer
Dim i As Integer, j As Integer, k As Integer, LastColNum As Integer
Dim LastColName As String
Dim MyInput As Variant
Dim CellRange As Range 'for later on when you want to dynamically define and offset your range
' Set WS_Count equal to the number of worksheets in the active workbook ---------------------------------------------------------------------------

WS_Count = ActiveWorkbook.Worksheets.Count

' Use a msgbox to allow the User to add the site ID to the header

MyInput = InputBox("Site Name", "Please enter Site Name", "Site Name")

' Begin the loop.


For j = 1 To WS_Count
'Set/initialize the variables here that are specific to the sheet, so each time we move to a new sheet they update-------------------------
Set CellRange = Sheets(j).Range("C4") 'use this for your offsetting, to tailor references to the column count
i = 1 'Start at zero, the first instance of it being non-null will return 1 since it immediately adds one
Do Until CellRange.Cells(1, i + 1) = "" 'i+1 because you are actually trying to tell which cell is last (before the null) not which one is null
i = i + 1
Loop

'Sheets(j).PageSetup.PrintArea = ""
Application.PrintCommunication = False
With Sheets(j).PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&10" & "Appendix G Laboratory Data" & Chr(10) & "Groundwater" & Chr(10) & "Site Inspection Report, Site Name"
.RightHeader = ""
.LeftFooter = "&""Arial,Bold""&8" & "AECOM"
.CenterFooter = ""
.RightFooter = "&""Arial""&10" & "Appendix G-PFAS Groundwater" & Chr(10) & "Page &P of &N"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.85)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Zoom = 63
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True

End With


Next j

End Sub
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
.CenterHeader = "&""Arial,Bold""&10" & "Appendix G Laboratory Data" & Chr(10) & "Groundwater" & Chr(10) & "Site Inspection Report, Site Name"
Try changing the last bit after the & to:

"Site Inspection Report, " & MyInput
 

Watch MrExcel Video

Forum statistics

Threads
1,123,436
Messages
5,601,656
Members
414,463
Latest member
5amhunter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top