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

phg

Board Regular
Joined
Jul 4, 2007
Messages
81
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.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
In each sheet's module, something like this.
Code:
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

Board Regular
Joined
Jul 4, 2007
Messages
81
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Board Regular
Joined
Jul 4, 2007
Messages
81

ADVERTISEMENT

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.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

In the ThisWorkbook module
Code:
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
Code:
Private Sub Worksheet_Activate()
Range("$A$1").Select
End Sub

lenze
 

phg

Board Regular
Joined
Jul 4, 2007
Messages
81
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
Code:
Sheets("CGSL").Activate
or better, perhaps, change the the Open event
Code:
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
 
Last edited:

phg

Board Regular
Joined
Jul 4, 2007
Messages
81
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top