I'm using Excel 2007. Here's the code:
Dim ws As Worksheet, lf As String, cf As String, rf As String
...
rf = "&""-,Italic""&K7F7F7F" & "Some text"
...
ws.PageSetup.LeftFooter = lf
ws.PageSetup.CenterFooter = cf
ws.PageSetup.RightFooter = rf
I get the following error on the last line.
Run-time error '1004': Unable to set the RightFooter property of the PageSetup class
The LeftFooter
and CenterFooter
properties get set just fine. However, if I change the order in which the properties are set, then the last one, be it LeftFooter
or CenterFooter
triggers the error, and the previous two always get set successfully. So that tells me that it's not an error in the syntax for the footer or anything.
Note that I'm setting some font to italic and a different color in all three footer sections. If I remove the part about changing to italic and color (only on the last of the footer sections), then it executes without error.
EDIT Also, if I remove only the italic part, then the code executes without error./EDIT
I was getting this error at first, then played around with variables and concatenation until finally it worked, but now it's broken again, and I cannot figure out the problem.
This question seems possibly related, but I'm not sure. I tried the fix that worked for that guy (using a Replace
that does nothing really except return a new string), but I still get the same error.
I've tried using no concatenation to set lf
, cf
, and rf
, merging variables, and a lot of other stuff (in desperation) that should not have an effect anyway, and I keep getting this error. Does anyone have any idea what the problem could be?
UPDATE
I never found an answer to my question, so I just removed the italic font style, and the rest was working. I was happy enough. Now, though, I've had to create the header and footer for a landscape sheet (the other was portrait). The header and footer are the exact same as for the portrait sheet excep that some text in the center footer section is longer. The rest is the exact same. The entire procedure to create the footer was copied and only the one piece of text was changed. Now I'm getting the same error as before (without the italic part). As before, it occurs on the last footer section being set. Note that the portrait footer is still functioning properly. This is driving me nuts, and I'm losing precious time on a matter that you'd think would be fairly straightforward.
Can anyone replicate the error?
Here's the full code:
Private Const LINE_CLR As String = "&K538ED5" ' RGB(83, 142, 213)
'Private Const TEXT_STYLE AS STRING = "&""-,Italic"""
Private Const TEXT_STYLE As String = ""
Private Const TEXT_CLR As String = "&K7F7F7F" ' RGB(127, 127, 127)
Private Const FTR_INDENT_RT As String = " "
Private Const FTR_INDENT_LT As String = " "
' *****************************************************************************
Private Function HOR_LINE_PORTRAIT() As String
HOR_LINE_PORTRAIT = String(128, "_")
End Function
' *****************************************************************************
Private Function HOR_LINE_LANDSCAPE() As String
HOR_LINE_LANDSCAPE = String(191, "_")
End Function
' *****************************************************************************
Public Sub CreateFooter_Landscape( _
ByRef ws As Worksheet, _
ByVal txt1 As String, _
ByVal txt2 As String, _
ByVal txt3 As String)
Dim txtL As String, txtC As String, txtR As String
Dim lf As String, cf As String, rf As String ' left, center, and right footer
txtL = "Some text1: " & txt1
txtC = "Some text2: " & txt2
txtR = "Some text3 " & txt3
lf = TEXT_STYLE & TEXT_CLR & FTR_INDENT_LT & txtL
cf = LINE_CLR & HOR_LINE_LANDSCAPE & Chr(10) & TEXT_STYLE & TEXT_CLR & txtC
rf = TEXT_STYLE & TEXT_CLR & txtR & FTR_INDENT_RT
ws.PageSetup.LeftFooter = lf
ws.PageSetup.CenterFooter = cf
ws.PageSetup.RightFooter = rf
End Sub