Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Headers & Footnotes from User Form
I am trying to get the Header/Footnote to update on all sheets in a Workbook
without changing the page formatting. Some of the Sheets are Portrait and some are landscape. I have a User Form that already has the data in some Text Boxes. There is a Control Button (Update_Engineer_Spec_10) that when pushed takes the information on the User Form and Updates the Workbook, but I would like to Update the Header / Footnot as well. The Header was supposed to look like this. Town: TEO No: Page 1 of ? Office: Supplier Order No: Appendix No: Town = City_1 Office = Office_1 TEO No = TEO_No_1 Supplier Order No = CES_No_1 Appendix No = TEO_Appx_No_2 Here is the code I have, but it dosen't seem to work. The truth is nothing happens. 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Headers & Footnotes from User Form
I believe y9ou have a VBA userform. "ME" is how you reference the Userform from within the code of the userform. If you are referencing the userform from module code you need to include the Userform name since there may be more than one userform in a workbook. So from Module Code Header Footnote Information Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town:" & UserForm1.City_1.Value & vbNewLine _ & "Office:" & Userfrom1.Office_1.Value .CenterHeader = "TEO No:" & Userfrom1.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Userform1.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Userform1.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165166 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Headers & Footnotes from User Form
Where is this code located? If its located in a Standard Module then you
need to use your Userform name like Userform1 as a reference instead of Me. I would also recommend you use the For Each...Next Loop to loop thru your worksheets instead for the For...Next Loop. For Each is used mainly for objects. So your code would look like this: 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Worksheet For Each sh In WorkSheets With sh.PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub Hope this helps! If so, click "YES" below. -- Cheers, Ryan "Brian" wrote: I am trying to get the Header/Footnote to update on all sheets in a Workbook without changing the page formatting. Some of the Sheets are Portrait and some are landscape. I have a User Form that already has the data in some Text Boxes. There is a Control Button (Update_Engineer_Spec_10) that when pushed takes the information on the User Form and Updates the Workbook, but I would like to Update the Header / Footnot as well. The Header was supposed to look like this. Town: TEO No: Page 1 of ? Office: Supplier Order No: Appendix No: Town = City_1 Office = Office_1 TEO No = TEO_No_1 Supplier Order No = CES_No_1 Appendix No = TEO_Appx_No_2 Here is the code I have, but it dosen't seem to work. The truth is nothing happens. 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Headers & Footnotes from User Form
The Code is Located under the "Private Sub Update_Engineer_Spec_10_Click()"
This is a control Button for update the Workbook. I put the cade a the end of that code on the user form. Ok, I did reference the Text Boxes Correctly, like "Me.City_1.Value". "Me" = The User Form "City_1" = The Text Box in the User Form "Value" = A varible Input from user 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub I am still learning this VBA Stuff. "Ryan H" wrote: Where is this code located? If its located in a Standard Module then you need to use your Userform name like Userform1 as a reference instead of Me. I would also recommend you use the For Each...Next Loop to loop thru your worksheets instead for the For...Next Loop. For Each is used mainly for objects. So your code would look like this: 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Worksheet For Each sh In WorkSheets With sh.PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub Hope this helps! If so, click "YES" below. -- Cheers, Ryan "Brian" wrote: I am trying to get the Header/Footnote to update on all sheets in a Workbook without changing the page formatting. Some of the Sheets are Portrait and some are landscape. I have a User Form that already has the data in some Text Boxes. There is a Control Button (Update_Engineer_Spec_10) that when pushed takes the information on the User Form and Updates the Workbook, but I would like to Update the Header / Footnot as well. The Header was supposed to look like this. Town: TEO No: Page 1 of ? Office: Supplier Order No: Appendix No: Town = City_1 Office = Office_1 TEO No = TEO_No_1 Supplier Order No = CES_No_1 Appendix No = TEO_Appx_No_2 Here is the code I have, but it dosen't seem to work. The truth is nothing happens. 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Headers & Footnotes from User Form
Brian, you cannot put a Sub within a Sub. It will not work.
Private Sub Update_Engineer_Spec_10_Click() 'Update Header Footnote Information Sub DynamicHeader() '<<<This line will cause an error 'because you already have a title 'line for the click event. Comment the second title line out and see if it runs. "Brian" wrote in message ... The Code is Located under the "Private Sub Update_Engineer_Spec_10_Click()" This is a control Button for update the Workbook. I put the cade a the end of that code on the user form. Ok, I did reference the Text Boxes Correctly, like "Me.City_1.Value". "Me" = The User Form "City_1" = The Text Box in the User Form "Value" = A varible Input from user 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub I am still learning this VBA Stuff. "Ryan H" wrote: Where is this code located? If its located in a Standard Module then you need to use your Userform name like Userform1 as a reference instead of Me. I would also recommend you use the For Each...Next Loop to loop thru your worksheets instead for the For...Next Loop. For Each is used mainly for objects. So your code would look like this: 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Worksheet For Each sh In WorkSheets With sh.PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub Hope this helps! If so, click "YES" below. -- Cheers, Ryan "Brian" wrote: I am trying to get the Header/Footnote to update on all sheets in a Workbook without changing the page formatting. Some of the Sheets are Portrait and some are landscape. I have a User Form that already has the data in some Text Boxes. There is a Control Button (Update_Engineer_Spec_10) that when pushed takes the information on the User Form and Updates the Workbook, but I would like to Update the Header / Footnot as well. The Header was supposed to look like this. Town: TEO No: Page 1 of ? Office: Supplier Order No: Appendix No: Town = City_1 Office = Office_1 TEO No = TEO_No_1 Supplier Order No = CES_No_1 Appendix No = TEO_Appx_No_2 Here is the code I have, but it dosen't seem to work. The truth is nothing happens. 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Headers & Footnotes from User Form
First, "ME" is a reserved object name and you can't use it to refer to other items. Yo are trying to assin MER to you user form which isn't correct. Second, when you asign a variable to an oobject you have to use "SET" like this Set Sht = activeworksheet or Set sht = sheets("Sheet1") This will not work Sht = activeworksheet -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165166 Microsoft Office Help |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Headers & Footnotes from User Form
It worked, the only thing is that I need it to be 8 Point Arial.
Also it reset all the page settings for the Worksheet. The Header and Footnote are correct, but the Body of the Sheet (Table) moved up till 1/2 the header is inside the header. "JLGWhiz" wrote: Brian, you cannot put a Sub within a Sub. It will not work. Private Sub Update_Engineer_Spec_10_Click() 'Update Header Footnote Information Sub DynamicHeader() '<<<This line will cause an error 'because you already have a title 'line for the click event. Comment the second title line out and see if it runs. "Brian" wrote in message ... The Code is Located under the "Private Sub Update_Engineer_Spec_10_Click()" This is a control Button for update the Workbook. I put the cade a the end of that code on the user form. Ok, I did reference the Text Boxes Correctly, like "Me.City_1.Value". "Me" = The User Form "City_1" = The Text Box in the User Form "Value" = A varible Input from user 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub I am still learning this VBA Stuff. "Ryan H" wrote: Where is this code located? If its located in a Standard Module then you need to use your Userform name like Userform1 as a reference instead of Me. I would also recommend you use the For Each...Next Loop to loop thru your worksheets instead for the For...Next Loop. For Each is used mainly for objects. So your code would look like this: 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Worksheet For Each sh In WorkSheets With sh.PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub Hope this helps! If so, click "YES" below. -- Cheers, Ryan "Brian" wrote: I am trying to get the Header/Footnote to update on all sheets in a Workbook without changing the page formatting. Some of the Sheets are Portrait and some are landscape. I have a User Form that already has the data in some Text Boxes. There is a Control Button (Update_Engineer_Spec_10) that when pushed takes the information on the User Form and Updates the Workbook, but I would like to Update the Header / Footnot as well. The Header was supposed to look like this. Town: TEO No: Page 1 of ? Office: Supplier Order No: Appendix No: Town = City_1 Office = Office_1 TEO No = TEO_No_1 Supplier Order No = CES_No_1 Appendix No = TEO_Appx_No_2 Here is the code I have, but it dosen't seem to work. The truth is nothing happens. 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub Thanks . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Headers & Footnotes from User Form
I got it to work perfect, except for the Line spacing.
Town: TEO No: Page 1 of ? Office: Supplier Order No: Appendix No: It needs to look like this: Town: TEO No: Page 1 of ? Office: Supplier Order No: Appendix No: I recorded 2 macro's one with the spacing and 1 removing the spacing. Then I printed them and compared them to see what was different. They are both exactly same. I need to get rid of that extra line spacing. "JLGWhiz" wrote: Brian, you cannot put a Sub within a Sub. It will not work. Private Sub Update_Engineer_Spec_10_Click() 'Update Header Footnote Information Sub DynamicHeader() '<<<This line will cause an error 'because you already have a title 'line for the click event. Comment the second title line out and see if it runs. "Brian" wrote in message ... The Code is Located under the "Private Sub Update_Engineer_Spec_10_Click()" This is a control Button for update the Workbook. I put the cade a the end of that code on the user form. Ok, I did reference the Text Boxes Correctly, like "Me.City_1.Value". "Me" = The User Form "City_1" = The Text Box in the User Form "Value" = A varible Input from user 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub I am still learning this VBA Stuff. "Ryan H" wrote: Where is this code located? If its located in a Standard Module then you need to use your Userform name like Userform1 as a reference instead of Me. I would also recommend you use the For Each...Next Loop to loop thru your worksheets instead for the For...Next Loop. For Each is used mainly for objects. So your code would look like this: 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Worksheet For Each sh In WorkSheets With sh.PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub Hope this helps! If so, click "YES" below. -- Cheers, Ryan "Brian" wrote: I am trying to get the Header/Footnote to update on all sheets in a Workbook without changing the page formatting. Some of the Sheets are Portrait and some are landscape. I have a User Form that already has the data in some Text Boxes. There is a Control Button (Update_Engineer_Spec_10) that when pushed takes the information on the User Form and Updates the Workbook, but I would like to Update the Header / Footnot as well. The Header was supposed to look like this. Town: TEO No: Page 1 of ? Office: Supplier Order No: Appendix No: Town = City_1 Office = Office_1 TEO No = TEO_No_1 Supplier Order No = CES_No_1 Appendix No = TEO_Appx_No_2 Here is the code I have, but it dosen't seem to work. The truth is nothing happens. 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub Thanks . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Headers & Footnotes from User Form
Try adjusting the numbers for where the header and footer and page "top" and "bottom" positions are. On Sat, 26 Dec 2009 11:56:01 -0800, Brian wrote: I got it to work perfect, except for the Line spacing. Town: TEO No: Page 1 of ? Office: Supplier Order No: Appendix No: It needs to look like this: Town: TEO No: Page 1 of ? Office: Supplier Order No: Appendix No: I recorded 2 macro's one with the spacing and 1 removing the spacing. Then I printed them and compared them to see what was different. They are both exactly same. I need to get rid of that extra line spacing. "JLGWhiz" wrote: Brian, you cannot put a Sub within a Sub. It will not work. Private Sub Update_Engineer_Spec_10_Click() 'Update Header Footnote Information Sub DynamicHeader() '<<<This line will cause an error 'because you already have a title 'line for the click event. Comment the second title line out and see if it runs. "Brian" wrote in message ... The Code is Located under the "Private Sub Update_Engineer_Spec_10_Click()" This is a control Button for update the Workbook. I put the cade a the end of that code on the user form. Ok, I did reference the Text Boxes Correctly, like "Me.City_1.Value". "Me" = The User Form "City_1" = The Text Box in the User Form "Value" = A varible Input from user 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub I am still learning this VBA Stuff. "Ryan H" wrote: Where is this code located? If its located in a Standard Module then you need to use your Userform name like Userform1 as a reference instead of Me. I would also recommend you use the For Each...Next Loop to loop thru your worksheets instead for the For...Next Loop. For Each is used mainly for objects. So your code would look like this: 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Worksheet For Each sh In WorkSheets With sh.PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub Hope this helps! If so, click "YES" below. -- Cheers, Ryan "Brian" wrote: I am trying to get the Header/Footnote to update on all sheets in a Workbook without changing the page formatting. Some of the Sheets are Portrait and some are landscape. I have a User Form that already has the data in some Text Boxes. There is a Control Button (Update_Engineer_Spec_10) that when pushed takes the information on the User Form and Updates the Workbook, but I would like to Update the Header / Footnot as well. The Header was supposed to look like this. Town: TEO No: Page 1 of ? Office: Supplier Order No: Appendix No: Town = City_1 Office = Office_1 TEO No = TEO_No_1 Supplier Order No = CES_No_1 Appendix No = TEO_Appx_No_2 Here is the code I have, but it dosen't seem to work. The truth is nothing happens. 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub Thanks . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Headers & Footnotes from User Form
Here is the code that works, except for the dbl line spacing. I can go in and
manualy remove it under page properties, but not with code for some reason. I have checked and rechecked the code, still no luck. Any Ideas? 'Update Header Footnote Information Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "&8Town: " & Me.City_1.Value & vbNewLine _ & "Office: " & Me.Office_1.Value .CenterHeader = "&8TEO No: " & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No: " & Me.CES_No_1.Value .RightHeader = "&8Page &P of &N" & vbNewLine _ & "Appendix No :" & Me.TEO_Appx_No_2.Value .CenterFooter = "&8RESTRICTED - PROPRIETARY INFORMATION" & vbNewLine _ & "Not for Use or Disclosure outside AT&T except under Written Agreement" .LeftFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.55) .BottomMargin = Application.InchesToPoints(0.7) .HeaderMargin = Application.InchesToPoints(0.25) .FooterMargin = Application.InchesToPoints(0.25) .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True End With Next sh End Sub "CellShocked" wrote: Try adjusting the numbers for where the header and footer and page "top" and "bottom" positions are. On Sat, 26 Dec 2009 11:56:01 -0800, Brian wrote: I got it to work perfect, except for the Line spacing. Town: TEO No: Page 1 of ? Office: Supplier Order No: Appendix No: It needs to look like this: Town: TEO No: Page 1 of ? Office: Supplier Order No: Appendix No: I recorded 2 macro's one with the spacing and 1 removing the spacing. Then I printed them and compared them to see what was different. They are both exactly same. I need to get rid of that extra line spacing. "JLGWhiz" wrote: Brian, you cannot put a Sub within a Sub. It will not work. Private Sub Update_Engineer_Spec_10_Click() 'Update Header Footnote Information Sub DynamicHeader() '<<<This line will cause an error 'because you already have a title 'line for the click event. Comment the second title line out and see if it runs. "Brian" wrote in message ... The Code is Located under the "Private Sub Update_Engineer_Spec_10_Click()" This is a control Button for update the Workbook. I put the cade a the end of that code on the user form. Ok, I did reference the Text Boxes Correctly, like "Me.City_1.Value". "Me" = The User Form "City_1" = The Text Box in the User Form "Value" = A varible Input from user 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub I am still learning this VBA Stuff. "Ryan H" wrote: Where is this code located? If its located in a Standard Module then you need to use your Userform name like Userform1 as a reference instead of Me. I would also recommend you use the For Each...Next Loop to loop thru your worksheets instead for the For...Next Loop. For Each is used mainly for objects. So your code would look like this: 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Worksheet For Each sh In WorkSheets With sh.PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub Hope this helps! If so, click "YES" below. -- Cheers, Ryan "Brian" wrote: I am trying to get the Header/Footnote to update on all sheets in a Workbook without changing the page formatting. Some of the Sheets are Portrait and some are landscape. I have a User Form that already has the data in some Text Boxes. There is a Control Button (Update_Engineer_Spec_10) that when pushed takes the information on the User Form and Updates the Workbook, but I would like to Update the Header / Footnot as well. The Header was supposed to look like this. Town: TEO No: Page 1 of ? Office: Supplier Order No: Appendix No: Town = City_1 Office = Office_1 TEO No = TEO_No_1 Supplier Order No = CES_No_1 Appendix No = TEO_Appx_No_2 Here is the code I have, but it dosen't seem to work. The truth is nothing happens. 'Update Header Footnote Information Sub DynamicHeader() Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "Town:" & Me.City_1.Value & vbNewLine _ & "Office:" & Me.Office_1.Value .CenterHeader = "TEO No:" & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No:" & Me.CES_No_1.Value .RightHeader = "Page &P of &N" & vbNewLine _ & "Appendix No:" & Me.TEO_Appx_No_2.Value .LeftFooter = "Left Footer if desired" .CenterFooter = "Center Footer if desired" .RightFooter = "Right Footer if desired" .TopMargin = Application.InchesToPoints(0.25) End With Next sh End Sub Thanks . . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Headers & Footnotes from User Form
could the double line be at the top of the 1st row of the worksheet? the code below removes the border at the top of row 1. For sh = 1 To Sheets.Count With Sheets(sh).PageSetup LeftHeader = "&8Town: " & Me.City_1.Value & vbNewLine _ & "Office: " & Me.Office_1.Value CenterHeader = "&8TEO No: " & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No: " & Me.CES_No_1.Value RightHeader = "&8Page &P of &N" & vbNewLine _ & "Appendix No :" & Me.TEO_Appx_No_2.Value CenterFooter = "&8RESTRICTED - PROPRIETARY INFORMATION" & vbNewLine & _ "Not for Use or Disclosure outside AT&T except under Written Agreement " LeftFooter = "" RightFooter = "" LeftMargin = Application.InchesToPoints(0.25) RightMargin = Application.InchesToPoints(0.25) TopMargin = Application.InchesToPoints(0.55) BottomMargin = Application.InchesToPoints(0.7) HeaderMargin = Application.InchesToPoints(0.25) FooterMargin = Application.InchesToPoints(0.25) ScaleWithDocHeaderFooter = True AlignMarginsHeaderFooter = True End With With Sheets(sh) With Rows(1).Borders(xlEdgeTop) LineStyle = xlNone End With End With Next sh -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165166 Microsoft Office Help |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Headers & Footnotes from User Form
I can go into page setup and manualy remove the line space. I even tried it
on a blank page and it did the same thing. For some reason it only puts the extra line space in when the code runs. There has to be something in the code that (VBNewLine) that is putting the extra line in. Town / TEO No / Page These are in the corrcet location 1/4" from the top Unwanted Line Space (Needs to be removed) Office / Supp Order / Appd No Needs to be moved up I am at a total loss over this. I have looked at all the help sections and there is nothing about this matter. "joel" wrote: could the double line be at the top of the 1st row of the worksheet? the code below removes the border at the top of row 1. For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "&8Town: " & Me.City_1.Value & vbNewLine _ & "Office: " & Me.Office_1.Value .CenterHeader = "&8TEO No: " & Me.TEO_No_1.Value & vbNewLine _ & "Supplier Order No: " & Me.CES_No_1.Value .RightHeader = "&8Page &P of &N" & vbNewLine _ & "Appendix No :" & Me.TEO_Appx_No_2.Value .CenterFooter = "&8RESTRICTED - PROPRIETARY INFORMATION" & vbNewLine & _ "Not for Use or Disclosure outside AT&T except under Written Agreement " .LeftFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.55) .BottomMargin = Application.InchesToPoints(0.7) .HeaderMargin = Application.InchesToPoints(0.25) .FooterMargin = Application.InchesToPoints(0.25) .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True End With With Sheets(sh) With Rows(1).Borders(xlEdgeTop) .LineStyle = xlNone End With End With Next sh -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165166 Microsoft Office Help . |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Headers & Footnotes from User Form
Try instead of vbnewline vbcrlf or vbcr or vblf -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165166 Microsoft Office Help |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Headers & Footnotes from User Form
I got it fixed. Here is the code that worked. I recorded a marco to add the
Header/Footnote then adapted it for my use. Notice there is no vbNewLine or anything like that. Not sure why it works, but it does. Thanks for all your help. Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "&8Cilli Code: " & CLLI_Code_1.Value & Chr(10) & "Office Name: " & Me.Office_1.Value .CenterHeader = "&8TEO Number: " & Me.TEO_No_1.Value & Chr(10) & "Supplier Order No: " & Me.CES_No_1.Value .RightHeader = "&8Page &N of &N" & Chr(10) & "Appendix No: " & Me.TEO_Appx_No_2.Value .CenterFooter = _ "&8RESTRICTED - PROPRIETARY INFORMATION" & Chr(10) & "Not for use or Disclosure outside ATT except under Written Agreement" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.55) .BottomMargin = Application.InchesToPoints(0.7) .HeaderMargin = Application.InchesToPoints(0.25) .FooterMargin = Application.InchesToPoints(0.25) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = -3 .CenterHorizontally = True .CenterVertically = True .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True End With Next sh End Sub "joel" wrote: Try instead of vbnewline vbcrlf or vbcr or vblf -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165166 Microsoft Office Help . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wookbook Update from User Form | Excel Programming | |||
Update user form labels | Excel Programming | |||
User Form: Cannot Update Text Box | Excel Discussion (Misc queries) | |||
Update Range from User Form | Excel Programming | |||
Find and Update with User Form | Excel Programming |