Excel VBA question - last 2 Windows selected

[Hedges]

I'm trying to find out the last 2 windows in Excel which were selected. Excel must "know" this information because if you close the active window it then makes the next-most-recently selected window the new active window. However I can't find this property in the window or windows collection objects. (The "Previous"/"Next" properties of the windows object refer to the order in which the windows were created.)

Any help gratefully accepted!

Thanks,
Hedges

[Arlie_Proctor]

Try the workbooks collection. You should be able to walk through the collection by index as well as by number.

[Course 4 Escapee]

Yes. What Arlie_Proctor said.

[Hedges]

Arlie

I don't think that walking through the workbooks collection by number (or by workbook name) will help. What if I have just one workbook open but have created six windows looking onto it? I want to be able to reference the _windows_ in the order they were last active.

Actually in my case, I _do_ want to reference the workbooks in the order they were last active (I'm really looking for the 2 most recently active workbooks) but I still can't find any property that will tell me this. I was hoping I could at least find it indirectly by going through the windows collection in the order in which windows were last active. As I said in my initial post, Excel clearly does keep a record somewhere of the order in which windows were last active. I just need to find out where!

Hedges

[NoName]

You may be able to do this by trapping the (I think) window_activate event and logging the window in a global variable (or three, the current plus the two prior).

[Hedges]

NoName - thanks for the suggestion. I might well use that as a back-up if I can't find the information more directly.

Hedges

[Arlie_Proctor]

I think this is more along the lines of what you wanted.

Sub Test_Window()

ActiveWindow.ActivatePrevious

End Sub

[thing]

ActiveWindow.ActivatePrevious

Heh. I just used this to improve one of my macros. Thanks! :D

[Hedges]

In Excel 97, the Windows collection does seem to refer to windows in the order in which they were last activated. So I can use this, or use better still use, Arlie's most recent suggeston, but........

Confession time: The real problem I was having was in Word, not Excel at all. And in Word the Windows collection index refers to the order in which the wondows were created, not the activation order. I framed the question as an Excel problem as I thought more people would be familiar with Excel rather than Word and not thinking that difference would be important!

Thanks for all the help, though. I think I'll just have to end up logging the windows through the activate event as NoName suggested.

Hedges

[Arlie_Proctor]

The object models for Excel, Word, Powerpoint, and Access are different and basic objects work differently in each (I just LOVE Microsoft). If you need VBA help in one particular application, you need to state that explicitly to get the right answer.