I have been searching for the fix for this but all I can find is how to transfer the text in a cell to the header. What I am looking for is the opposite - how to populate a cell with the text from the centerheader.
Anybody got any ideas?
Many thanks,
A
Try:
Code:Sub test() With Sheets("Sheet1") .Range("A1") = .PageSetup.CenterHeader End With End Sub
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
If you haven't already please take some time to read the Forum Rules.
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
I guess you could use the old XLM calls via a Name, eg:
Name: _PGHDR
RefersTo: =GET.DOCUMENT(54,GET.CELL(62,INDIRECT("R1C1",FALSE)))
called from a cell
=_PGHDR
I suspect a UDF would be more flexible... EDIT: ie: Domski's code(albeit in UDF form)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks, was trying it outside of a with statement and was showing an error.
The problem is though it is also returning &"Times New Roman,Regular" in front of the text, from the format setup I have when setting the header. The code I have to set the header is:
With Sheets("JLL").PageSetup
.CenterHeader = "&""Times New Roman""&10" & Sheets("JLL").Range("Ref_HeaderText_CAPS").Text
End With
To come up with a solution it might help to understand what you are trying to achieve with this.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
If you haven't already please take some time to read the Forum Rules.
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
I need a prompt to confirm that the header is correct when the sheet is printed, and if not change it manually (for some real technophobes!!). Have done this by having a reference cell that takes the header text in the workbook_open procedure, which then shows in a userform triggered on the workbook_beforeprint procedure. The text is edited in the userform, which updates the reference cell, and in turn updates the header text. I've got the reference cell in the middle so I can use functions to clean the text input in the userform.
All works nicely except for getting the text from the header on workbook_open!
How about using a BeforePrint event to set the header automatically every time you print?
Pop this on the ThisWorkbook page of your vba project:
Code:Private Sub Workbook_BeforePrint(Cancel As Boolean) With Sheets("JLL") .PageSetup.CenterHeader = "&""Times New Roman""&10" & .Range("Ref_HeaderText_CAPS").Text End With End Sub
Hope it helps,
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
If you haven't already please take some time to read the Forum Rules.
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Thanks,
I've had to over-complicate this so that the text in the userform links back to the header text, in case someone changes the header manually, therefore the the reference cell and the header won't match. That could really throw some of the old technophobes who will be using this!!
I have instead fixed the problem by using the right function to remove the known number of characters relating to the formatting from the left of the header text.
Cheers,
A
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks