Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How can I clear historical information under custom headers?
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? |
#2
|
|||
|
|||
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 "MS" wrote: 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? |
#3
|
|||
|
|||
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? "Jason Morin" wrote: 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 "MS" wrote: 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? |
#4
|
|||
|
|||
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 On Wed, 8 Jun 2005 07:24:40 -0700, "Jason Morin" wrote: 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 "MS" wrote: 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? |
#5
|
|||
|
|||
But the good thing is that stuff is associated with the pc (or user).
It doesn't travel with the workbook, right? Myrna Larson wrote: 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 On Wed, 8 Jun 2005 07:24:40 -0700, "Jason Morin" wrote: 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 "MS" wrote: 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? -- Dave Peterson |
#6
|
|||
|
|||
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? "Dave Peterson" wrote: But the good thing is that stuff is associated with the pc (or user). It doesn't travel with the workbook, right? |
#7
|
|||
|
|||
I think I searched the registry once looking for a unique string (for testing
this) and didn't find it there. Ches wrote: 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? "Dave Peterson" wrote: But the good thing is that stuff is associated with the pc (or user). It doesn't travel with the workbook, right? -- Dave Peterson |
#8
|
|||
|
|||
I wondered if one of the sub-folders might have something at either this
location.... HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\E xcel 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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Macro to clear out check box seletions | Excel Worksheet Functions | |||
Aggregating categorized information from several worksheets | Excel Worksheet Functions | |||
Update a spreadsheet with new information. | Excel Discussion (Misc queries) | |||
Clearing information in certain columns | Excel Discussion (Misc queries) | |||
Sharing information between Access and Excel | Excel Discussion (Misc queries) |