What is the VBA for the Excel 2007 "Switch Window" command button?

[ND Pard]

In Excel 2007, on the View tab, in the Window section, is a Switch Windows
button.

I would like to replicate that feature via VBA. Ie, during a subprocedure
run, I would like it to display the open workbooks and allow the user to
click on one of the results to activate that workbook.

What is the VBA for the Excel 2007 "Switch Window" command button?

Thanks in advance.

 

[Ron de Bruin]

Hi ND Pard

Normal you can use this in 2007 for buttons

Application.CommandBars.ExecuteMso ("WindowSwitchWindowsMenuExcel")

But this is a menu so it is not working
Will think about it this evening

 

[Gary''s Student]

sub pickup()
n = Windows.Count
s = "Pick From:"
For i = 1 To n
s = s & Chr(10) & Windows(i).Caption
Next
t = Application.InputBox(prompt:=s, Type:=2)
Windows(t).Activate
End Sub

 

[Gary''s Student]

sub pickup()
n = Windows.Count
s = "Pick From:"
For i = 1 To n
s = s & Chr(10) & Windows(i).Caption
Next
t = Application.InputBox(prompt:=s, Type:=2)
Windows(t).Activate
End Sub

this should work in 2007 or prior versions.

 

[ND Pard]

I know I could create a form with a list or combobox and populate it with the
names of the open workbooks, and may end up doing so, but I am hoping that a
msopopup like feature is available. I just have had no luck in finding what
I want.

Thanks for your help; I look forward to hearing from you.

 

[ND Pard]

Thanks Gary's Studen. I did make small modifications to your procedure (see
below).

But what I was hoping to do is simply replicate what happens when you click
on the Switch Button.

Sub pickup_modified()
Dim i As Integer
Dim n As Integer
Dim s As String
Dim t As Variant

n = Windows.Count
s = "Pick From:"
For i = 1 To n
s = s & Chr(10) & Windows(i).Caption
Next
t = Application.InputBox(prompt:=s, Type:=2)
If t = False Then
Exit Sub
End If
Windows(t).Activate
End Sub

 

[Jon Peltier]

Application.Windows(1).ActivateNext

- Jon