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.

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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"ND Pard" wrote in message ...
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.

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 - gsnu200836


"ND Pard" wrote:

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.

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

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.
--
Gary''s Student - gsnu200836


"ND Pard" wrote:

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.

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" wrote:

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.

Application.Windows(1).ActivateNext

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"ND Pard" wrote in message
...
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.