DisplayHeadings

S

Sandy

I have the following Sub which does the job - however when the section of
code (asterisked) to hide the Row and Column heading runs, all the sheets
(there are 28) are opened in turn causing a lot of screen flicker - is there
any way that the code could be changed to avoid this happening.
Sandy

Sub Hide_Toolbars()

Open_All_Sheets

Application.ScreenUpdating = False
Application.EnableEvents = False

************
For Each sh In Worksheets
sh.Activate
ActiveWindow.DisplayHeadings = False
Next
************
Sheets("Data Input").Select

For Each wksht In Worksheets
With Application
.DisplayFullScreen = False
.DisplayFormulaBar = False
.CommandBars("Worksheet Menu Bar").Enabled = False
.CommandBars("Standard").Visible = False
.CommandBars("Formatting").Visible = False
.CommandBars("Drawing").Visible = False
End With
Next

Lock_All_Sheets

Sheets("Data Input").Select

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
 
B

Bob Phillips

It only applies to the window object, so it has to be active.

How about doing it as part of the print event instead?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
B

Bob Phillips

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveWindow
.DisplayHeadings = False
End With
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
S

Sandy

I have inserted the code exactly as you said - into the "ThisWorkbook"
section. The Row and Column headings remain in place. Is there something
else I am missing?
I must say that I don't understand why a Before_Print event should hide the
headings, can you explain?
Sandy
 
B

Bob Phillips

Sorry Sandy, I have misled you as I was thinking of PrintHeadings. I don't
think you can avoid opening each sheet so as to show its window.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
S

Sandy

Not to worry.
What I have decided to do is to put the following in each sheet - at least
it does away with the screen flickering at start up.

Private Sub Worksheet_Activate()
ActiveWindow.DisplayHeadings = False
End Sub

Thanks for your efforts
Sandy
 
D

Don Guillett

If that is your solution why not just put it in ONCE in the ThisWorkbook
module sheetactivate event
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top