VBA determine workbook index and switch to one 'Other' workbook

Question

I have code which runs on one workbook and starts by referencing 1 other open workbook, which could have any name, I want to switch to the other open workbook by using the index number for this reason.

Users could open the workbook containing code first or the other workbook first, so I want to be able to say something like IF index of the active sheet is 1, then Workbooks(2).Activate, but if the index of the active sheet is 2, then Workbooks(1).Activate, thank you.

I have this code to make sure there are only 2 books open already;

   If Workbooks.Count > 2 Then
        MsgBox "Have ONLY the new data file and this open before running :)"
        Exit Sub
End If

Assuming you only have one window open on each, you can just call activewindow.ActivateNext to switch from one to the other. — Rory

Unknown Unknown · Accepted Answer · 2016-03-15T11:08:56

To toggle back and forth between two open workbooks in the Workbooks Collection use some maths against a comparison of the ActiveWorkbook property and the ThisWorkbook Property.

Workbooks(IIf(CBool(Workbooks(1).Name = ThisWorkbook.Name), _
              1 - CBool(ActiveWorkbook.Name = ThisWorkbook.Name), _
              2 + CBool(ActiveWorkbook.Name = ThisWorkbook.Name))).Activate

This should likely be accompanied by a check to make sure that there are only two workbooks in the queue (as your original sample indicates).

VBA determine workbook index and switch to one 'Other' workbook

1 Answers