Range activation when a worksheet is activated

[Gavin T]

Hey guys,

When a user activates a worksheet, excel reactivates the range that was last active when the worksheet was deactivated (sounds more confusing that what I'm trying to say.) I'd like to know, is there some property of the Worksheet object that stores the information of that range or, somehow a way to programmatically change that range? I'm trying to somehow set which range would activate upon worksheet activation - without using worksheet events or activating the worksheet itself.

I haven't come across any mention of this anywhere and I don't know if this is possible via vba.

Thanks for any ideas

 

[offthelip]

That is exactly what worksheet events are for, why are you trying to avoid them??

 

[Gavin T]

It's more of an academic question. But either way using a worksheet event wouldn't really solve what I'm thinking about. I have many codes that works on worksheets (without activating them) but leaves the range selected in a way I don't want. I could just have all my code go through each worksheet, activate it, and select the desired range but seems like there should be a faster and better way.

 

[offthelip]

Why do you need to select a range in your code? it is usually possible to avoid selecting ranges in VBA, and the reason for avoiding selecting ranges is to avoid exactly the problem you have come up with

 

[Gavin T]

I am reapplying a sort on a table on another sheet. Which automatically selects the entire table.

wsCompleted.ListObjects("tSummary").Sort.Apply

 

[offthelip]

Ok I understand the problem, I think you are left with reselecting the correct range after it.