I know that this is probably a very simple issue but I can't find an answer
and everything that I have tried doesn't work. How can I modify this code so
I can either use Excel's "Range Names" ie LftHdrTxt which refers to cell B11
on Sheet2, or ranges Sheet2.Range("B11"). Currently the text LftHdrTxt is
placed in the left header and CtrHdrTxt in the center header etc. The quotes
are required. The PageSetupXl4m defines LeftHead As String with the
following "Optional LeftHead As String," etc. I'm trying to update the
Headers and Footers on 75 sheets from values input by a user in cells, on a
single sheet, in an excel template. Because of different page setup's on
many of the sheets ie portrait on some and landscape on Others. Because of
this I can't simply activate all of the sheets then enter the desired
information in page setup. I'm trying to modify code I found on the
McGimpsey & Associates website. www.mcgimpsey.com/excel/udfs/pagesetup.html.

PageSetupXL4M LeftHead:="LftHdrTxt", CenterHead:="CtrHdrTxt",
RightHead:="RhtHdrTxt", LeftFoot:="LftFtrTxt", CenterFoot:="CtrFtrTxt",
RightFoot:="RhtFtrTxt"

Here is the code for PageSetupXL4M

Public Sub PageSetupXL4M( _
Optional LeftHead As String, _
Optional CenterHead As String, _
Optional RightHead As String, _
Optional LeftFoot As String, _
Optional CenterFoot As String, _
Optional RightFoot As String, _
Optional LeftMarginInches As String, _
Optional RightMarginInches As String, _
Optional TopMarginInches As String, _
Optional BottomMarginInches As String, _
Optional HeaderMarginInches As String, _
Optional FooterMarginInches As String, _
Optional PrintHeadings As String, _
Optional PrintGridlines As String, _
Optional PrintComments As String, _
Optional PrintQuality As String, _
Optional CenterHorizontally As String, _
Optional CenterVertically As String, _
Optional Orientation As String, _
Optional Draft As String, _
Optional PaperSize As String, _
Optional FirstPageNumber As String, _
Optional Order As String, _
Optional BlackAndWhite As String, _
Optional Zoom As String)
'based on a post by John Green in
'microsoft.public.excel.programming
'on 21 January 2001:
'http://google.com/groups?selm=VA.00000b2f.0028c7e5%40mara9"
Const c As String = ","
Dim pgSetup As String
Dim head As String
Dim foot As String
If LeftHead < "" Then head = "&L" & LeftHead
If CenterHead < "" Then head = head & "&C" & CenterHead
If RightHead < "" Then head = head & "&R" & RightHead
If Not head="" Then head = """" & head & """"
If LeftFoot < "" Then foot = "&L" & LeftFoot
If CenterFoot < "" Then foot = foot & "&C" & CenterFoot
If RightFoot < "" Then foot = foot & "&R" & RightFoot
If Not foot="" Then foot = """" & foot & """"

pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
LeftMarginInches & c & RightMarginInches & c & _
TopMarginInches & c & BottomMarginInches & c & _
PrintHeadings & c & PrintGridlines & c & _
CenterHorizontally & c & CenterVertically & c & _
Orientation & c & PaperSize & c & Zoom & c & _
FirstPageNumber & c & Order & c & BlackAndWhite & c & _
PrintQuality & c & HeaderMarginInches & c & _
FooterMarginInches & c & PrintComments & c & Draft & ")"
Application.ExecuteExcel4Macro pgSetup
End Sub

If you have defined cell B11 on Sheet1 to have the Name:
be_eleven
then either:

Sub try1()
ActiveSheet.PageSetup.LeftHeader = Range("B11").Value
End Sub

or

Sub try2()
ActiveSheet.PageSetup.LeftHeader = Range("be_eleven").Value
End Sub

will set that left header
--
Gary''s Student - gsnu200kL


"Breck" wrote:

I know that this is probably a very simple issue but I can't find an answer
and everything that I have tried doesn't work. How can I modify this code so
I can either use Excel's "Range Names" ie LftHdrTxt which refers to cell B11
on Sheet2, or ranges Sheet2.Range("B11"). Currently the text LftHdrTxt is
placed in the left header and CtrHdrTxt in the center header etc. The quotes
are required. The PageSetupXl4m defines LeftHead As String with the
following "Optional LeftHead As String," etc. I'm trying to update the
Headers and Footers on 75 sheets from values input by a user in cells, on a
single sheet, in an excel template. Because of different page setup's on
many of the sheets ie portrait on some and landscape on Others. Because of
this I can't simply activate all of the sheets then enter the desired
information in page setup. I'm trying to modify code I found on the
McGimpsey & Associates website. www.mcgimpsey.com/excel/udfs/pagesetup.html.

PageSetupXL4M LeftHead:="LftHdrTxt", CenterHead:="CtrHdrTxt",
RightHead:="RhtHdrTxt", LeftFoot:="LftFtrTxt", CenterFoot:="CtrFtrTxt",
RightFoot:="RhtFtrTxt"

Here is the code for PageSetupXL4M

