How do I use a macro to set the range that will be active

[phg]

Excel keeps track of the range and window layout that was active the last time you activated a worksheet and makes it active when you select the sheet again.

I want to create a macro which set the active range and window layout for several worksheets so that when the user selects the sheet I know what they are going to see.

I want to do this without actually selecting the worksheet in the macro.

Any suggestions.

 

[lenze]

In each sheet's module, something like this.

Private Sub WorkSheet_Activate()
Range("$A$1").Select
End Sub

Note: if you want to go to the same range on all sheets, you can just use the ThisWorkBook Activate event.

lenze

 

[phg]

I am getting myself stuck in a loop since I want it to be in one position on start-up and another when the workbook is open and you move to it from another sheet.

If I add the positioning to the desired place for start-up in the before_close event, as soon as the macro moves to the sheet to do the positioning it initiates the workbook_activate which moves to the other position.

Excel obviously keeps track of the window layout for each worksheet. Is there no way the programmer can get at that information from a macro?

Phil

 

[lenze]

What are the name(s) of your sheet(s) and what cell do you want selected on startup? What cell on sheet reentry?

lenze

 

[phg]

On start-up I would like "R4" selected and "Q1" in the upper left corner of the window.

On re-entry I would "A1" selected and in the upper left corner of the window.

 

[phg]

The sheet name is "CGSL"

 

[lenze]

In the ThisWorkbook module

Private Sub Workbook_Open()
Application.Goto reference:=Sheets("CGSL").Range("Q1"), scroll:=True
Sheets("CGSL").Range("R$4").Select
End Sub

In the Sheet module

Private Sub Worksheet_Activate()
Range("$A$1").Select
End Sub

lenze

 

[phg]

If you save and close the workbook with "Sheet2" active, when you reopen it it will go sheet "CGSL" with "K1" in the upper left corner.

As I understand the Workbook_Open fires first. When you execute the .GoTo step it scrolls the window to the correct place and then triggers the Worksheet_Activate event which positions the window with "A1" in the upper left corner, overridiing the scroll and then finally selects "R$4".

 

[lenze]

No, you do not understand. The 2 events are totally seperate and independent. When you open the WorkBook, The Open event goes to Q1, scrolls to the upper left, then selects R4. The Activate event goes to A1 when the sheet is entered from another sheet. I see your problem when the sheet is saved with Sheet2 active, because the Activate event may be fired from the Open event. Try simply adding a BeforeSave event to the ThisWorkbook module

Sheets("CGSL").Activate

or better, perhaps, change the the Open event

Private Sub Workbook_Open()
Application.EnableEvents = False
Application.Goto reference:=Sheets("CGSL").Range("Q1"), scroll:=True
Sheets("CGSL").Range("R$4").Select
Application.EnableEvents = True
End Sub

That should solve the problem

lenze

 

[phg]

You're right I can make this work by using the .EnableEvents property but it would be nice to have the original question answered.

Is it possible to set the range that will be selected on a given sheet from a vba module? That way I wouldn't have to add a worksheet_activate event to each new worksheet I add and I wouldn't have to edit each worksheet if I wanted to change the default location