I use text boxes (1 each for Left, Center, and Right Headers and Left,
Center, and Right Footers) on a userform to get information to include
in the headers and footers of a worksheet.

I found out that headers and footers can only hold a total of only 255
characters each (at least in Excel 2000). And so I'm not sure whether
the error is generated from that (the combined number of characters in
all three header textboxes along with the number of characters in the
ExecuteExcel4Macro variable (pSetup).

Does anyone know how many characters in the ExecuteExcel4Macro apply
toward the 255 character limit so I can display a message box telling
the user to limit the character count to xxx or fewer?

Or does anyone know how to shorten this code statement (e.g., can it
be broken into two separate ExecuteExcel4Macro statements (1 for
header and 1 for footer) as a workaround for the limit? Or is there a
limit to the string length for the ExecuteExcel4Macro?

Thanks much,
Geoff

Code:

Sub PageSetup(Optional pTextLHeader As String, Optional pTextCHeader
As String, Optional pTextRHeader As String, Optional pTextLFooter As
String, Optional pTextCFooter As String, Optional pTextRFooter As
String) ', Optional pCellGridlines As Boolean)

Dim pHeaderText As String, pFooterText As String
Dim pMarginLeft As String, pMarginRight As String, pMarginHead As
String, pMarginFoot As String
Dim pMarginTop As String, pMarginBottom As String
Dim pRowColHeadings As Boolean, pCellComments As Boolean,
pCellGridlines As Boolean
Dim pQuality As String, pCenterHorizontally As Boolean,
pCenterVertically As Boolean
Dim pOrientation As Integer, pDraft As Boolean, pPaperSize As
Integer, pPageNumber As String
Dim pPageOrder As String, pBWCells As Boolean, pScale As String

Dim pSetup As String

If pTextLHeader = "" Then pTextLHeader = "Skill Description"
If pTextCHeader = "" Then pTextCHeader = "Practice Sheet"
If pTextRHeader = "[Sheet]" Or pTextRHeader = "" Then pTextRHeader
= "&A"

If pTextLFooter = "" Then pTextLFooter = "Aim: "
If pTextCFooter = "" Then
pTextCFooter = " "
ElseIf IsNumeric(Left(pTextCFooter, 1)) Then
pTextCFooter = " " & pTextCFooter
End If
If pTextRFooter = "Page [x] of [y]" Then pTextRFooter = "Page &P
of &N"

pHeaderText = """&L&B" & pTextLHeader & "&C&B" & pTextCHeader &
"&R&B" & pTextRHeader & """"
pFooterText = """&L&B" & pTextLFooter & "&C&06" & pTextCFooter &
"&R&B" & pTextRFooter & """"
pMarginLeft = 0.5
pMarginRight = 0.5
pMarginTop = 1
pMarginBottom = 0.75
pMarginHead = 0.65
pMarginFoot = 0.5
pRowColHeadings = False
pCellGridlines = False
pCellComments = False
pQuality = ""
pCenterHorizontally = True
pCenterVertically = True
pOrientation = 2
pDraft = False
pPaperSize = 1
pPageNumber = """Auto"""
pPageOrder = 1
pBWCells = False
pScale = 100

pSetup = "PAGE.SETUP(" & pHeaderText & "," & pFooterText & "," &
pMarginLeft & "," & pMarginRight & ","
pSetup = pSetup & pMarginTop & "," & pMarginBottom & "," &
pRowColHeadings & "," & pCellGridlines & "," & pCenterHorizontally &
","
pSetup = pSetup & pCenterVertically & "," & pOrientation & "," &
pPaperSize & "," & pScale & ","
pSetup = pSetup & pPageNumber & "," & pPageOrder & "," & pBWCells
& "," & pQuality & ","
pSetup = pSetup & pMarginHead & "," & pMarginFoot & "," &
pCellComments & "," & pDraft & ")"

Application.ExecuteExcel4Macro pSetup
Call FixReturnsInHeaders

End Sub

Sub FixReturnsInHeaders()
Dim ws As Worksheet
Dim lh As String, ch As String, rh As String
Dim lf As String, cf As String, rf As String
Dim i As Integer, tmp1 As String, tmp2 As String

Set ws = ActiveSheet



If LHeaderHasReturn Then
lh = ws.PageSetup.LeftHeader
For i = 1 To Len(lh)
If Mid$(lh, i, 2) = vbCrLf Then
tmp1 = Left$(lh, i - 1)
tmp2 = Right$(lh, Len(lh) - i - 1)
lh = tmp1 & vbLf & tmp2
ws.PageSetup.LeftHeader = lh
i = i - 1
End If
Next i
End If
If CHeaderHasReturn Then
ch = ws.PageSetup.CenterHeader
For i = 1 To Len(ch)
If Mid$(ch, i, 2) = vbCrLf Then
tmp1 = Left$(ch, i - 1)
tmp2 = Right$(ch, Len(ch) - i - 1)
ch = tmp1 & vbLf & tmp2
ws.PageSetup.CenterHeader = ch
i = i - 1
End If
Next i
End If
If RHeaderHasReturn Then
rh = ws.PageSetup.RightHeader
For i = 1 To Len(rh)
If Mid$(rh, i, 2) = vbCrLf Then
tmp1 = Left$(rh, i - 1)
tmp2 = Right$(rh, Len(rh) - i - 1)
lh = tmp1 & vbLf & tmp2
ws.PageSetup.RightHeader = rh
i = i - 1
End If
Next i
End If
If LFooterHasReturn Then
lf = ws.PageSetup.LeftFooter
For i = 1 To Len(lf)
If Mid$(lf, i, 2) = vbCrLf Then
tmp1 = Left$(lf, i - 1)
tmp2 = Right$(lf, Len(lf) - i - 1)
lf = tmp1 & vbLf & tmp2
ws.PageSetup.LeftFooter = lf
i = i - 1
End If
Next i
End If
If CFooterHasReturn Then
cf = ws.PageSetup.CenterFooter
For i = 1 To Len(cf)
If Mid$(cf, i, 2) = vbCrLf Then
tmp1 = Left$(cf, i - 1)
tmp2 = Right$(cf, Len(cf) - i - 1)
cf = tmp1 & vbLf & tmp2
ws.PageSetup.CenterFooter = cf
i = i - 1
End If
Next i
End If
If RFooterHasReturn Then
rf = ws.PageSetup.RightFooter
For i = 1 To Len(rf)
If Mid$(rf, i, 2) = vbCrLf Then
tmp1 = Left$(rf, i - 1)
tmp2 = Right$(rf, Len(rf) - i - 1)
rf = tmp1 & vbLf & tmp2
ws.PageSetup.RightFooter = rf
i = i - 1
End If
Next i
End If

End Sub