Public Sub PageSetupXL4M( _
Optional LeftHead As String, _
Optional CenterHead As String, _
Optional RightHead As String, _
Optional LeftFoot As String, _
Optional CenterFoot As String, _
Optional RightFoot As String, _
Optional LeftMarginInches As String, _
Optional RightMarginInches As String, _
Optional TopMarginInches As String, _
Optional BottomMarginInches As String, _
Optional HeaderMarginInches As String, _
Optional FooterMarginInches As String, _
Optional PrintHeadings As String, _
Optional PrintGridlines As String, _
Optional PrintComments As String, _
Optional PrintQuality As String, _
Optional CenterHorizontally As String, _
Optional CenterVertically As String, _
Optional Orientation As String, _
Optional Draft As String, _
Optional PaperSize As String, _
Optional FirstPageNumber As String, _
Optional Order As String, _
Optional BlackAndWhite As String, _
Optional Zoom As String)
'based on a post by John Green in
'microsoft.public.excel.programming
'on 21 January 2001:
'http://google.com/groups?selm=VA.00000b2f.0028c7e5%40mara9"
Const c As String = ","
Dim pgSetup As String
Dim head As String
Dim foot As String
If LeftHead < "" Then head = "&L" & LeftHead
If CenterHead < "" Then head = head & "&C" & CenterHead
If RightHead < "" Then head = head & "&R" & RightHead
If Not head="" Then head = """" & head & """"
If LeftFoot < "" Then foot = "&L" & LeftFoot
If CenterFoot < "" Then foot = foot & "&C" & CenterFoot
If RightFoot < "" Then foot = foot & "&R" & RightFoot
If Not foot="" Then foot = """" & foot & """"

pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
LeftMarginInches & c & RightMarginInches & c & _
TopMarginInches & c & BottomMarginInches & c & _
PrintHeadings & c & PrintGridlines & c & _
CenterHorizontally & c & CenterVertically & c & _
Orientation & c & PaperSize & c & Zoom & c & _
FirstPageNumber & c & Order & c & BlackAndWhite & c & _
PrintQuality & c & HeaderMarginInches & c & _
FooterMarginInches & c & PrintComments & c & Draft & ")"
Application.ExecuteExcel4Macro pgSetup
End Sub

Gary's Student

I have used pagesetup successfully usuing range names but running
PageSetup on 75 sheets would take 30 minutes or longer because that
method is so slow. The excel4macro page.setup takes 3 minutes. There
have been many posts on this issue with PageSetup.


On Jan 27, 8:01*am, Gary''s Student
wrote:
If you have defined cell B11 on Sheet1 to have the Name:
be_eleven
then either:

Sub try1()
ActiveSheet.PageSetup.LeftHeader = Range("B11").Value
End Sub

or

Sub try2()
ActiveSheet.PageSetup.LeftHeader = Range("be_eleven").Value
End Sub

will set that left header
--
Gary''s Student - gsnu200kL

"Breck" wrote:
I know that this is probably a very simple issue but I can't find an answer
and everything that I have tried doesn't work. How can I modify this code so
I can either use Excel's "Range Names" ie LftHdrTxt which refers to cell B11
on Sheet2, or ranges Sheet2.Range("B11"). Currently the text LftHdrTxt is
placed in the left header and CtrHdrTxt in the center header etc. The quotes
are required. The PageSetupXl4m defines LeftHead As String with the
following "Optional LeftHead As String," etc. I'm trying to update the
Headers and Footers on 75 sheets from values input by a user in cells, on a
single sheet, in an excel template. Because of different page setup's on
many of the sheets *ie portrait on some and landscape on Others. Because of
this I can't simply activate all of the sheets then enter the desired
information in page setup. I'm trying to modify code I found on the
McGimpsey & Associates website.www.mcgimpsey.com/excel/udfs/pagesetup.html.

PageSetupXL4M LeftHead:="LftHdrTxt", CenterHead:="CtrHdrTxt",
RightHead:="RhtHdrTxt", *LeftFoot:="LftFtrTxt", CenterFoot:="CtrFtrTxt",
RightFoot:="RhtFtrTxt"

Here is the code for PageSetupXL4M

