除非空白，否则 Excel 页眉/页脚不会通过 VBA 更改
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18601744/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel headers/footers won't change via VBA unless blank
提问by bjelleklang
Disclaimer: It's been a few years since I worked (a lot) with VBA, so this might be an issue caused by confusing myself with what is essentially a very different language from what I usually deal with.
免责声明：自从我（很多）使用 VBA 以来已经有几年了，所以这可能是一个问题，因为我将自己与一种本质上与我通常处理的语言截然不同的语言混淆了。
So; I've got a workbook (Excel 2010) with multiple sheets (20+), most of whom are multi-page. To make things easier when printing everything, I want to add some sheet-specific headers with amongst others the name of the sheet, number of pages and so on.
所以; 我有一个包含多张工作表 (20+) 的工作簿 (Excel 2010)，其中大多数是多页的。为了使打印所有内容时更容易，我想添加一些特定于工作表的标题，其中包括工作表的名称、页数等。
I've written a tiny function that should (in theory) do this for me by iterating over all the sheets setting the header. However, for some reason it only works if the header is empty; if it already has a value it refuses to overwrite for some unknown reason.
我已经编写了一个小函数，它应该（理论上）通过迭代所有设置标题的工作表来为我做这件事。但是，出于某种原因，它仅在标题为空时才有效；如果它已经有一个值，它会出于某种未知原因拒绝覆盖。
Dim sheetIndex, numsheets As Integer
sheetIndex = 1
numsheets = Sheets.Count
' Loop through each sheet, but don't set any of them to active
While sheetIndex <= numsheets
Dim sheetname, role, labeltext As String
sheetname = Sheets(sheetIndex).name
role = GetRole(mode)
labeltext = "Some text - " & sheetname & " - " & role
With Sheets(sheetIndex).PageSetup
.LeftHeader = labeltext
.CenterHeader = ""
.RightHeader = "Page &[Page] / &[Pages]"
.LeftFooter = "&[Date] - &[Time]"
.CenterFooter = ""
.RightFooter = "Page &P / &N"
End With
sheetIndex = sheetIndex + 1
Wend
回答by Stewbob
I found a solution that seems to work for replacing text. For whatever reason, in the macro, you need to include the header/footer format character codesin order for it to work properly.
我找到了一个似乎适用于替换文本的解决方案。无论出于何种原因，在宏中，您都需要包含页眉/页脚格式字符代码才能使其正常工作。
This code worked to replace existing header text with new information:
此代码用于用新信息替换现有标题文本：
Sub test()
Dim sht As Worksheet
Set sht = Worksheets(1)
sht.PageSetup.LeftHeader = "&L left text"
sht.PageSetup.CenterHeader = "&C center Text"
sht.PageSetup.RightHeader = "&R right text"
End Sub
Without the &L, &C, and &Rcodes before the text, I could not get it to work.
如果没有&L，&C和&R文本之前的代码，我无法得到它的工作。
Some interesting behavior I found is that if you use the following code:
我发现一些有趣的行为是，如果您使用以下代码：
.CenterHeader = "&L some text"
it will actually put the some textin the LeftHeaderposition. This led me to believe that the formatting codes were very important.
它实际上会将some text置于该LeftHeader位置。这让我相信格式化代码非常重要。
回答by Buggy
The line Application.PrintCommunication = False(which is added by the macro recorder) before doing PageSetup screws up the formating via VBA.
Application.PrintCommunication = False在执行 PageSetup 之前的行（由宏记录器添加）通过 VBA 搞砸了格式化。
If your code has got this line in it, try removing it. That solved my problem with setting the header and footer via VBA.
如果您的代码中有此行，请尝试将其删除。这解决了我通过 VBA 设置页眉和页脚的问题。
回答by glbcpa
I've read StackOverflow for years and this is the first time I've actually been able to post a solution ... hope it helps someone!! Also, you need to remember, I am a CPA not a programmer ;-)
我已经阅读 StackOverflow 多年了，这是我第一次真正能够发布解决方案......希望它对某人有所帮助！！另外，您需要记住，我是注册会计师而不是程序员；-)
I am reading some values from the ActiveSheet to populate the header. The application is a tax election that will be sent with a tax return so it must have the taxpayer's name and social security number at the top.
我正在从 ActiveSheet 读取一些值来填充标题。该申请是一种税务选择，将与纳税申报表一起发送，因此它必须在顶部显示纳税人的姓名和社会安全号码。
Sub PrintElection()
' Print preview the MTM Election
If Range("Tax_Year").Value = Range("First_MTM_year").Value Then
ActiveSheet.PageSetup.LeftHeader = Format(Worksheets("Election").Range("Taxpayer_Name").Value)
ActiveSheet.PageSetup.RightHeader = Format(Worksheets("Election").Range("Taxpayer_SSN").Value)
ActiveWindow.SelectedSheets.PrintPreview
Else
MsgBox "The 'Effective For Tax Year' date must EQUAL the 'First MTM year' date", vbOKOnly, "Check Years"
Sheets("Roadmap").Select
Range("First_MTM_year").Select
End If
End Sub
It checks to see if the Mark-to-Market election year is the same as the election form then formats the election page.
它会检查按市值计算的选举年份是否与选举表格相同，然后格式化选举页面。
回答by Greg L
I split the sheet print setup into 2 loops. First loop with Application.PrintCommunication = False I run the non-header/footer setup. I then set Application.PrintCommunication = True and run the header/footer setup in a second loop. Appears to run faster than in XL2003, and applies the header/footer correctly. Until MS fixes this bug, that works fine for me.
我将单张打印设置分成 2 个循环。第一个循环 Application.PrintCommunication = False 我运行非页眉/页脚设置。然后我设置 Application.PrintCommunication = True 并在第二个循环中运行页眉/页脚设置。似乎比 XL2003 运行得更快，并正确应用页眉/页脚。在 MS 修复此错误之前，这对我来说很好用。
