| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update "centerheader" code running slowly need help speeding up
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update "centerheader" code running slowly need help speeding up
Here are a couple of pages that show how to use an ancient technology called
XLM (XL Macro programming language) that does page setup like lightning: http://groups.google.com/group/micro...y&rnum=2&pli=1 http://www.eggheadcafe.com/forumarch...st23283696.asp - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ http://www.vbaexpress.com/kb/getarticle.php?kb_id=750 "Breck" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update "centerheader" code running slowly need help speeding up
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. On Jan 13, 10:15*pm, "Jon Peltier" wrote: Here are a couple of pages that show how to use an ancient technology called XLM (XL Macro programming language) that does page setup like lightning: http://groups.google.com/group/micro...rogramming/bro... http://www.eggheadcafe.com/forumarch...Jun2005/post23... - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc.http://PeltierTech.com/WordPress/ _______ http://www.vbaexpress.com/kb/getarticle.php?kb_id=750 "Breck" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update "centerheader" code running slowly need help speeding up
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""" On Jan 13, 10:15*pm, "Jon Peltier" wrote: Here are a couple of pages that show how to use an ancient technology called XLM (XL Macro programming language) that does page setup like lightning: http://groups.google.com/group/micro...rogramming/bro... http://www.eggheadcafe.com/forumarch...Jun2005/post23... - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc.http://PeltierTech.com/WordPress/ _______ http://www.vbaexpress.com/kb/getarticle.php?kb_id=750 "Breck" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update "centerheader" code running slowly need help speeding up
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""" On Jan 13, 10:15*pm, "Jon Peltier" wrote: Here are a couple of pages that show how to use an ancient technology called XLM (XL Macro programming language) that does page setup like lightning: http://groups.google.com/group/micro...rogramming/bro... http://www.eggheadcafe.com/forumarch...Jun2005/post23... - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc.http://PeltierTech.com/WordPress/ _______ http://www.vbaexpress.com/kb/getarticle.php?kb_id=750 "Breck" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update "centerheader" code running slowly need help speeding up
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""" On Jan 13, 10:15*pm, "Jon Peltier" wrote: Here are a couple of pages that show how to use an ancient technology called XLM (XL Macro programming language) that does page setup like lightning: http://groups.google.com/group/micro...rogramming/bro... http://www.eggheadcafe.com/forumarch...Jun2005/post23... - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc.http://PeltierTech.com/WordPress/ _______ http://www.vbaexpress.com/kb/getarticle.php?kb_id=750 "Breck" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update "centerheader" code running slowly need help speeding up
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/ _______ "Breck" wrote in message ... 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""" On Jan 13, 10:15 pm, "Jon Peltier" wrote: Here are a couple of pages that show how to use an ancient technology called XLM (XL Macro programming language) that does page setup like lightning: http://groups.google.com/group/micro...rogramming/bro... http://www.eggheadcafe.com/forumarch...Jun2005/post23... - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc.http://PeltierTech.com/WordPress/ _______ http://www.vbaexpress.com/kb/getarticle.php?kb_id=750 "Breck" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update "centerheader" code running slowly need help speeding up
Thanks for you help
"Jon Peltier" wrote in message ... 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/ _______ "Breck" wrote in message ... 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""" On Jan 13, 10:15 pm, "Jon Peltier" wrote: Here are a couple of pages that show how to use an ancient technology called XLM (XL Macro programming language) that does page setup like lightning: http://groups.google.com/group/micro...rogramming/bro... http://www.eggheadcafe.com/forumarch...Jun2005/post23... - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc.http://PeltierTech.com/WordPress/ _______ http://www.vbaexpress.com/kb/getarticle.php?kb_id=750 "Breck" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION | Excel Discussion (Misc queries) | |||
| Running "String" of Code | Excel Programming | |||
| Running Custom Code if "Entire Workbook" is Selected for Printing | Excel Programming | |||
| Stop code from running when I click "Cancel" | Excel Programming | |||
| "Casing" the strings for smooth code running | Excel Programming | |||
Linear Mode
