Macro for Custom Header

[matt_st_onge]

Hello,

I am trying to create a macro button to help me automatically do the
following tasks:

-Change Sheet
-View
-Header and Footer
-Custom Header

When I try to record the macro, it won't let me stop the macro with a
dialog box open.

The custom header will change every time the file is opened.

Any ideas?

Any help is greatly appreciated!

Matt St.Onge
Drafting & Estimating Manager
Mankato Kasota Stone
www.mankatokasotastone.com

 

[Guest]

Do you just want to show the pagesetup dialog box?

Application.Dialogs(xlDialogPageSetup).Show

 

[matt_st_onge]

Great! Yes. Is it possible to take it a couple steps further?

i.e...

-Header/Footer Tab
-Custom Header...

Thanks.

Matt St.Onge
Drafting & Estimating Manager
Mankato Kasota Stone
www.mankatokasotastone.com

 

[matt_st_onge]

More...

When the pagesetup dialog box comes up, you can literally hit "H" on
the keyboard and "<Alt>C" to get there after that. However, I am not
aware of how to program literal key strokes into VBA.

I am assuming that is all I would need to do?

Thanks again.

Matt St.Onge
Drafting & Estimating Manager
Mankato Kasota Stone
www.mankatokasotastone.com

 

[Guest]

SendKeys can perform literal keystrokes.

Application.SendKeys "H" & "%(C)"
Application.Dialogs(xlDialogPageSetup).Show

After you confirm the custom header, click OK and return to the main
pagesetup form - it may not show the header you just entered. Just click OK.


Alternatively, you could get the user to input the header via an inputbox,
then have VBA set the custom header

x = Application.InputBox("Enter Header")
Sheets("Sheet1").PageSetup.LeftHeader = x

If you search VBA's help for headers, you should be able to find formatting
codes for font, font size, italics, bold, etc.