Hiding / Showing header

Taf

New Member
Joined
May 2, 2003
Messages
49
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
Code:
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

Code:
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??
 
Last edited:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Hermanito

Well-known Member
Joined
Apr 4, 2007
Messages
1,238
Code:
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? :biggrin:
 

Taf

New Member
Joined
May 2, 2003
Messages
49
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

Well-known Member
Joined
Apr 4, 2007
Messages
1,238
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:

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

Taf

New Member
Joined
May 2, 2003
Messages
49
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

Well-known Member
Joined
Apr 4, 2007
Messages
1,238
Code:
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

New Member
Joined
May 2, 2003
Messages
49
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

Well-known Member
Joined
Apr 4, 2007
Messages
1,238
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

New Member
Joined
May 2, 2003
Messages
49
Thanx for your help, I seem to have found an interesting workaround:

Code:
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
 

Forum statistics

Threads
1,171,654
Messages
5,876,716
Members
433,206
Latest member
james_y

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