Hiding / Showing header

Taf

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

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
35
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
35
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
35
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
35
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,082,380
Messages
5,365,117
Members
400,824
Latest member
Themilkybarkid

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top