Update "centerheader" code running slowly need help speeding up

B

Breck

I recently added 1 new line of code to the following code I have been
running for a year. Previous to adding the the line of code
"PageSetup.CenterHeader = Range("A1").Value" I would click on a sheet
and there would be a slight almost unnoticable pause while the code
executed. After adding the above code there is now a 5 to 8 second
pause after clicking on the sheet. This pause is the same whether Calc
is on or off.

Is it just the nature of that new line of code or is there a way of
tweaking my code so it executes faster. I have 32 sheets with the same
code for each sheet. The entire Excel 2007 spreadsheet is 7MB
compress. It it was saved in Excel 2003 the file size would approach
20MB so it is a very large file.

What I'm trying to achieve is to make sure that the "crosses at" value
on 9 different charts is updated with the most current value that is
on the same sheet. This is the part that is working fine. I wanted to
update each of the sheets Header with data that is entered in another
location also. That is the additional code that causing the 5-8 second
delay. I also tried code that would update the header of every sheet
after the desired text is entered on a sheet but that code took 5-10
minuted to run so I tried the individual sheet approach hoping that
would run faster.


Private Sub worksheet_activate()
Application.ScreenUpdating = False

'In case sheets are grouped
Sheet4.Select

On Error GoTo Ungroup

'To Unprotect this sheet if protected
If Me.ProtectContents = True Then
ActiveSheet.Select
ActiveSheet.Unprotect
Else
End If

'This updates the sheet print header
PageSetup.CenterHeader = Range("A1").Value <======== This is the
new line of code that slows everything down

'To Update the Charts CrossesAt value from setup sheet that are on
the sheet
With ActiveSheet
If .ChartObjects("Chart 1").Chart.Axes(xlValue).CrossesAt
<> .Range("J20") Then
.ChartObjects("Chart 1").Chart.Axes(xlValue).CrossesAt
= .Range("J20")
End If
If .ChartObjects("Chart 2").Chart.Axes(xlValue).CrossesAt
<> .Range("K20") Then
.ChartObjects("Chart 2").Chart.Axes(xlValue).CrossesAt
= .Range("K20")
End If
If .ChartObjects("Chart 3").Chart.Axes(xlValue).CrossesAt
<> .Range("L20") Then
.ChartObjects("Chart 3").Chart.Axes(xlValue).CrossesAt
= .Range("L20")
End If
If .ChartObjects("Chart 4").Chart.Axes(xlValue).CrossesAt
<> .Range("Q20") Then
.ChartObjects("Chart 4").Chart.Axes(xlValue).CrossesAt
= .Range("Q20")
End If
If .ChartObjects("Chart 5").Chart.Axes(xlValue).CrossesAt
<> .Range("R20") Then
.ChartObjects("Chart 5").Chart.Axes(xlValue).CrossesAt
= .Range("R20")
End If
If .ChartObjects("Chart 6").Chart.Axes(xlValue).CrossesAt
<> .Range("S20") Then
.ChartObjects("Chart 6").Chart.Axes(xlValue).CrossesAt
= .Range("S20")
End If
If .ChartObjects("Chart 7").Chart.Axes(xlValue).CrossesAt
<> .Range("T20") Then
.ChartObjects("Chart 7").Chart.Axes(xlValue).CrossesAt
= .Range("T20")
End If
If .ChartObjects("Chart 8").Chart.Axes(xlValue).CrossesAt
<> .Range("AA65") Then
.ChartObjects("Chart 8").Chart.Axes(xlValue).CrossesAt
= .Range("AA65")
End If
If .ChartObjects("Chart 9").Chart.Axes(xlValue).CrossesAt
<> .Range("AB65") Then
.ChartObjects("Chart 9").Chart.Axes(xlValue).CrossesAt
= .Range("AB65")
End If
End With

Range("AI1:AI262").AutoFilter Field:=1, Criteria1:="<>0"

If Me.ProtectContents = False Then
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, AllowFormattingColumns:=True, AllowFiltering:=True
'MsgBox "Protected"
Else
ActiveSheet.Unprotect
'MsgBox "Un Protected"
End If
'Also see below
'Me.ProtectDrawingObjects

On Error GoTo 0

