# Excel 2007 Switch Windows



## joefrench (Feb 19, 2008)

Over the past year, I've found that switching between multiple workbooks and performing different operations can be a bit cumbersome in Excel 2007 with the Ribbon (they've added a few user clicks just to switch windows).
While assisting a member of the board(in this thread), I realized that what we were working on would help resolve this slight nuisance. So I turned it into an AddIn and added the macro to my Quick Access Toolbar.  All that it requires is a UserForm with two ListBoxes.

I'm sure that I am not the first to do this but I figured I'd post it here in case there are one or two other users that would like something like this!

Here's the code:
UserForm Module

```
Private Sub ListBox1_Click()
Dim actvWb, wsName As String
Dim ws As Worksheet
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) Then
            actvWb = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
        End If
    Next i
    
Application.Workbooks(actvWb).Activate

Me.ListBox2.Clear
With Me.ListBox2
    For Each ws In Application.Workbooks(actvWb).Worksheets
        wsName = ws.Name
        .AddItem wsName
    Next
End With

End Sub
'***********************************
Private Sub ListBox2_Click()
Dim actvWs As String
Dim ws As Worksheet
    For i = 0 To Me.ListBox2.ListCount - 1
        If Me.ListBox2.Selected(i) Then
            actvWs = Me.ListBox2.List(Me.ListBox2.ListIndex, 0)
        End If
    Next i
    
Sheets(actvWs).Activate
Unload Me
End Sub
'***********************************
Private Sub UserForm_Initialize()
Dim wbName As String
Dim wb As Object
Dim listQty As Integer
Dim rStart As Range
Set rStart = Sheet1.Range("A:A")

With Me.ListBox1
    For Each wb In Application.Workbooks
        wbName = wb.Name
        .AddItem wbName
    Next
End With
End Sub
```

Probably not the best coding but it was whipped together pretty quickly and is functional (for me anyway).

Cheers!


----------

