|
Using range names or ranges between quotes
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 |
Using range names or ranges between quotes
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 |
Using range names or ranges between quotes
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 |
Using range names or ranges between quotes
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 |
Using range names or ranges between quotes
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 |
Using range names or ranges between quotes
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 |
Using range names or ranges between quotes
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 |
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. 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 |
Using range names or ranges between quotes
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. |
Using range names or ranges between quotes
I just realized that I should have used PageSetupXL4M2 h,
LeftMarginInches:="1" instead of PageSetupXL4M2 h, LeftMarginInches:=1 Now I'm getting the error "Constant expression required" on the pgSetup = line where the "c" is highlighted that is defined as Const c As String = "," On Jan 27, 8:06*pm, Breck wrote: 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. 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 On Jan 27, 2:00*pm, "Peter T" <peter_t@discussions wrote: 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 ... read more » |
Using range names or ranges between quotes
I just replaced all of the "& c &" with "& "," &" and the code ran
without error but didn't update the headers either. I steped through the code the "tHds", "lefthead", "CenterHead" etc are all empty. They are not being filled. the Leftmargin On Jan 27, 8:33*pm, Breck wrote: I just realized that I should have used PageSetupXL4M2 h, LeftMarginInches:="1" instead of PageSetupXL4M2 h, LeftMarginInches:=1 Now I'm getting the error "Constant expression required" on the pgSetup = line where the "c" is highlighted that is defined as Const c As String = "," On Jan 27, 8:06*pm, Breck wrote: 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. 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 & _ ... read more » |
Using range names or ranges between quotes
I just discovered that I also left off the h. after the "If" and
changed the "tHds" to "hHeader" in the second module. I fell so dumb it's working great now. Thank you very very much. You have been so helpful and I appreciate the time that you took to help me out. Now is there any reason that I can't add additional code to run PageSetupXL4M2 h, next-arg, etc on every sheet in the workbook. On Jan 27, 8:33*pm, Breck wrote: I just realized that I should have used PageSetupXL4M2 h, LeftMarginInches:="1" instead of PageSetupXL4M2 h, LeftMarginInches:=1 Now I'm getting the error "Constant expression required" on the pgSetup = line where the "c" is highlighted that is defined as Const c As String = "," On Jan 27, 8:06*pm, Breck wrote: 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. 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 & _ ... read more » |
Using range names or ranges between quotes
Glad you got it working. Afraid I gave a hurried explanation to cover quite
a lot, with a typo or two might have thrown you off track. To run the code on every sheet I think the sheet will need to be active first. Normally that's not necessary but I suspect with the XL4 macro that'll be required (I haven't looked into it) Now that you are into using 'Type' why not make one or two more, such as a Type to combine all the 'margin' arg's Sub myPgSetup() Dim h As hHeaders, m as mMargins Dim ws as Worksheet h.LeftHead = Range("LftHdrTxt").Text h.etc = m.LeftMarginInches = cStr(number) m.etc = For each ws in Activeworkbook.Worksheets ws.Activate ' could change some arg's depending on the sheet ' If ws.Name = "abc" or ws.Name = "def" Then ' m.LeftMarginInches = "1.2" ' Else m.LeftMarginInches = "2.3" ' End If PageSetupXL4M2 h, m, nextArg, etc Next Regards, Peter T "Breck" wrote in message ... I just discovered that I also left off the h. after the "If" and changed the "tHds" to "hHeader" in the second module. I fell so dumb it's working great now. Thank you very very much. You have been so helpful and I appreciate the time that you took to help me out. Now is there any reason that I can't add additional code to run PageSetupXL4M2 h, next-arg, etc on every sheet in the workbook. On Jan 27, 8:33 pm, Breck wrote: I just realized that I should have used PageSetupXL4M2 h, LeftMarginInches:="1" instead of PageSetupXL4M2 h, LeftMarginInches:=1 Now I'm getting the error "Constant expression required" on the pgSetup = line where the "c" is highlighted that is defined as Const c As String = "," On Jan 27, 8:06 pm, Breck wrote: 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. 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 & _ ... read more » |
Using range names or ranges between quotes
Here is what I have now. The margin settings are not working. As I
step thru the code using debug the "m" has all of the proper margin numbers in the value. but the "TopMarginInches" etc all have "" in the value. Also the hHeaders and mMargins both have "missing" in the value and "Variant/Error" in the type. the c has "Application-defined or object-defined error" in the value but i'm not using that one now anyway. What am I missing? Also if I create the name "SheetName" or something similar for every sheet but define scope as local instead of workbook would I be able to add that the some of the head header def's and have it add a unique sheet name to the header? Public Type hHeaders LeftHead As String CenterHead As String RightHead As String LeftFoot As String CenterFoot As String RightFoot As String End Type Public Type mMargins LeftMarginInches As String RightMarginInches As String TopMarginInches As String BottomMarginInches As String HeaderMarginInches As String FooterMarginInches As String End Type Sub myPgSetup() Dim h As hHeaders, m As mMargins Dim ws As Worksheet h.LeftHead = Range("LftHdrFnt").Text & Range("LftHdrTxt").Text h.CenterHead = Range("CtrHdrFnt").Text & Range("CtrHdrTxt").Text h.RightHead = Range("RhtHdrFnt").Text & Range("RhtHdrTxt").Text h.LeftFoot = Range("LftFtrFnt").Text & Range("LftFtrTxt").Text h.CenterFoot = Range("LftFtrFnt").Text & Range("CtrFtrTxt").Text h.RightFoot = Range("LftFtrFnt").Text & Range("RhtFtrTxt").Text m.LeftMarginInches = Range("LftMrgn").Value m.RightMarginInches = Range("RhtMrgn").Value m.TopMarginInches = Range("TpMrgn").Value m.BottomMarginInches = Range("BtmMrgn").Value m.HeaderMarginInches = Range("HdrMrgn").Value m.FooterMarginInches = Range("FtrMrgn").Value PageSetupXL4M2 h, m End Sub Public Sub PageSetupXL4M2(h As hHeaders, m As mMargins, _ Optional hHeaders, Optional mMargins, _ 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 h.LeftHead < "" Then head = "&L" & h.LeftHead If h.CenterHead < "" Then head = head & "&C" & h.CenterHead If h.RightHead < "" Then head = head & "&R" & h.RightHead If Not head = "" Then head = """" & head & """" If h.LeftFoot < "" Then foot = "&L" & h.LeftFoot If h.CenterFoot < "" Then foot = foot & "&C" & h.CenterFoot If h.RightFoot < "" Then foot = foot & "&R" & h.RightFoot If Not foot = "" Then foot = """" & foot & """" pgSetup = "PAGE.SETUP(" & head & "," & foot & "," & _ m.LeftMarginInches & "," & m.RightMarginInches & "," & _ m.TopMarginInches & "," & m.BottomMarginInches & "," & _ PrintHeadings & "," & PrintGridlines & "," & _ CenterHorizontally & "," & CenterVertically & "," & _ Orientation & "," & PaperSize & "," & Zoom & "," & _ FirstPageNumber & "," & Order & "," & BlackAndWhite & "," & _ PrintQuality & "," & m.HeaderMarginInches & "," & _ m.FooterMarginInches & "," & PrintComments & "," & Draft & ")" Application.ExecuteExcel4Macro pgSetup End Sub On Jan 28, 1:25*am, "Peter T" <peter_t@discussions wrote: Glad you got it working. Afraid I gave a hurried explanation to cover quite a lot, with a typo or two might have thrown you off track. To run the code on every sheet I think the sheet will need to be active first. Normally that's not necessary but I suspect with the XL4 macro that'll be required (I haven't looked into it) Now that you are into using 'Type' why not make one or two more, such as a Type to combine all the 'margin' arg's Sub myPgSetup() Dim h As hHeaders, m as mMargins Dim ws as Worksheet h.LeftHead = Range("LftHdrTxt").Text h.etc = m.LeftMarginInches = cStr(number) m.etc = For each ws in Activeworkbook.Worksheets ws.Activate ' could change some arg's depending on the sheet ' If ws.Name = "abc" or ws.Name = "def" Then ' m.LeftMarginInches = "1.2" ' Else m.LeftMarginInches = "2.3" ' End If PageSetupXL4M2 h, m, nextArg, etc Next Regards, Peter T "Breck" wrote in message ... I just discovered that I also left off the h. after the "If" and changed the "tHds" to "hHeader" in the second module. I fell so dumb it's working great now. Thank you very very much. You have been so helpful and I appreciate the time that you took to help me out. Now is there any reason that I can't add additional code to run PageSetupXL4M2 h, next-arg, etc on every sheet in the workbook. On Jan 27, 8:33 pm, Breck wrote: I just realized that I should have used PageSetupXL4M2 h, LeftMarginInches:="1" instead of PageSetupXL4M2 h, LeftMarginInches:=1 Now I'm getting the error "Constant expression required" on the pgSetup = line where the "c" is highlighted that is defined as Const c As String = "," On Jan 27, 8:06 pm, Breck wrote: 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. 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 ... read more » |
Using range names or ranges between quotes
Not sure why it isn't working for you but looks like you can remove quite a
few more of those optional arg's which are now replaced with the two type arguments, 'h' & 'm' Also, leave in PageSetupXL4M2( h As hHeaders, m As mMargins, but take out Optional hHeaders, Optional mMargins, as well as the other now redundant arg's Following seems to work fine for me - Option Explicit Public Type hHeaders LeftHead As String CenterHead As String RightHead As String LeftFoot As String CenterFoot As String RightFoot As String End Type Public Type mMargins LeftMarginInches As String RightMarginInches As String TopMarginInches As String BottomMarginInches As String HeaderMarginInches As String FooterMarginInches As String End Type Sub myPgSetup() Dim h As hHeaders, m As mMargins Dim ws As Worksheet Const cm As Single = 1 / 2.5 h.LeftHead = "LftHdrFnt + LftHdrTxt" h.CenterHead = "CtrHdrFnt + CtrHdrTxt" h.RightHead = "RhtHdrFnt + RhtHdrTxt" h.LeftFoot = "LftFtrFnt + ftFtrTxt" h.CenterFoot = "LftFtrFnt + CtrFtrTxt" h.RightFoot = "LftFtrFnt + RhtFtrTxt" If Application.International(xlMetric) Then m.LeftMarginInches = Round(2.2 * cm, 2) m.RightMarginInches = Round(2.4 * cm, 2) m.TopMarginInches = Round(2.6 * cm, 2) m.BottomMarginInches = Round(2.8 * cm, 2) m.HeaderMarginInches = Round(1.3 * cm, 2) m.FooterMarginInches = Round(1.7 * cm, 2) Else m.LeftMarginInches = 1.2 m.RightMarginInches = 1.4 m.TopMarginInches = 1.6 m.BottomMarginInches = 1.8 m.HeaderMarginInches = 0.7 m.FooterMarginInches = 0.9 End If For Each ws In ActiveWorkbook.Worksheets ws.Activate PageSetupXL4M2 h, m Next End Sub Public Sub PageSetupXL4M2(h As hHeaders, m As mMargins, _ 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 h.LeftHead < "" Then head = "&L" & h.LeftHead If h.CenterHead < "" Then head = head & "&C" & h.CenterHead If h.RightHead < "" Then head = head & "&R" & h.RightHead If Not head = "" Then head = """" & head & """" If h.LeftFoot < "" Then foot = "&L" & h.LeftFoot If h.CenterFoot < "" Then foot = foot & "&C" & h.CenterFoot If h.RightFoot < "" Then foot = foot & "&R" & h.RightFoot If Not foot = "" Then foot = """" & foot & """" pgSetup = "PAGE.SETUP(" & head & "," & foot & "," & _ m.LeftMarginInches & "," & m.RightMarginInches & "," & _ m.TopMarginInches & "," & m.BottomMarginInches & "," & _ PrintHeadings & "," & PrintGridlines & "," & _ CenterHorizontally & "," & CenterVertically & "," & _ Orientation & "," & PaperSize & "," & Zoom & "," & _ FirstPageNumber & "," & Order & "," & BlackAndWhite & "," & _ PrintQuality & "," & m.HeaderMarginInches & "," & _ m.FooterMarginInches & "," & PrintComments & "," & Draft & ")" Application.ExecuteExcel4Macro pgSetup End Sub Obviously those inches & cms values are not equivalent, only like that to make it easy to validate correct values are applied depending on your regional settings. I didn't bother recreating your named ranges to get the values, just the hard coded strings for testing, but that's another matter. Regards, Peter T |
Using range names or ranges between quotes
Success at last I can't believe how good its working it takes just 65
seconds to update the page settings of all 75 pages. Since you weren't having problem I made 2 changes. I changed the format of the cell that had the margin settings to "general" then I changes all of the m.margin setting from .value to .text ran the macro and it ran perfectly. Well almost perfectly. I introduced another twist I added more to the CenterHead line h.CenterHead = Range("CtrHdrFnt").Text & Range("CtrHdrTxt").Text & Chr (10) & Range("CtrHdrPNFnt").Text & Range("ShtNm").Text where ShtNm is a range name on each sheet that has the scope set as the individual sheet instead of the usual workbook just like the "Print_Area" setting are sheet specific. the macro used the first sheets "ShtNm" instead of each sheets "ShtNm" I also turned off events so the Workbook_Activate wouldn't execute on the sheets that used Workbook_Activate to trigger additional code. Wasn't sure where the best place for it would be though. For Each ws In ActiveWorkbook.Worksheets Application.EnableEvents = False ws.Activate PageSetupXL4M2 h, m Application.EnableEvents = True Next I thought that the ws.Activate would allow this to work since you can watch the macro cycle through all of the sheets in the workbook. Not that the rest of the code is working so well I get to figure this one out now. Most the the "ShtNm" refer to cells A1 on that sheet but there are 15 sheets that refer to C1. The sheets with C1 can be changed if necessary to A1 but I'm not sure yet if that will help. I can't thank you enough for your help. You have been so kind. I'm creating a volleyball stats program gratis for the girls Intermountain Volleyball Association. They will be offering the workbook to volleyball clubs and high school coaches in the region for a small fee that goes into a scholarship fund. We are hoping that it will not only help some girls but also help improve the play of all of the girls by tracking the individual and team statistics. On Jan 28, 8:41*am, "Peter T" <peter_t@discussions wrote: Not sure why it isn't working for you but looks like you can remove quite a few more of those optional arg's which are now replaced with the two type arguments, 'h' & 'm' Also, leave in PageSetupXL4M2( * * *h As hHeaders, m As mMargins, but take out * * Optional hHeaders, Optional mMargins, as well as the other now redundant arg's Following seems to work fine for me - Option Explicit Public Type hHeaders * * LeftHead As String * * CenterHead As String * * RightHead As String * * LeftFoot As String * * CenterFoot As String * * RightFoot As String End Type Public Type mMargins * * LeftMarginInches As String * * RightMarginInches As String * * TopMarginInches As String * * BottomMarginInches As String * * HeaderMarginInches As String * * FooterMarginInches As String End Type Sub myPgSetup() Dim h As hHeaders, m As mMargins Dim ws As Worksheet Const cm As Single = 1 / 2.5 * * h.LeftHead = "LftHdrFnt + LftHdrTxt" * * h.CenterHead = "CtrHdrFnt + CtrHdrTxt" * * h.RightHead = "RhtHdrFnt + RhtHdrTxt" * * h.LeftFoot = "LftFtrFnt + ftFtrTxt" * * h.CenterFoot = "LftFtrFnt + CtrFtrTxt" * * h.RightFoot = "LftFtrFnt + RhtFtrTxt" * * If Application.International(xlMetric) Then * * * * m.LeftMarginInches = Round(2.2 * cm, 2) * * * * m.RightMarginInches = Round(2.4 * cm, 2) * * * * m.TopMarginInches = Round(2.6 * cm, 2) * * * * m.BottomMarginInches = Round(2.8 * cm, 2) * * * * m.HeaderMarginInches = Round(1.3 * cm, 2) * * * * m.FooterMarginInches = Round(1.7 * cm, 2) * * Else * * * * m.LeftMarginInches = 1.2 * * * * m.RightMarginInches = 1.4 * * * * m.TopMarginInches = 1.6 * * * * m.BottomMarginInches = 1.8 * * * * m.HeaderMarginInches = 0.7 * * * * m.FooterMarginInches = 0.9 * * End If * * For Each ws In ActiveWorkbook.Worksheets * * * * ws.Activate * * * * PageSetupXL4M2 h, m * * Next End Sub Public Sub PageSetupXL4M2(h As hHeaders, m As mMargins, _ * * * * * * * * * * * * * 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 h.LeftHead < "" Then head = "&L" & h.LeftHead * * If h.CenterHead < "" Then head = head & "&C" & h.CenterHead * * If h.RightHead < "" Then head = head & "&R" & h.RightHead * * If Not head = "" Then head = """" & head & """" * * If h.LeftFoot < "" Then foot = "&L" & h.LeftFoot * * If h.CenterFoot < "" Then foot = foot & "&C" & h.CenterFoot * * If h.RightFoot < "" Then foot = foot & "&R" & h.RightFoot * * If Not foot = "" Then foot = """" & foot & """" * * pgSetup = "PAGE.SETUP(" & head & "," & foot & "," & _ * * * * * * * m.LeftMarginInches & "," & m.RightMarginInches & "," & _ * * * * * * * m.TopMarginInches & "," & m.BottomMarginInches & "," & _ * * * * * * * PrintHeadings & "," & PrintGridlines & "," & _ * * * * * * * CenterHorizontally & "," & CenterVertically & "," & _ * * * * * * * Orientation & "," & PaperSize & "," & Zoom & "," & _ * * * * * * * FirstPageNumber & "," & Order & "," & BlackAndWhite & "," & _ * * * * * * * PrintQuality & "," & m.HeaderMarginInches & "," & _ * * * * * * * m.FooterMarginInches & "," & PrintComments & "," & Draft & ")" * * Application.ExecuteExcel4Macro pgSetup End Sub Obviously those inches & cms values are not equivalent, only like that to make it easy to validate correct values are applied depending on your regional settings. I didn't bother recreating your named ranges to get the values, just the hard coded strings for testing, but that's another matter. |
Using range names or ranges between quotes
sorry type Not that the rest of the code is working so well
should be Now that the rest of the code is working so well On Jan 28, 1:32*pm, Breck wrote: Success at last I can't believe how good its working it takes just 65 seconds to update the page settings of all 75 pages. Since you weren't having problem I made 2 changes. I changed the format of the cell that had the margin settings to "general" then I changes all of the m.margin setting from .value to .text ran the macro and it ran perfectly. Well almost perfectly. I introduced another twist I added more to the CenterHead line h.CenterHead = Range("CtrHdrFnt").Text & Range("CtrHdrTxt").Text & Chr (10) & Range("CtrHdrPNFnt").Text & Range("ShtNm").Text where ShtNm is a range name on each sheet that has the scope set as the individual sheet instead of the usual workbook just like the "Print_Area" setting are sheet specific. the macro used the first sheets "ShtNm" instead of each sheets "ShtNm" I also turned off events so the Workbook_Activate wouldn't execute on the sheets that used Workbook_Activate to trigger additional code. Wasn't sure where the best place for it would be though. For Each ws In ActiveWorkbook.Worksheets * * Application.EnableEvents = False * * ws.Activate * * PageSetupXL4M2 h, m * * Application.EnableEvents = True Next *I thought that the ws.Activate would allow this to work since you can watch the macro cycle through all of the sheets in the workbook. Not that the rest of the code is working so well I get to figure this one out now. Most the the "ShtNm" refer to cells A1 on that sheet but there are 15 sheets that refer to C1. The sheets with C1 can be changed if necessary to A1 but I'm not sure yet if that will help. I can't thank you enough for your help. You have been so kind. I'm creating a volleyball stats program gratis for the girls Intermountain Volleyball Association. They will be offering the workbook to volleyball clubs and high school coaches in the region for a small fee that goes into a scholarship fund. We are hoping that it will not only help some girls but also help improve the play of all of the girls by tracking the individual and team statistics. On Jan 28, 8:41*am, "Peter T" <peter_t@discussions wrote: Not sure why it isn't working for you but looks like you can remove quite a few more of those optional arg's which are now replaced with the two type arguments, 'h' & 'm' Also, leave in PageSetupXL4M2( * * *h As hHeaders, m As mMargins, but take out * * Optional hHeaders, Optional mMargins, as well as the other now redundant arg's Following seems to work fine for me - Option Explicit Public Type hHeaders * * LeftHead As String * * CenterHead As String * * RightHead As String * * LeftFoot As String * * CenterFoot As String * * RightFoot As String End Type Public Type mMargins * * LeftMarginInches As String * * RightMarginInches As String * * TopMarginInches As String * * BottomMarginInches As String * * HeaderMarginInches As String * * FooterMarginInches As String End Type Sub myPgSetup() Dim h As hHeaders, m As mMargins Dim ws As Worksheet Const cm As Single = 1 / 2.5 * * h.LeftHead = "LftHdrFnt + LftHdrTxt" * * h.CenterHead = "CtrHdrFnt + CtrHdrTxt" * * h.RightHead = "RhtHdrFnt + RhtHdrTxt" * * h.LeftFoot = "LftFtrFnt + ftFtrTxt" * * h.CenterFoot = "LftFtrFnt + CtrFtrTxt" * * h.RightFoot = "LftFtrFnt + RhtFtrTxt" * * If Application.International(xlMetric) Then * * * * m.LeftMarginInches = Round(2.2 * cm, 2) * * * * m.RightMarginInches = Round(2.4 * cm, 2) * * * * m.TopMarginInches = Round(2.6 * cm, 2) * * * * m.BottomMarginInches = Round(2.8 * cm, 2) * * * * m.HeaderMarginInches = Round(1.3 * cm, 2) * * * * m.FooterMarginInches = Round(1.7 * cm, 2) * * Else * * * * m.LeftMarginInches = 1.2 * * * * m.RightMarginInches = 1.4 * * * * m.TopMarginInches = 1.6 * * * * m.BottomMarginInches = 1.8 * * * * m.HeaderMarginInches = 0.7 * * * * m.FooterMarginInches = 0.9 * * End If * * For Each ws In ActiveWorkbook.Worksheets * * * * ws.Activate * * * * PageSetupXL4M2 h, m * * Next End Sub Public Sub PageSetupXL4M2(h As hHeaders, m As mMargins, _ * * * * * * * * * * * * * 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 h.LeftHead < "" Then head = "&L" & h.LeftHead * * If h.CenterHead < "" Then head = head & "&C" & h.CenterHead * * If h.RightHead < "" Then head = head & "&R" & h.RightHead * * If Not head = "" Then head = """" & head & """" * * If h.LeftFoot < "" Then foot = "&L" & h.LeftFoot * * If h.CenterFoot < "" Then foot = foot & "&C" & h.CenterFoot * * If h.RightFoot < "" Then foot = foot & "&R" & h.RightFoot * * If Not foot = "" Then foot = """" & foot & """" * * pgSetup = "PAGE.SETUP(" & head & "," & foot & "," & _ * * * * * * * m.LeftMarginInches & "," & m.RightMarginInches & "," & _ * * * * * * * m.TopMarginInches & "," & m.BottomMarginInches & "," & _ * * * * * * * PrintHeadings & "," & PrintGridlines & "," & _ * * * * * * * CenterHorizontally & "," & CenterVertically & "," & _ * * * * * * * Orientation & "," & PaperSize & "," & Zoom & "," & _ * * * * * * * FirstPageNumber & "," & Order & "," & BlackAndWhite & "," & _ * * * * * * * PrintQuality & "," & m.HeaderMarginInches & "," & _ * * * * * * * m.FooterMarginInches & "," & PrintComments & "," & Draft & ")" * * Application.ExecuteExcel4Macro pgSetup End Sub Obviously those inches & cms values are not equivalent, only like that to make it easy to validate correct values are applied depending on your regional settings. I didn't bother recreating your named ranges to get the values, just the hard coded strings for testing, but that's another matter. Regards, Peter T |
Using range names or ranges between quotes
Sounds like you're well up and running. Just a few comments -
I introduced another twist I added more to the CenterHead line h.CenterHead = Range("CtrHdrFnt").Text & Range("CtrHdrTxt").Text & Chr (10) & Range("CtrHdrPNFnt").Text & Range("ShtNm").Text where ShtNm is a range name on each sheet that has the scope set as the individual sheet instead of the usual workbook As you know, Worksheet or 'local' level names allow to use the same name to define a range on each sheet, defined like 'SheetName'!theName ensure you are doing h.CenterHead = Range("CtrHdrFnt").Text etc only when the specific sheet is active. That means code like the above should be in the worksheet loop just after the relevant sheet has been activated. You could say, assign some of the 'Type' variables, which apply to all sheets before the loop, then specific sheet variables in the loop after activating the sheet. Not sure if you are doing that already, if not you should. I also turned off events so the Workbook_Activate wouldn't execute on the sheets that used Workbook_Activate to trigger additional code. Wasn't sure where the best place for it would be though. For Each ws In ActiveWorkbook.Worksheets Application.EnableEvents = False ws.Activate PageSetupXL4M2 h, m Application.EnableEvents = True Next No need to change EnableEvents in each loop Application.EnableEvents = False ws.Activate ' populate any variables that take data from worksheet level ' names here ' though the name would need to exist on each sheet PageSetupXL4M2 h, m Next Application.EnableEvents = True (Typically an EnableEvents would be reset after an error handler. But in your case probably best not, if something goes wrong you'll find out quickly and easily. Be sure to reset EnableEvents manually if necessary (ie if code fails to finish normally) They will be offering the workbook to volleyball clubs and high school coaches in the region for a small fee that goes into a scholarship fund. That's great :-) Regards, Peter T "Breck" wrote in message ... Success at last I can't believe how good its working it takes just 65 seconds to update the page settings of all 75 pages. Since you weren't having problem I made 2 changes. I changed the format of the cell that had the margin settings to "general" then I changes all of the m.margin setting from .value to .text ran the macro and it ran perfectly. Well almost perfectly. I introduced another twist I added more to the CenterHead line h.CenterHead = Range("CtrHdrFnt").Text & Range("CtrHdrTxt").Text & Chr (10) & Range("CtrHdrPNFnt").Text & Range("ShtNm").Text where ShtNm is a range name on each sheet that has the scope set as the individual sheet instead of the usual workbook just like the "Print_Area" setting are sheet specific. the macro used the first sheets "ShtNm" instead of each sheets "ShtNm" I also turned off events so the Workbook_Activate wouldn't execute on the sheets that used Workbook_Activate to trigger additional code. Wasn't sure where the best place for it would be though. For Each ws In ActiveWorkbook.Worksheets Application.EnableEvents = False ws.Activate PageSetupXL4M2 h, m Application.EnableEvents = True Next I thought that the ws.Activate would allow this to work since you can watch the macro cycle through all of the sheets in the workbook. Not that the rest of the code is working so well I get to figure this one out now. Most the the "ShtNm" refer to cells A1 on that sheet but there are 15 sheets that refer to C1. The sheets with C1 can be changed if necessary to A1 but I'm not sure yet if that will help. I can't thank you enough for your help. You have been so kind. I'm creating a volleyball stats program gratis for the girls Intermountain Volleyball Association. They will be offering the workbook to volleyball clubs and high school coaches in the region for a small fee that goes into a scholarship fund. We are hoping that it will not only help some girls but also help improve the play of all of the girls by tracking the individual and team statistics. On Jan 28, 8:41 am, "Peter T" <peter_t@discussions wrote: Not sure why it isn't working for you but looks like you can remove quite a few more of those optional arg's which are now replaced with the two type arguments, 'h' & 'm' Also, leave in PageSetupXL4M2( h As hHeaders, m As mMargins, but take out Optional hHeaders, Optional mMargins, as well as the other now redundant arg's Following seems to work fine for me - Option Explicit Public Type hHeaders LeftHead As String CenterHead As String RightHead As String LeftFoot As String CenterFoot As String RightFoot As String End Type Public Type mMargins LeftMarginInches As String RightMarginInches As String TopMarginInches As String BottomMarginInches As String HeaderMarginInches As String FooterMarginInches As String End Type Sub myPgSetup() Dim h As hHeaders, m As mMargins Dim ws As Worksheet Const cm As Single = 1 / 2.5 h.LeftHead = "LftHdrFnt + LftHdrTxt" h.CenterHead = "CtrHdrFnt + CtrHdrTxt" h.RightHead = "RhtHdrFnt + RhtHdrTxt" h.LeftFoot = "LftFtrFnt + ftFtrTxt" h.CenterFoot = "LftFtrFnt + CtrFtrTxt" h.RightFoot = "LftFtrFnt + RhtFtrTxt" If Application.International(xlMetric) Then m.LeftMarginInches = Round(2.2 * cm, 2) m.RightMarginInches = Round(2.4 * cm, 2) m.TopMarginInches = Round(2.6 * cm, 2) m.BottomMarginInches = Round(2.8 * cm, 2) m.HeaderMarginInches = Round(1.3 * cm, 2) m.FooterMarginInches = Round(1.7 * cm, 2) Else m.LeftMarginInches = 1.2 m.RightMarginInches = 1.4 m.TopMarginInches = 1.6 m.BottomMarginInches = 1.8 m.HeaderMarginInches = 0.7 m.FooterMarginInches = 0.9 End If For Each ws In ActiveWorkbook.Worksheets ws.Activate PageSetupXL4M2 h, m Next End Sub Public Sub PageSetupXL4M2(h As hHeaders, m As mMargins, _ 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 h.LeftHead < "" Then head = "&L" & h.LeftHead If h.CenterHead < "" Then head = head & "&C" & h.CenterHead If h.RightHead < "" Then head = head & "&R" & h.RightHead If Not head = "" Then head = """" & head & """" If h.LeftFoot < "" Then foot = "&L" & h.LeftFoot If h.CenterFoot < "" Then foot = foot & "&C" & h.CenterFoot If h.RightFoot < "" Then foot = foot & "&R" & h.RightFoot If Not foot = "" Then foot = """" & foot & """" pgSetup = "PAGE.SETUP(" & head & "," & foot & "," & _ m.LeftMarginInches & "," & m.RightMarginInches & "," & _ m.TopMarginInches & "," & m.BottomMarginInches & "," & _ PrintHeadings & "," & PrintGridlines & "," & _ CenterHorizontally & "," & CenterVertically & "," & _ Orientation & "," & PaperSize & "," & Zoom & "," & _ FirstPageNumber & "," & Order & "," & BlackAndWhite & "," & _ PrintQuality & "," & m.HeaderMarginInches & "," & _ m.FooterMarginInches & "," & PrintComments & "," & Draft & ")" Application.ExecuteExcel4Macro pgSetup End Sub Obviously those inches & cms values are not equivalent, only like that to make it easy to validate correct values are applied depending on your regional settings. I didn't bother recreating your named ranges to get the values, just the hard coded strings for testing, but that's another matter. Regards, Peter T |
Using range names or ranges between quotes
"Breck" wrote in message
sorry type Not that the rest of the code is working so well should be Now that the rest of the code is working so well Phew ! Peter T |
| All times are GMT +1. The time now is 11:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com