How can I clear historical information under custom headers?

M

MS

I'm trying to clear all the historical information listed under the custom
header and footer buttons. Is there a fast easy way of doing this?
 
J

Jason Morin

Try a macro like this:

Sub ClearAll()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With
Next
End Sub

---
To use, press ALT+F11, go to Insert > Module, paste the code in the window,
and press ALT+Q. Now go to Tools > Macro > Macros and run it.

HTH
Jason
Atlanta, GA
 
M

MS

I followed your very clear directions carefully, however the macro seemed to
have no effect.

If I create a new (blank) excel worksheet , these historical headers and
footers are still part of the page setup. Which leads me to believe these
historical headers and footers are being tracked at a higher level than just
the WS level, and clearing them at the WS level has no effect.

Maybe it's being controlled at the application level?
 
M

Myrna Larson

Hi, Jason:

That doesn't do what the OP wants. He wants, when he clicks the arrow on the
dropdown list, to see only the items that Excel put there, not any custom
entries that he made himself. AFAIK, there's no way to do what he wants.

Myrna Larson
 
D

Dave Peterson

But the good thing is that stuff is associated with the pc (or user).

It doesn't travel with the workbook, right?
 
C

Ches

The information in the drop-down must be stored at the application level,
perhaps in a location such as Documents and Settings\Owner\Application
Data\Microsoft\Excel, which is where Excel stores the custom chart format
that can be created; or maybe even in the registry. Does anyone have any info
or ideas?
 
D

Dave Peterson

I think I searched the registry once looking for a unique string (for testing
this) and didn't find it there.
 
C

Ches

I wondered if one of the sub-folders might have something at either this
location....
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel
or possibly at...
HKEY_USERS\S-1-5-21-507921405-1958367476-1801674531-1003\Software\Microsoft\Office\11.0\Excel
(possibly in the UserInfo folder, where a hex string is stored)

Simple test: first export this particular registry key to be able to restore
it in event of catastrophe, then delete the value, and see what happens! If
the data is deleted from the drop-down menu, voila; would have to check that
only the unwanted data had been affected, and then re-establish new data as
you want it. A project for a quiet aftenoon....
 
Top