'The macro is done. Use Exit sub, otherwise the macro
'execution WILL continue into the error handler
Exit Sub

Ungroup:
MsgBox "Please ungroup sheets"


End Sub
 
J

Jon Peltier

B

Breck

I'll give it a try but I'm new at this and don't know how to program
yet. I found most of the code posted somewhere else and adapted it to
my needs. I hope that I can figure it out.
 
B

Breck

It works sort of. I copied the code from the first link and the pause
is down to what it was before adding the new code. I tried to add a
range name where the text "My Company" and "Highly Confidential and
Proprietary" but it didn't work. I can't figure out what changes need
to be made to change the text to a range name. I would also like to
change the font size but adding "&28 " before the "My Company" also
resulted in an error. Can anyone help.

head = """&LMy Company&R&D / &T"""
foot = """&LHighly Confidential and Proprietary&RFinance"""
 
B

Breck

It works sort of. I copied the code from the first link and the pause
is down to what it was before adding the new code. I tried to add a
range name where the text "My Company" and "Highly Confidential and
Proprietary" but it didn't work. I can't figure out what changes need
to be made to change the text to a range name. I would also like to
change the font size but adding "&28 " before the "My Company" also
resulted in an error. Can anyone help.

head = """&LMy Company&R&D / &T"""
foot = """&LHighly Confidential and Proprietary&RFinance"""
 
B

Breck

It works sort of. I copied the code from the first link and the pause
is down to what it was before adding the new code. I tried to add a
range name where the text "My Company" and "Highly Confidential and
Proprietary" but it didn't work. I can't figure out what changes need
to be made to change the text to a range name. I would also like to
change the font size but adding "&28 " before the "My Company" also
resulted in an error. Can anyone help.

head = """&LMy Company&R&D / &T"""
foot = """&LHighly Confidential and Proprietary&RFinance"""
 
J

Jon Peltier

head = """&Lsomething&Csomething&Rsomething"""

I think you need each escape code even if the relevant something is "". If
you want to say "My Company R&D" you have to enter it as "My Company R&&D".
If the command works without &28, it will work with it. Start simple and
build it up piece by piece, testing each piece until it works. To send text
from a cell somewhere, put the text into a variable and concatenate the
variable:

head = """&Lsomething&C" & sInsertedText & "&Rsomething"""

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


It works sort of. I copied the code from the first link and the pause
is down to what it was before adding the new code. I tried to add a
range name where the text "My Company" and "Highly Confidential and
Proprietary" but it didn't work. I can't figure out what changes need
to be made to change the text to a range name. I would also like to
change the font size but adding "&28 " before the "My Company" also
resulted in an error. Can anyone help.

head = """&LMy Company&R&D / &T"""
foot = """&LHighly Confidential and Proprietary&RFinance"""
 
B

Breck

Thanks for you help

Jon Peltier said:
head = """&Lsomething&Csomething&Rsomething"""

I think you need each escape code even if the relevant something is "". If
you want to say "My Company R&D" you have to enter it as "My Company
R&&D". If the command works without &28, it will work with it. Start
simple and build it up piece by piece, testing each piece until it works.
To send text from a cell somewhere, put the text into a variable and
concatenate the variable:

head = """&Lsomething&C" & sInsertedText & "&Rsomething"""

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


It works sort of. I copied the code from the first link and the pause
is down to what it was before adding the new code. I tried to add a
range name where the text "My Company" and "Highly Confidential and
Proprietary" but it didn't work. I can't figure out what changes need
to be made to change the text to a range name. I would also like to
change the font size but adding "&28 " before the "My Company" also
resulted in an error. Can anyone help.

head = """&LMy Company&R&D / &T"""
foot = """&LHighly Confidential and Proprietary&RFinance"""
Click to expand...
 

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

Similar Threads

Excel 2003 code to use instead of ChartObjects 4
MinimumScale method of Axis Object failed 1
Changing a horizontal axis crosses value using VBA 5
Private Sub Worksheet script is not effecting worksheet 1
Change minimumscale with the change event of a combobox 1
Worksheet_Calculate event and input data 3
Graph - Runtime Error 1004 5
Skip if variable not determined 1

Top