Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Update Headers & Footnotes from User Form
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 683
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 489
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 683
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,565
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 683
Default 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


.

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 683
Default 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


.

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 277
Default 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


.

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Wookbook Update from User Form Brian Excel Programming 8 December 22nd 09 12:51 PM
Update user form labels Jim Excel Programming 2 June 21st 07 10:43 AM
User Form: Cannot Update Text Box Charles in Iraq Excel Discussion (Misc queries) 0 October 12th 06 07:53 AM
Update Range from User Form fybar[_2_] Excel Programming 1 April 2nd 06 01:27 AM
Find and Update with User Form prodsched[_7_] Excel Programming 0 May 18th 04 03:35 PM


All times are GMT +1. The time now is 10:42 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement