Help me with macros

[All the damn names are ta]

I have made a user form with a text box and a button, i want a macro assigned to the button which takes the text from the text box and puts it in the header and also puts the date in the footer

 

[phantom1975]

WELCOME TO THE BOARD

This should do the trick:

Private Sub CommandButton1_Click()
With Sheets("Sheet1").PageSetup
.CenterHeader = TextBox1.Value
.CenterFooter = Date
End With
End Sub

Did that do the trick?

 

[All the damn names are ta]

Thanks a lot that worked really well.


Private Sub CommandButton1_Click()
With Sheets("Sheet1").PageSetup
.CenterHeader = TextBox1.Value
.CenterFooter = Date
End With
End Sub

But can you make it so the header/footer is applied to all sheets, and also for the form to close after header/footer has been done.

 

[phantom1975]

On 2002-10-29 16:44, All the **** names are taken wrote:
Thanks a lot that worked really well.


Private Sub CommandButton1_Click()
With Sheets("Sheet1").PageSetup
.CenterHeader = TextBox1.Value
.CenterFooter = Date
End With
End Sub

But can you make it so the header/footer is applied to all sheets, and also for the form to close after header/footer has been done.

See if this works:

Private Sub CommandButton1_Click()
With ActiveWorkBook.Worksheets.PageSetup
.CenterHeader = TextBox1.Value
.CenterFooter = Date
End With
Unload Me
' Me is just another way of saying UserForm
End Sub

 

[All the damn names are ta]

when i run this macro it does not work, and it highlights this:
With ActiveWorkBook.Worksheets.PageSetup
so i'm guessing this needs changing

 

[phantom1975]

I was taking a guess on that last one. Otherwise, you should be able to copy and paste the code. Instead of Sheets("blahblah") change it to ActiveSheet.

 

[Juan Pablo González]

There's no direct way of assigning the same PageSetup to all worksheets in a workbook, in VBA, you have to loop through each of them (Unlike within Excel)

 

[All the damn names are ta]

Thanks

 

[Jack in the UK]

JPG Hi mate

Would
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

With
.... code

End with not work? Just an idea

 

[Juan Pablo González]

No Jack, doesn't work... MS has documented it, there are tricks, like using XLM macros, or copying sheets, but, no, that won't work... sucks, doesn't it ?