I am creating a report document all created in Excel.
In worksheet1 are a number of parameters that the user
enters such as:
Client NameSystem TypeSite AddressReport Type
On sheet2 I want to use this information in the headers and footers.
I have been able to do this with the
worksheet_SelectionChange sub see below
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim RepType As String, SysType As String, Client As String, Site As String
'Clear anything in the header and footer
Sheet2.PageSetup.CenterHeader = ""
Sheet2.PageSetup.CenterFooter = ""
RepType = Sheet1.Range("c18").Text
SysType = Sheet1.Range("c19").Text
Client = Sheet1.Range("c20").Text
Site = Sheet1.Range("c21").Text
'Enter information into Header with first line being larger than the second line
Sheet2.PageSetup.CenterHeader = "&04" & Chr(13) & "&B&14" & RepType & "&B&04" & Chr(13) & Chr(13) & "&10" & SysType
'Enter information into Footer with first line being larger than the second line
Sheet2.PageSetup.CenterFooter = "&B&12" & Client & "&B&10" & Chr(13) & Site
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, this updates all the time and once run I lose the undo facility which I really want to keep.
therefore want to only update the Headers and Footers on sheet 2 if one or more of the relevant cells on sheet 1 change.
A major thing I can't sort in my head is where code should be located and how to pass variables around, so please
include which module the code needs to be placed in.
Your help would be appreciated.