Public Sub PageSetupXL4M( _
* * * * Optional LeftHead As String, _
* * * * Optional CenterHead As String, _
* * * * Optional RightHead As String, _
* * * * Optional LeftFoot As String, _
* * * * Optional CenterFoot As String, _
* * * * Optional RightFoot As String, _
* * * * Optional LeftMarginInches As String, _
* * * * Optional RightMarginInches As String, _
* * * * Optional TopMarginInches As String, _
* * * * Optional BottomMarginInches As String, _
* * * * Optional HeaderMarginInches As String, _
* * * * Optional FooterMarginInches As String, _
* * * * Optional PrintHeadings As String, _
* * * * Optional PrintGridlines As String, _
* * * * Optional PrintComments As String, _
* * * * Optional PrintQuality As String, _
* * * * Optional CenterHorizontally As String, _
* * * * Optional CenterVertically As String, _
* * * * Optional Orientation As String, _
* * * * Optional Draft As String, _
* * * * Optional PaperSize As String, _
* * * * Optional FirstPageNumber As String, _
* * * * Optional Order As String, _
* * * * Optional BlackAndWhite As String, _
* * * * Optional Zoom As String)
* * *'based on a post by John Green in
* * *'microsoft.public.excel.programming
* * *'on 21 January 2001:
* * *'http://google.com/groups?selm=VA.00000b2f.0028c7e5%40mara9"
* * *Const c As String = ","
* * *Dim pgSetup As String
* * *Dim head As String
* * *Dim foot As String
* * *If LeftHead < "" Then head = "&L" & LeftHead
* * *If CenterHead < "" Then head = head & "&C" & CenterHead
* * *If RightHead < "" Then head = head & "&R" & RightHead
* * *If Not head="" Then head = """" & head & """"
* * *If LeftFoot < "" Then foot = "&L" & LeftFoot
* * *If CenterFoot < "" Then foot = foot & "&C" & CenterFoot
* * *If RightFoot < "" Then foot = foot & "&R" & RightFoot
* * *If Not foot="" Then foot = """" & foot & """"

* * *pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
* * * *LeftMarginInches & c & RightMarginInches & c & _
* * * *TopMarginInches & c & BottomMarginInches & c & _
* * * *PrintHeadings & c & PrintGridlines & c & _
* * * *CenterHorizontally & c & CenterVertically & c & _
* * * *Orientation & c & PaperSize & c & Zoom & c & _
* * * *FirstPageNumber & c & Order & c & BlackAndWhite & c & _
* * * *PrintQuality & c & HeaderMarginInches & c & _
* * * *FooterMarginInches & c & PrintComments & c & Draft & ")"
* * *Application.ExecuteExcel4Macro pgSetup
* *End Sub

I couldn't paste your code as I got "too many continuations". Following
might solve two problems at the same time

' top of module

Type hHeaders
LeftHead As String
CenterHead As String
RightHead As String
LeftFoot As String
CenterFoot As String
RightFoot As String
End Type

Sub abc()
Dim h As tHeaders

h.LeftFoot = Range("LftHdrTxt").Text
h.CenterHead = Range("CtrHdrTxt").Text
' etc

PageSetupXL4M h, next-arg, etc

End Sub

