Hiding / Showing header

[Taf]

Hi everybody,

I have a strage problem. I am trying to cycle through all the sheets in the orkbook and hide header, sheet tabs and the gridlines. I have tried to do this through 2 macros

Sub DeGridize()
Dim wd As Window
    For Each wd In ThisWorkbook.Windows
        With ActiveWindow
            .DisplayHeadings = False
            .DisplayGridlines = False
            .DisplayWorkbookTabs = False
        End With
    Next wd
End Sub

the second macro I tried was

Sub DeGridize()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheet
        With ActiveWindow
            .DisplayHeadings = False
            .DisplayGridlines = False
            .DisplayWorkbookTabs = False
        End With
    Next ws
End Sub

Once the macro has been run, the active worksheet is appropriately "cleaned". But as I cycle through the worksheets with CTRL+PGDN/PGUP, the other worksheets have not been hidden appropriately. Interestingly, if I press CTRL+DN and go to the end of the worksheet, the sheet tabs then hide. But the grid is still visible. If I run the macro again, it again cleans the activesheet but not the remaining sheets.

Is something wrong with the macro??

 

[Hermanito]

Sub DeGridize()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheet[COLOR=red]s[/COLOR]
        With [COLOR=red]ws[/COLOR]
            .DisplayHeadings = False
            .DisplayGridlines = False
            .DisplayWorkbookTabs = False
        End With
    Next ws
End Sub

Just make the changes indicated in red and it should work fine.

By the way, an orkbook, is that a workbook from some fantasy realm?

 

[Taf]

Sorry about the typos.

tried your code but I am getting the following error.

"Compile Error: Method or Data member not found"

The code highlighted is ".DisplayHeadings "

 

[Hermanito]

Oh, indeed, I just assumed those properties were tried and tested by you already, just that they didn't apply to all sheets... I checked and they are actually properties of the Excel application instance, try this:

Sub DeGridize()
    Dim wnd As Window
    wnd = Application.Windows(1)
    With wnd
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayWorkbookTabs = False
    End With
End Sub

 

[Taf]

Thanks for the code ... but again I got an error. This time round it was:

Run-time error '91':
Object Variable or With block variable not set

 

[Hermanito]

Sub DeGridize()
    Dim wnd As Window
    [COLOR=red]Set[/COLOR] wnd = Application.Windows(1)
    With wnd
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayWorkbookTabs = False
    End With
End Sub

Add the little word in red , that should do it...

 

[Taf]

The code worked but again it does not seem to do what I would like it to do ... which is primarily to remove the headings, gridline and workbooktabs for all the worksheets. The same problems that I highlghted in the original post still persists.

 

[Hermanito]

I see what you mean, but these properties are not worksheet properties, they're not even workbook properties; so I am not sure why it only seems to work on the active sheet. Perhaps Microsoft doesn't expect you to know keyboard shortcuts and thinks, once the tabs are gone, you cannot switch sheets anymore...

 

[Taf]

Thanx for your help, I seem to have found an interesting workaround:

Sub DeGridize()
Dim wSheet As Worksheet
Dim wnd As Window
    For Each wSheet In ThisWorkbook.Worksheets
        wSheet.Activate
        Set wnd = Application.Windows(1)
        With wnd
            .DisplayHeadings = False
            .DisplayGridlines = False
            .DisplayWorkbookTabs = False
        End With
    Next wSheet
End Sub

Now I would probably finish this off by just wrapping the code with a screenupdating false /true code to stop the screen flickering

Thanx again