Help me with macros

Joined
Oct 28, 2002
Messages
28
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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?
 
Joined
Oct 28, 2002
Messages
28
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

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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
 
Joined
Oct 28, 2002
Messages
28

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959

ADVERTISEMENT

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)
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
JPG Hi mate

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

With
.... code

End with not work? Just an idea
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top