Public Sub PageSetupXL4M(h as hHeaders,

where tHds replaces all the optional strings now included in the 'Type'

you'll need to change things like

If LeftHead < "" Then head = "&L" & LeftHead
to
If h.LeftHead < "" Then head = "&L" & h.LeftHead

Maybe you could combine all those margin arg's in a 'Type'
Public Type mMargins
mLeftMarginInches as string
etc


Regards,
Peter T



"Breck" wrote in message
...
I know that this is probably a very simple issue but I can't find an answer
and everything that I have tried doesn't work. How can I modify this code
so I can either use Excel's "Range Names" ie LftHdrTxt which refers to cell
B11 on Sheet2, or ranges Sheet2.Range("B11"). Currently the text LftHdrTxt
is placed in the left header and CtrHdrTxt in the center header etc. The
quotes are required. The PageSetupXl4m defines LeftHead As String with the
following "Optional LeftHead As String," etc. I'm trying to update the
Headers and Footers on 75 sheets from values input by a user in cells, on a
single sheet, in an excel template. Because of different page setup's on
many of the sheets ie portrait on some and landscape on Others. Because of
this I can't simply activate all of the sheets then enter the desired
information in page setup. I'm trying to modify code I found on the
McGimpsey & Associates website.
www.mcgimpsey.com/excel/udfs/pagesetup.html.

PageSetupXL4M LeftHead:="LftHdrTxt", CenterHead:="CtrHdrTxt",
RightHead:="RhtHdrTxt", LeftFoot:="LftFtrTxt", CenterFoot:="CtrFtrTxt",
RightFoot:="RhtFtrTxt"

Here is the code for PageSetupXL4M

Public Sub PageSetupXL4M( _
Optional LeftHead As String, _
Optional CenterHead As String, _
Optional RightHead As String, _
Optional LeftFoot As String, _
Optional CenterFoot As String, _
Optional RightFoot As String, _
Optional LeftMarginInches As String, _
Optional RightMarginInches As String, _
Optional TopMarginInches As String, _
Optional BottomMarginInches As String, _
Optional HeaderMarginInches As String, _
Optional FooterMarginInches As String, _
Optional PrintHeadings As String, _
Optional PrintGridlines As String, _
Optional PrintComments As String, _
Optional PrintQuality As String, _
Optional CenterHorizontally As String, _
Optional CenterVertically As String, _
Optional Orientation As String, _
Optional Draft As String, _
Optional PaperSize As String, _
Optional FirstPageNumber As String, _
Optional Order As String, _
Optional BlackAndWhite As String, _
Optional Zoom As String)
'based on a post by John Green in
'microsoft.public.excel.programming
'on 21 January 2001:
'http://google.com/groups?selm=VA.00000b2f.0028c7e5%40mara9"
Const c As String = ","
Dim pgSetup As String
Dim head As String
Dim foot As String
If LeftHead < "" Then head = "&L" & LeftHead
If CenterHead < "" Then head = head & "&C" & CenterHead
If RightHead < "" Then head = head & "&R" & RightHead
If Not head="" Then head = """" & head & """"
If LeftFoot < "" Then foot = "&L" & LeftFoot
If CenterFoot < "" Then foot = foot & "&C" & CenterFoot
If RightFoot < "" Then foot = foot & "&R" & RightFoot
If Not foot="" Then foot = """" & foot & """"

pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
LeftMarginInches & c & RightMarginInches & c & _
TopMarginInches & c & BottomMarginInches & c & _
PrintHeadings & c & PrintGridlines & c & _
CenterHorizontally & c & CenterVertically & c & _
Orientation & c & PaperSize & c & Zoom & c & _
FirstPageNumber & c & Order & c & BlackAndWhite & c & _
PrintQuality & c & HeaderMarginInches & c & _
FooterMarginInches & c & PrintComments & c & Draft & ")"
Application.ExecuteExcel4Macro pgSetup
End Sub

typo

Dim h As tHeaders
should have been -
Dim h As hHeaders

Might be others, I didn't test

Peter T

"Peter T" <peter_t@discussions wrote in message
...
I couldn't paste your code as I got "too many continuations". Following
might solve two problems at the same time

' top of module

Type hHeaders
LeftHead As String
CenterHead As String
RightHead As String
LeftFoot As String
CenterFoot As String
RightFoot As String
End Type

Sub abc()
Dim h As tHeaders

h.LeftFoot = Range("LftHdrTxt").Text
h.CenterHead = Range("CtrHdrTxt").Text
' etc

PageSetupXL4M h, next-arg, etc

End Sub

Public Sub PageSetupXL4M(h as hHeaders,

where tHds replaces all the optional strings now included in the 'Type'

you'll need to change things like

If LeftHead < "" Then head = "&L" & LeftHead
to
If h.LeftHead < "" Then head = "&L" & h.LeftHead

Maybe you could combine all those margin arg's in a 'Type'
Public Type mMargins
mLeftMarginInches as string
etc


Regards,
Peter T



"Breck" wrote in message
...
I know that this is probably a very simple issue but I can't find an
answer and everything that I have tried doesn't work. How can I modify
this code so I can either use Excel's "Range Names" ie LftHdrTxt which
refers to cell B11 on Sheet2, or ranges Sheet2.Range("B11"). Currently the
text LftHdrTxt is placed in the left header and CtrHdrTxt in the center
header etc. The quotes are required. The PageSetupXl4m defines LeftHead As
String with the following "Optional LeftHead As String," etc. I'm trying
to update the Headers and Footers on 75 sheets from values input by a user
in cells, on a single sheet, in an excel template. Because of different
page setup's on many of the sheets ie portrait on some and landscape on
Others. Because of this I can't simply activate all of the sheets then
enter the desired information in page setup. I'm trying to modify code I
found on the McGimpsey & Associates website.
www.mcgimpsey.com/excel/udfs/pagesetup.html.

PageSetupXL4M LeftHead:="LftHdrTxt", CenterHead:="CtrHdrTxt",
RightHead:="RhtHdrTxt", LeftFoot:="LftFtrTxt", CenterFoot:="CtrFtrTxt",
RightFoot:="RhtFtrTxt"

Here is the code for PageSetupXL4M

Public Sub PageSetupXL4M( _
Optional LeftHead As String, _
Optional CenterHead As String, _
Optional RightHead As String, _
Optional LeftFoot As String, _
Optional CenterFoot As String, _
Optional RightFoot As String, _
Optional LeftMarginInches As String, _
Optional RightMarginInches As String, _
Optional TopMarginInches As String, _
Optional BottomMarginInches As String, _
Optional HeaderMarginInches As String, _
Optional FooterMarginInches As String, _
Optional PrintHeadings As String, _
Optional PrintGridlines As String, _
Optional PrintComments As String, _
Optional PrintQuality As String, _
Optional CenterHorizontally As String, _
Optional CenterVertically As String, _
Optional Orientation As String, _
Optional Draft As String, _
Optional PaperSize As String, _
Optional FirstPageNumber As String, _
Optional Order As String, _
Optional BlackAndWhite As String, _
Optional Zoom As String)
'based on a post by John Green in
'microsoft.public.excel.programming
'on 21 January 2001:
'http://google.com/groups?selm=VA.00000b2f.0028c7e5%40mara9"
Const c As String = ","
Dim pgSetup As String
Dim head As String
Dim foot As String
If LeftHead < "" Then head = "&L" & LeftHead
If CenterHead < "" Then head = head & "&C" & CenterHead
If RightHead < "" Then head = head & "&R" & RightHead
If Not head="" Then head = """" & head & """"
If LeftFoot < "" Then foot = "&L" & LeftFoot
If CenterFoot < "" Then foot = foot & "&C" & CenterFoot
If RightFoot < "" Then foot = foot & "&R" & RightFoot
If Not foot="" Then foot = """" & foot & """"

pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
LeftMarginInches & c & RightMarginInches & c & _
TopMarginInches & c & BottomMarginInches & c & _
PrintHeadings & c & PrintGridlines & c & _
CenterHorizontally & c & CenterVertically & c & _
Orientation & c & PaperSize & c & Zoom & c & _
FirstPageNumber & c & Order & c & BlackAndWhite & c & _
PrintQuality & c & HeaderMarginInches & c & _
FooterMarginInches & c & PrintComments & c & Draft & ")"
Application.ExecuteExcel4Macro pgSetup
End Sub

Peter T


I pasted the code from the website verbatum. I had to remove many of
the continuation lines "_" so that 2 or 3 lines of code would be on
one line. I'll try you suggestion latter tonight when I am home.

On Jan 27, 8:15*am, "Peter T" <peter_t@discussions wrote:
typo

Dim h As tHeaders

should have been -
Dim h As hHeaders

Might be others, I didn't test

Peter T

"Peter T" <peter_t@discussions wrote in message

...

I couldn't paste your code as I got "too many continuations". Following
might solve two problems at the same time

' top of module

Type hHeaders
LeftHead As String
CenterHead As String
RightHead As String
LeftFoot As String
CenterFoot As String
RightFoot As String
End Type

Sub abc()
Dim h As tHeaders

h.LeftFoot = Range("LftHdrTxt").Text
h.CenterHead = Range("CtrHdrTxt").Text
' etc

PageSetupXL4M h, next-arg, etc

End Sub

Public Sub PageSetupXL4M(h as hHeaders,

where tHds replaces all the optional strings now included in the 'Type'

you'll need to change things like

* * If LeftHead < "" Then head = "&L" & LeftHead
to
* *If h.LeftHead < "" Then head = "&L" & h.LeftHead

Maybe you could combine all those margin arg's in a 'Type'
Public Type mMargins
mLeftMarginInches as string
etc

Regards,
Peter T

"Breck" wrote in message
...
I know that this is probably a very simple issue but I can't find an
answer and everything that I have tried doesn't work. How can I modify
this code so I can either use Excel's "Range Names" ie LftHdrTxt which
refers to cell B11 on Sheet2, or ranges Sheet2.Range("B11"). Currently the
text LftHdrTxt is placed in the left header and CtrHdrTxt in the center
header etc. The quotes are required. The PageSetupXl4m defines LeftHead As
String with the following "Optional LeftHead As String," etc. I'm trying
to update the Headers and Footers on 75 sheets from values input by a user
in cells, on a single sheet, in an excel template. Because of different
page setup's on many of the sheets *ie portrait on some and landscape on
Others. Because of this I can't simply activate all of the sheets then
enter the desired information in page setup. I'm trying to modify code I
found on the McGimpsey & Associates website.
www.mcgimpsey.com/excel/udfs/pagesetup.html.

PageSetupXL4M LeftHead:="LftHdrTxt", CenterHead:="CtrHdrTxt",
RightHead:="RhtHdrTxt", *LeftFoot:="LftFtrTxt", CenterFoot:="CtrFtrTxt",
RightFoot:="RhtFtrTxt"

Here is the code for PageSetupXL4M

Public Sub PageSetupXL4M( _
* * * *Optional LeftHead As String, _
* * * *Optional CenterHead As String, _
* * * *Optional RightHead As String, _
* * * *Optional LeftFoot As String, _
* * * *Optional CenterFoot As String, _
* * * *Optional RightFoot As String, _
* * * *Optional LeftMarginInches As String, _
* * * *Optional RightMarginInches As String, _
* * * *Optional TopMarginInches As String, _
* * * *Optional BottomMarginInches As String, _
* * * *Optional HeaderMarginInches As String, _
* * * *Optional FooterMarginInches As String, _
* * * *Optional PrintHeadings As String, _
* * * *Optional PrintGridlines As String, _
* * * *Optional PrintComments As String, _
* * * *Optional PrintQuality As String, _
* * * *Optional CenterHorizontally As String, _
* * * *Optional CenterVertically As String, _
* * * *Optional Orientation As String, _
* * * *Optional Draft As String, _
* * * *Optional PaperSize As String, _
* * * *Optional FirstPageNumber As String, _
* * * *Optional Order As String, _
* * * *Optional BlackAndWhite As String, _
* * * *Optional Zoom As String)
* * 'based on a post by John Green in
* * 'microsoft.public.excel.programming
* * 'on 21 January 2001:
* * 'http://google.com/groups?selm=VA.00000b2f.0028c7e5%40mara9"
* * Const c As String = ","
* * Dim pgSetup As String
* * Dim head As String
* * Dim foot As String
* * If LeftHead < "" Then head = "&L" & LeftHead
* * If CenterHead < "" Then head = head & "&C" & CenterHead
* * If RightHead < "" Then head = head & "&R" & RightHead
* * If Not head="" Then head = """" & head & """"
* * If LeftFoot < "" Then foot = "&L" & LeftFoot
* * If CenterFoot < "" Then foot = foot & "&C" & CenterFoot
* * If RightFoot < "" Then foot = foot & "&R" & RightFoot
* * If Not foot="" Then foot = """" & foot & """"

* * pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
* * * LeftMarginInches & c & RightMarginInches & c & _
* * * TopMarginInches & c & BottomMarginInches & c & _
* * * PrintHeadings & c & PrintGridlines & c & _
* * * CenterHorizontally & c & CenterVertically & c & _
* * * Orientation & c & PaperSize & c & Zoom & c & _
* * * FirstPageNumber & c & Order & c & BlackAndWhite & c & _
* * * PrintQuality & c & HeaderMarginInches & c & _
* * * FooterMarginInches & c & PrintComments & c & Draft & ")"
* * Application.ExecuteExcel4Macro pgSetup
* End Sub

Peter T

I forgot to mention that the Excel4Macro page.setup requires the
information to be in a specific format to work. It has to be formatted
with I think 17 commas separating all of the options that you see if
you opened the page setup dialog box manually


On Jan 27, 8:15*am, "Peter T" <peter_t@discussions wrote:
typo

Dim h As tHeaders

should have been -
Dim h As hHeaders

Might be others, I didn't test

Peter T

"Peter T" <peter_t@discussions wrote in message

...

I couldn't paste your code as I got "too many continuations". Following
might solve two problems at the same time

' top of module

Type hHeaders
LeftHead As String
CenterHead As String
RightHead As String
LeftFoot As String
CenterFoot As String
RightFoot As String
End Type

Sub abc()
Dim h As tHeaders

h.LeftFoot = Range("LftHdrTxt").Text
h.CenterHead = Range("CtrHdrTxt").Text
' etc

PageSetupXL4M h, next-arg, etc

End Sub

Public Sub PageSetupXL4M(h as hHeaders,

where tHds replaces all the optional strings now included in the 'Type'

you'll need to change things like

* * If LeftHead < "" Then head = "&L" & LeftHead
to
* *If h.LeftHead < "" Then head = "&L" & h.LeftHead

Maybe you could combine all those margin arg's in a 'Type'
Public Type mMargins
mLeftMarginInches as string
etc

Regards,
Peter T

"Breck" wrote in message
...
I know that this is probably a very simple issue but I can't find an
answer and everything that I have tried doesn't work. How can I modify
this code so I can either use Excel's "Range Names" ie LftHdrTxt which
refers to cell B11 on Sheet2, or ranges Sheet2.Range("B11"). Currently the
text LftHdrTxt is placed in the left header and CtrHdrTxt in the center
header etc. The quotes are required. The PageSetupXl4m defines LeftHead As
String with the following "Optional LeftHead As String," etc. I'm trying
to update the Headers and Footers on 75 sheets from values input by a user
in cells, on a single sheet, in an excel template. Because of different
page setup's on many of the sheets *ie portrait on some and landscape on
Others. Because of this I can't simply activate all of the sheets then
enter the desired information in page setup. I'm trying to modify code I
found on the McGimpsey & Associates website.
www.mcgimpsey.com/excel/udfs/pagesetup.html.

PageSetupXL4M LeftHead:="LftHdrTxt", CenterHead:="CtrHdrTxt",
RightHead:="RhtHdrTxt", *LeftFoot:="LftFtrTxt", CenterFoot:="CtrFtrTxt",
RightFoot:="RhtFtrTxt"

Here is the code for PageSetupXL4M

Public Sub PageSetupXL4M( _
* * * *Optional LeftHead As String, _
* * * *Optional CenterHead As String, _
* * * *Optional RightHead As String, _
* * * *Optional LeftFoot As String, _
* * * *Optional CenterFoot As String, _
* * * *Optional RightFoot As String, _
* * * *Optional LeftMarginInches As String, _
* * * *Optional RightMarginInches As String, _
* * * *Optional TopMarginInches As String, _
* * * *Optional BottomMarginInches As String, _
* * * *Optional HeaderMarginInches As String, _
* * * *Optional FooterMarginInches As String, _
* * * *Optional PrintHeadings As String, _
* * * *Optional PrintGridlines As String, _
* * * *Optional PrintComments As String, _
* * * *Optional PrintQuality As String, _
* * * *Optional CenterHorizontally As String, _
* * * *Optional CenterVertically As String, _
* * * *Optional Orientation As String, _
* * * *Optional Draft As String, _
* * * *Optional PaperSize As String, _
* * * *Optional FirstPageNumber As String, _
* * * *Optional Order As String, _
* * * *Optional BlackAndWhite As String, _
* * * *Optional Zoom As String)
* * 'based on a post by John Green in
* * 'microsoft.public.excel.programming
* * 'on 21 January 2001:
* * 'http://google.com/groups?selm=VA.00000b2f.0028c7e5%40mara9"
* * Const c As String = ","
* * Dim pgSetup As String
* * Dim head As String
* * Dim foot As String
* * If LeftHead < "" Then head = "&L" & LeftHead
* * If CenterHead < "" Then head = head & "&C" & CenterHead
* * If RightHead < "" Then head = head & "&R" & RightHead
* * If Not head="" Then head = """" & head & """"
* * If LeftFoot < "" Then foot = "&L" & LeftFoot
* * If CenterFoot < "" Then foot = foot & "&C" & CenterFoot
* * If RightFoot < "" Then foot = foot & "&R" & RightFoot
* * If Not foot="" Then foot = """" & foot & """"

* * pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
* * * LeftMarginInches & c & RightMarginInches & c & _
* * * TopMarginInches & c & BottomMarginInches & c & _
* * * PrintHeadings & c & PrintGridlines & c & _
* * * CenterHorizontally & c & CenterVertically & c & _
* * * Orientation & c & PaperSize & c & Zoom & c & _
* * * FirstPageNumber & c & Order & c & BlackAndWhite & c & _
* * * PrintQuality & c & HeaderMarginInches & c & _
* * * FooterMarginInches & c & PrintComments & c & Draft & ")"
* * Application.ExecuteExcel4Macro pgSetup
* End Sub

I think 17 commas

It depends which way and for what purpose you are using the XL4 Page.Setup
XL4 macro. I would have thought typically would take 20 arg's with 19 commas
(quick glance at Macrofun.hlp).

However many commas, that has nothing whatsoever to do with what I was
trying to suggest. Firstly how to get the value of a named cell then to use
"Type" to pass many values to the procedure all wrapped up in one argument.

Regards,
Peter T

"Breck" wrote in message
...
Peter T

I forgot to mention that the Excel4Macro page.setup requires the
information to be in a specific format to work. It has to be formatted
with I think 17 commas separating all of the options that you see if
you opened the page setup dialog box manually


On Jan 27, 8:15 am, "Peter T" <peter_t@discussions wrote:
typo

Dim h As tHeaders

should have been -
Dim h As hHeaders

Might be others, I didn't test

Peter T

"Peter T" <peter_t@discussions wrote in message

...

I couldn't paste your code as I got "too many continuations". Following
might solve two problems at the same time

' top of module

Type hHeaders
LeftHead As String
CenterHead As String
RightHead As String
LeftFoot As String
CenterFoot As String
RightFoot As String
End Type

Sub abc()
Dim h As tHeaders

h.LeftFoot = Range("LftHdrTxt").Text
h.CenterHead = Range("CtrHdrTxt").Text
' etc

PageSetupXL4M h, next-arg, etc

End Sub

Public Sub PageSetupXL4M(h as hHeaders,

where tHds replaces all the optional strings now included in the 'Type'

you'll need to change things like

If LeftHead < "" Then head = "&L" & LeftHead
to
If h.LeftHead < "" Then head = "&L" & h.LeftHead

Maybe you could combine all those margin arg's in a 'Type'
Public Type mMargins
mLeftMarginInches as string
etc

Regards,
Peter T

"Breck" wrote in message
...
I know that this is probably a very simple issue but I can't find an
answer and everything that I have tried doesn't work. How can I modify
this code so I can either use Excel's "Range Names" ie LftHdrTxt which
refers to cell B11 on Sheet2, or ranges Sheet2.Range("B11"). Currently
the
text LftHdrTxt is placed in the left header and CtrHdrTxt in the center
header etc. The quotes are required. The PageSetupXl4m defines LeftHead
As
String with the following "Optional LeftHead As String," etc. I'm trying
to update the Headers and Footers on 75 sheets from values input by a
user
in cells, on a single sheet, in an excel template. Because of different
page setup's on many of the sheets ie portrait on some and landscape on
Others. Because of this I can't simply activate all of the sheets then
enter the desired information in page setup. I'm trying to modify code I
found on the McGimpsey & Associates website.
www.mcgimpsey.com/excel/udfs/pagesetup.html.

PageSetupXL4M LeftHead:="LftHdrTxt", CenterHead:="CtrHdrTxt",
RightHead:="RhtHdrTxt", LeftFoot:="LftFtrTxt", CenterFoot:="CtrFtrTxt",
RightFoot:="RhtFtrTxt"

Here is the code for PageSetupXL4M

Public Sub PageSetupXL4M( _
Optional LeftHead As String, _
Optional CenterHead As String, _
Optional RightHead As String, _
Optional LeftFoot As String, _
Optional CenterFoot As String, _
Optional RightFoot As String, _
Optional LeftMarginInches As String, _
Optional RightMarginInches As String, _
Optional TopMarginInches As String, _
Optional BottomMarginInches As String, _
Optional HeaderMarginInches As String, _
Optional FooterMarginInches As String, _
Optional PrintHeadings As String, _
Optional PrintGridlines As String, _
Optional PrintComments As String, _
Optional PrintQuality As String, _
Optional CenterHorizontally As String, _
Optional CenterVertically As String, _
Optional Orientation As String, _
Optional Draft As String, _
Optional PaperSize As String, _
Optional FirstPageNumber As String, _
Optional Order As String, _
Optional BlackAndWhite As String, _
Optional Zoom As String)
'based on a post by John Green in
'microsoft.public.excel.programming
'on 21 January 2001:
'http://google.com/groups?selm=VA.00000b2f.0028c7e5%40mara9"
Const c As String = ","
Dim pgSetup As String
Dim head As String
Dim foot As String
If LeftHead < "" Then head = "&L" & LeftHead
If CenterHead < "" Then head = head & "&C" & CenterHead
If RightHead < "" Then head = head & "&R" & RightHead
If Not head="" Then head = """" & head & """"
If LeftFoot < "" Then foot = "&L" & LeftFoot
If CenterFoot < "" Then foot = foot & "&C" & CenterFoot
If RightFoot < "" Then foot = foot & "&R" & RightFoot
If Not foot="" Then foot = """" & foot & """"

pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
LeftMarginInches & c & RightMarginInches & c & _
TopMarginInches & c & BottomMarginInches & c & _
PrintHeadings & c & PrintGridlines & c & _
CenterHorizontally & c & CenterVertically & c & _
Orientation & c & PaperSize & c & Zoom & c & _
FirstPageNumber & c & Order & c & BlackAndWhite & c & _
PrintQuality & c & HeaderMarginInches & c & _
FooterMarginInches & c & PrintComments & c & Draft & ")"
Application.ExecuteExcel4Macro pgSetup
End Sub

I really appreciate your help. I didn't look at your code thoroughly
until I got home and started putting it together. Then I realized that
you had everything covered blending your new code with the original
code. I think that your count is right I just estimated.

This is what I have. I hope that I understood your instructions and
followed them correctly. I'm getting a "Compile Error" "Argument not
optional" at the end of the First Module on the line that begins
PAGESETUPXL4M2. Once I get this part working I try your suggestion
about the margin arg's.

Why did you suggest "Public Type mMargins" for the margin arg's but
not "Public type hHeaders" for the header/footer arg's. And is all of
the code supposed to be in the same module or does it not matter

First Module

Type hHeaders
LeftHead As String
CenterHead As String
RightHead As String
LeftFoot As String
CenterFoot As String
RightFoot As String
End Type

Sub Update_Header_Footers()
Dim h As hHeaders

h.LeftHead = Range("LftHdrTxt").Text
h.CenterHead = Range("CtrHdrTxt").Text
h.RightHead = Range("RhtHdrTxt").Text
h.LeftFoot = Range("LftFtrTxt").Text
h.CenterFoot = Range("CtrFtrTxt").Text
h.RightFoot = Range("RhtFtrTxt").Text

PageSetupXL4M2 h, LeftMarginInches:=1

End Sub

Second Module

Public Sub PageSetupXL4M2(h As hHeaders, _
tHds, _
Optional LeftMarginInches As String, Optional
RightMarginInches As String, _
Optional TopMarginInches As String, Optional
BottomMarginInches As String, _
Optional HeaderMarginInches As String, Optional
FooterMarginInches As String, _
Optional PrintHeadings As String, Optional PrintGridlines As
String, _
Optional PrintComments As String, _
Optional PrintQuality As String, _
Optional CenterHorizontally As String, Optional
CenterVertically As String, _
Optional Orientation As String, _
Optional Draft As String, _
Optional PaperSize As String, _
Optional FirstPageNumber As String, _
Optional Order As String, _
Optional BlackAndWhite As String, _
Optional Zoom As String)
'based on a post by John Green in
'microsoft.public.excel.programming
'on 21 January 2001:
'http://google.com/groups?selm=VA.00000b2f.0028c7e5%40mara9"
Const c As String = ","
Dim pgSetup As String
Dim head As String
Dim foot As String
If LeftHead < "" Then head = "&L" & h.LeftHead
If CenterHead < "" Then head = head & "&C" & h.CenterHead
If RightHead < "" Then head = head & "&R" & h.RightHead
If Not head = "" Then head = """" & head & """"
If LeftFoot < "" Then foot = "&L" & h.LeftFoot
If CenterFoot < "" Then foot = foot & "&C" & h.CenterFoot
If RightFoot < "" Then foot = foot & "&R" & h.RightFoot
If Not foot = "" Then foot = """" & foot & """"

pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
LeftMarginInches & c & RightMarginInches & c & _
TopMarginInches & c & BottomMarginInches & c & _
PrintHeadings & c & PrintGridlines & c & _
CenterHorizontally & c & CenterVertically & c & _
Orientation & c & PaperSize & c & Zoom & c & _
FirstPageNumber & c & Order & c & BlackAndWhite & c & _
PrintQuality & c & HeaderMarginInches & c & _
FooterMarginInches & c & PrintComments & c & Draft & ")"
Application.ExecuteExcel4Macro pgSetup
End Sub



--------------------------------------------------
From: "Peter T" <peter_t@discussions
Sent: Tuesday, January 27, 2009 2:00 PM
Newsgroups: microsoft.public.excel.programming
Subject: Using range names or ranges between quotes

I think 17 commas

It depends which way and for what purpose you are using the XL4 Page.Setup
XL4 macro. I would have thought typically would take 20 arg's with 19 commas
(quick glance at Macrofun.hlp).

However many commas, that has nothing whatsoever to do with what I was
trying to suggest. Firstly how to get the value of a named cell then to use
"Type" to pass many values to the procedure all wrapped up in one argument.