Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing view as well as selection from code.
Using something like the following code, I was able to change the selection
in sheets other than the active sheet, transparent to the user. However, this code does not change the view upon entering the sheets. Is there a way to shift what will be displayed, comparable to hitting the "Home" key? I.e., the selected cell in the top left corner of the view upon entering the sheet. Sub SetActiveCell() application.ScreenUpdating = False set sh = Activesheet With Worksheets("Sheet B") .Select .Range("A1").Select End with sh.Activate Application.ScreenUpdating = True End sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing view as well as selection from code.
Hi AVR,
You can use Application.Goto to select and position to the destination cell, but it also activates the sheet. Just switch back to the current sheet afterwards. e.g. sn = Activesheet.Name Application.Goto Reference:=Worksheets("Sheet B").Range("A1"), scroll:=True Sheets(sn).Activate -- Gerd "AVR" wrote: Using something like the following code, I was able to change the selection in sheets other than the active sheet, transparent to the user. However, this code does not change the view upon entering the sheets. Is there a way to shift what will be displayed, comparable to hitting the "Home" key? I.e., the selected cell in the top left corner of the view upon entering the sheet. Sub SetActiveCell() application.ScreenUpdating = False set sh = Activesheet With Worksheets("Sheet B") .Select .Range("A1").Select End with sh.Activate Application.ScreenUpdating = True End sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing view as well as selection from code.
set sh = Activesheet 'If you do this
sh.Activate 'Then this is meaningless If you want to set Range A1 in the top left corner of your screen when you change sheets, Then Sheets(2).Activate Range("A1").Activate Sheets(3).Activate Range("A1").Activate Or to make it so that it will open on that view with code or manually you can use the SheetActivate event in the Worksheet code module with this in each worksheet. Private Sub Workbook_SheetActivate( ) Range("A1").Activate End Sub To enter the code snippet, just right click the sheet tab, then click "View Code" and paste the snippet into the module and save. "AVR" wrote: Using something like the following code, I was able to change the selection in sheets other than the active sheet, transparent to the user. However, this code does not change the view upon entering the sheets. Is there a way to shift what will be displayed, comparable to hitting the "Home" key? I.e., the selected cell in the top left corner of the view upon entering the sheet. Sub SetActiveCell() application.ScreenUpdating = False set sh = Activesheet With Worksheets("Sheet B") .Select .Range("A1").Select End with sh.Activate Application.ScreenUpdating = True End sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing view as well as selection from code.
set sh = Activesheet 'If you do this
sh.Activate 'Then this is meaningless Try reading the code again. In between, another sheet is selected. Thus the sh.Activate returns to the original sheet. If you want to set Range A1 in the top left corner of your screen when you change sheets, Then His code already does that, except select insures only one cell is selected. Activate on a range doesn't insure that. -- Regards, Tom Ogilvy "JLGWhiz" wrote in message ... set sh = Activesheet 'If you do this sh.Activate 'Then this is meaningless If you want to set Range A1 in the top left corner of your screen when you change sheets, Then Sheets(2).Activate Range("A1").Activate Sheets(3).Activate Range("A1").Activate Or to make it so that it will open on that view with code or manually you can use the SheetActivate event in the Worksheet code module with this in each worksheet. Private Sub Workbook_SheetActivate( ) Range("A1").Activate End Sub To enter the code snippet, just right click the sheet tab, then click "View Code" and paste the snippet into the module and save. "AVR" wrote: Using something like the following code, I was able to change the selection in sheets other than the active sheet, transparent to the user. However, this code does not change the view upon entering the sheets. Is there a way to shift what will be displayed, comparable to hitting the "Home" key? I.e., the selected cell in the top left corner of the view upon entering the sheet. Sub SetActiveCell() application.ScreenUpdating = False set sh = Activesheet With Worksheets("Sheet B") .Select .Range("A1").Select End with sh.Activate Application.ScreenUpdating = True End sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to make View Fit Selection work? | Excel Discussion (Misc queries) | |||
cursor moves view instead of selection | Excel Discussion (Misc queries) | |||
Changing Selection for the "Center Across Selection" alignment | Excel Discussion (Misc queries) | |||
Changing the 3D-View in powerpoint using vba | Excel Programming | |||
Scroll selection into view | Excel Programming |