Hide Row/Column Headers on Each Sheet

[throtmorton]

Hi,

I'm trying to make a simple macro to let the user hide row and column headers throughout the active workbook.

Here's what I came up with, and I've tried variations on the theme, but it only does the active sheet and won't cycle through to the others. Searched to no avail.

Any help appreciated.

Paul

Public Sub RowColumnHeaders()
Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
    ActiveWindow.DisplayHeadings = False
    
    Next ws

End Sub

 

[Peter_SSs]

throtmorton

Try this modification:

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> RowColumnHeaders()
<SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets
    ws.Activate
    ActiveWindow.DisplayHeadings = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> ws
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[babycody]

Not a code person, but I got this from recording. I just grouped the sheets and then selected the option not to display column and row headers. The last bit ungrouped the sheets.

Sub Macro5()

    Sheets.Select
    ActiveWindow.DisplayHeadings = False
    ActiveSheet.Select
End Sub

 

[RalphA]

Hmm, I used your coding on a two-sheet workbook with a heading in the first three cells of each sheet, as well as a header in each sheet, ran the macro multple times, and couldn't detect any changes to any of the two worksheets. What am I doing wrong?

 

[babycody]

Hmm, I used your coding on a two-sheet workbook with a heading in the first three cells of each sheet, as well as a header in each sheet, ran the macro multple times, and couldn't detect any changes to any of the two worksheets. What am I doing wrong?

The headers refered to here are the A, B, C, ..... at the top of each column, and the 1, 2, 3..... beside each row. You can see the option by going to Tools>Options and then choose the View tab. The option is toward the bottom. HTH clear things up for you.

 

[throtmorton]

Peter,

Yes, that works, thanks.

Is there also a way to come back to the current sheet? Right now, it cycles through and ends up at the farthest tab on the right.

Thanks,

Paul

 

[Boller]

Assuming you mean the A,B,C... column headers and the 1,2,3...row numbers :-

Public Sub RowColumnHeaders()
Dim ws As Worksheet
Set ws = ActiveSheet
Sheets.Select
ActiveWindow.DisplayHeadings = False
ws.Select
End Sub

 

[throtmorton]

Boller,

Thanks, but I seem to run into trouble with that code. If I use it in "This Workbook," I get the message "400." If I place it in a Module, I get Runtime 1004 error, and "Method 'Select' of 'Sheets' Failed"

Paul

 

[RalphA]

throtmorton:

Yes, I now see what you were describing, and I got the same problem as you, that is, only the active sheet would not display the A, B, C and 1, 2, 3 headings.

But, I see that Peter's good code did the trick!

 

[Peter_SSs]

Peter,

Yes, that works, thanks.

Is there also a way to come back to the current sheet? Right now, it cycles through and ends up at the farthest tab on the right.

Thanks,

Paul

try this:

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> RowColumnHeaders()
<SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> CurrentSheet <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Set</SPAN> CurrentSheet = ActiveSheet
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets
    ws.Activate
    ActiveWindow.DisplayHeadings = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> ws
    CurrentSheet.Activate
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>