Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to keep view still when running macos?
Hi,
This is a probably a very basic quetsion, but how do I ensure that when a macro is run, that my current view of the worksheet on the screen remains unaffected and doesn't jump around trying to follow the locus of the macro as it does its stuff perhaps in areas of the worksheet not currently in my original field of view? Cheers Ace70 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to keep view still when running macos?
Try:
Application.screenupdating = False your macro Application.screenupdating = True Mike "Ace70" wrote: Hi, This is a probably a very basic quetsion, but how do I ensure that when a macro is run, that my current view of the worksheet on the screen remains unaffected and doesn't jump around trying to follow the locus of the macro as it does its stuff perhaps in areas of the worksheet not currently in my original field of view? Cheers Ace70 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to keep view still when running macos?
Awesome. Thanks!
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to keep view still when running macos?
I may have jumped the gun here a bit. If I do as suggested:
Application.screenupdating = False your macro Application.screenupdating = True ....then unless my current view is at the topmost/left most part of the worksheet, executing the macro WILL change my current view to be end up being at the topmost/leftmost part of the worksheet when the macro completes. What shoulld do to prevent this so that the current view remains still/constant regardless of where I am on the worksheet when I execute the macro? Ace70 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to keep view still when running macos?
On Jun 11, 12:35 pm, Ace70 wrote:
I may have jumped the gun here a bit. If I do as suggested: Application.screenupdating = False your macro Application.screenupdating = True ...then unless my current view is at the topmost/left most part of the worksheet, executing the macro WILL change my current view to be end up being at the topmost/leftmost part of the worksheet when the macro completes. What shoulld do to prevent this so that the current view remains still/constant regardless of where I am on the worksheet when I execute the macro? Ace70 As a general rule you should try to avoid changing the selection in a macro: it's almost always unnecessary, can make the macro run slower, and (as you mention above) forgets your initial state. So for example instead of Workbooks("NewBook.xls").Select Worksheets("SomeSheet").Select Range("A1:C20").Select Selection.Font.Bold = True (as typically generated when you record a macro) you can use : Workbooks("NewBook.xls"). Worksheets("SomeSheet").Range("A1:C20").Font.Bold = True You can do this more elegantly using varaiables to keep track of what you're working on: Dim wb as Workbook, ws as Worksheet, rangeToBolden as Range Set wb = Workbooks ("NewBook.xls") Set ws = wb.Worksheets("SomeSheet") Set rangeToBolden = ws.Range ("A1:C20") rangeToBolden.Font.Bold = True ' could do other stuff with ws here.. If you absolutely _have_ to move away from you initial selection, then you can remember it at the start of the macro and restore it at the end: dim wbInitial as Workbook , wsInitial as Worksheet, rngInitial as Range ' save initial state Set wbInitial = activeworkbook Set wsInitial = activesheet Set rngInitial = selection ' '' macro code here ' 'restore initial state wbInitial.Activate wsInitial.Select rngInitial.Select |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to keep view still when running macos?
Hi, thanks for the general rule tip.
All my macros deal with stuff on the one worksheet. They do things such as refresh pivot tables, sort lists and copy cells from one spot to another. I have tried going into the VBA code for the macros I have that jump around and have tried to apply the same rules as you did to your example. However I did get errors I don't kno whow to correct. eg. I initially started off with: Sub Sort_New_List() Range("B53:P133").Select Selection.Sort Key1:=Range("L54"), Order1:=xlDescending, Key2:=Range( _ "M54"), Order2:=xlDescending, Key3:=Range("N54"), Order3:=xlDescending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Range("B53").Select ' I originally selected B53 as where I want the window view to be viewing when the macro is complete End Sub From this, I tried deleting the "Select" cases like in the example provided and I ended up with: Sub Sort_New_List() Range ("B53:P133") Sort Key1:=Range("L54"), Order1:=xlDescending, Key2:=Range( _ "M54"), Order2:=xlDescending, Key3:=Range("N54"), Order3:=xlDescending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal End Sub However I get an complie error invalid use of proptery where its says Range ("B53:P133") Any clues of tweaking this. As a general rule you should try to avoid changing the selection in a macro: it's almost always unnecessary, can make the macro run slower, and (as you mention above) forgets your initial state. So for example instead of Workbooks("NewBook.xls").Select Worksheets("SomeSheet").Select Range("A1:C20").Select Selection.Font.Bold = True (as typically generated when you record a macro) you can use : Workbooks("NewBook.xls"). Worksheets("SomeSheet").Range("A1:C20").Font.Bold = True You can do this more elegantly using varaiables to keep track of what you're working on: Dim wb as Workbook, ws as Worksheet, rangeToBolden as Range Set wb = Workbooks ("NewBook.xls") Set ws = wb.Worksheets("SomeSheet") Set rangeToBolden = ws.Range ("A1:C20") rangeToBolden.Font.Bold = True ' could do other stuff with ws here.. If you absolutely _have_ to move away from you initial selection, then you can remember it at the start of the macro and restore it at the end: dim wbInitial as Workbook , wsInitial as Worksheet, rngInitial as Range ' save initial state Set wbInitial = activeworkbook Set wsInitial = activesheet Set rngInitial = selection ' '' macro code here ' 'restore initial state wbInitial.Activate wsInitial.Select rngInitial.Select- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to keep view still when running macos?
I think you dropped a "with" keyword.
Option Explicit Sub Sort_New_List() with worksheets("sheet9999") '<-- change this 'or with Activesheet '????? with .Range("B53:P133") 'column L is the 11th column in B:P 'column M is the 12th column in B:P 'column N is the 13th column in B:P .sort key1:=.columns(11), order1:=xldescending, _ Key2:=.columns(12), Order2:=xlDescending, _ Key3:=.columns(13), Order3:=xlDescending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal end with end with End Sub (Uncompiled and untested--watch for typos.) And I bet you know if there are headers or not. If you do, don't let excel guess. Supply the information yourself and you won't have to worry about excel guessing wrong. Ace70 wrote: Hi, thanks for the general rule tip. All my macros deal with stuff on the one worksheet. They do things such as refresh pivot tables, sort lists and copy cells from one spot to another. I have tried going into the VBA code for the macros I have that jump around and have tried to apply the same rules as you did to your example. However I did get errors I don't kno whow to correct. eg. I initially started off with: Sub Sort_New_List() Range("B53:P133").Select Selection.Sort Key1:=Range("L54"), Order1:=xlDescending, Key2:=Range( _ "M54"), Order2:=xlDescending, Key3:=Range("N54"), Order3:=xlDescending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Range("B53").Select ' I originally selected B53 as where I want the window view to be viewing when the macro is complete End Sub From this, I tried deleting the "Select" cases like in the example provided and I ended up with: Sub Sort_New_List() Range ("B53:P133") Sort Key1:=Range("L54"), Order1:=xlDescending, Key2:=Range( _ "M54"), Order2:=xlDescending, Key3:=Range("N54"), Order3:=xlDescending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal End Sub However I get an complie error invalid use of proptery where its says Range ("B53:P133") Any clues of tweaking this. As a general rule you should try to avoid changing the selection in a macro: it's almost always unnecessary, can make the macro run slower, and (as you mention above) forgets your initial state. So for example instead of Workbooks("NewBook.xls").Select Worksheets("SomeSheet").Select Range("A1:C20").Select Selection.Font.Bold = True (as typically generated when you record a macro) you can use : Workbooks("NewBook.xls"). Worksheets("SomeSheet").Range("A1:C20").Font.Bold = True You can do this more elegantly using varaiables to keep track of what you're working on: Dim wb as Workbook, ws as Worksheet, rangeToBolden as Range Set wb = Workbooks ("NewBook.xls") Set ws = wb.Worksheets("SomeSheet") Set rangeToBolden = ws.Range ("A1:C20") rangeToBolden.Font.Bold = True ' could do other stuff with ws here.. If you absolutely _have_ to move away from you initial selection, then you can remember it at the start of the macro and restore it at the end: dim wbInitial as Workbook , wsInitial as Worksheet, rngInitial as Range ' save initial state Set wbInitial = activeworkbook Set wsInitial = activesheet Set rngInitial = selection ' '' macro code here ' 'restore initial state wbInitial.Activate wsInitial.Select rngInitial.Select- Hide quoted text - - Show quoted text - -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
View Custom View with Sheet Protection | New Users to Excel | |||
How do I view color onscreen (it shows up in print view only) | Excel Discussion (Misc queries) | |||
column is hidden in normal view but not print view | Excel Worksheet Functions | |||
How to view a custom view when the worksheet is protected? | Excel Worksheet Functions | |||
My view has changed, I want same view as when opening excel | Excel Worksheet Functions |