DisplayHeadings
 

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

DisplayHeadings
 

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)

"Sandy" wrote in message
...
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

DisplayHeadings
 

Can you elaborate a bit Bob? - I'm an amateur at this stuff.
Sandy

"Bob Phillips" wrote in message
...
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)

"Sandy" wrote in message
...
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

DisplayHeadings
 

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)

"Sandy" wrote in message
...
Can you elaborate a bit Bob? - I'm an amateur at this stuff.
Sandy

"Bob Phillips" wrote in message
...
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)

"Sandy" wrote in message
...
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

DisplayHeadings
 

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

"Bob Phillips" wrote in message
...

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)

"Sandy" wrote in message
...
Can you elaborate a bit Bob? - I'm an amateur at this stuff.
Sandy

"Bob Phillips" wrote in message
...
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)

"Sandy" wrote in message
...
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

DisplayHeadings
 

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)

"Sandy" wrote in message
...
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

"Bob Phillips" wrote in message
...

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)

"Sandy" wrote in message
...
Can you elaborate a bit Bob? - I'm an amateur at this stuff.
Sandy

"Bob Phillips" wrote in message
...
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)

"Sandy" wrote in message
...
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

DisplayHeadings
 

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

"Bob Phillips" wrote in message
...
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)

"Sandy" wrote in message
...
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

"Bob Phillips" wrote in message
...

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)

"Sandy" wrote in message
...
Can you elaborate a bit Bob? - I'm an amateur at this stuff.
Sandy

"Bob Phillips" wrote in message
...
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)

"Sandy" wrote in message
...
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

DisplayHeadings
 

If that is your solution why not just put it in ONCE in the ThisWorkbook
module sheetactivate event
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sandy" wrote in message
...
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

"Bob Phillips" wrote in message
...
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)

"Sandy" wrote in message
...
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

"Bob Phillips" wrote in message
...

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)

"Sandy" wrote in message
...
Can you elaborate a bit Bob? - I'm an amateur at this stuff.
Sandy

"Bob Phillips" wrote in message
...
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)

"Sandy" wrote in message
...
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