Prev: drop down fill in
Next: Calendar stuff
From: Budget Programmer on 14 May 2010 16:11
Hello,
How do I display a list of workbooks that the user currently has open, and
choose one from that list, so that I can activate that workbook and continue
with macro processing.
Many Thanks for your help.
--
Programmer on Budget
From: JLGWhiz on 14 May 2010 16:48
Did you read the response to your other post?



"Budget Programmer" <BudgetProgrammer(a)discussions.microsoft.com> wrote in
message news:A97D1C9B-6AE1-4BB3-826C-4C36ADB64CD6(a)microsoft.com...
> Hello,
> How do I display a list of workbooks that the user currently has open, and
> choose one from that list, so that I can activate that workbook and
> continue
> with macro processing.
> Many Thanks for your help.
> --
> Programmer on Budget


From: Gord Dibben on 14 May 2010 17:06
Window>Choose from list of open workbooks.

In 2007 that would be View>Window section>Switch Windows

Assumes workbooks have not been hidden.


Gord Dibben MS Excel MVP

On Fri, 14 May 2010 13:11:01 -0700, Budget Programmer
<BudgetProgrammer(a)discussions.microsoft.com> wrote:

>Hello,
>How do I display a list of workbooks that the user currently has open, and
>choose one from that list, so that I can activate that workbook and continue
>with macro processing.
>Many Thanks for your help.

From: Chip Pearson on 14 May 2010 19:40
Two ways come to mind. First, display a numbered list of open
workbooks and let the user select a workbook by number:

Sub AAA()
Dim N As Long
Dim S As String
Dim WB As Workbook
For Each WB In Workbooks
N = N + 1
S = S & CStr(N) & " " & WB.Name & vbNewLine
Next WB
N = Application.InputBox(prompt:="Select workbook by number." &
vbNewLine & S, _
Type:=1)
If N <= 0 Or N > Workbooks.Count Then
MsgBox "Invalid selection"
Else
MsgBox "You selected: " & Workbooks(N).Name
End If
End Sub

The other way is to have the user click a cell on the workbook he
wants to use. E.g.,

Sub BBB()
Dim R As Range
Dim WB As Workbook
On Error Resume Next
Set R = Application.InputBox(prompt:="Click on the workbook",
Type:=8)
If Err.Number = 0 Then
Set WB = R.Parent.Parent
MsgBox "you clicked workbook: " & WB.Name
Else
MsgBox "invalid"
End If
End Sub


Note that in both procs, the Application.InputBox, rather than VBA's
native InputBox method is called. This makes a difference.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com





On Fri, 14 May 2010 13:11:01 -0700, Budget Programmer
<BudgetProgrammer(a)discussions.microsoft.com> wrote:

>Hello,
>How do I display a list of workbooks that the user currently has open, and
>choose one from that list, so that I can activate that workbook and continue
>with macro processing.
>Many Thanks for your help.
From: Budget Programmer on 2 Jun 2010 15:35
Hi,
My posts seem to take a while to show up. There was never a delay
previously. I thought my request was lost. Chip answered it though. I'm
all set. Thanks.
--
Programmer on Budget


"JLGWhiz" wrote:

> Did you read the response to your other post?
>
>
>
> "Budget Programmer" <BudgetProgrammer(a)discussions.microsoft.com> wrote in
> message news:A97D1C9B-6AE1-4BB3-826C-4C36ADB64CD6(a)microsoft.com...
> > Hello,
> > How do I display a list of workbooks that the user currently has open, and
> > choose one from that list, so that I can activate that workbook and
> > continue
> > with macro processing.
> > Many Thanks for your help.
> > --
> > Programmer on Budget
>
>
> .
>
 | 
Pages: 1
Prev: drop down fill in
Next: Calendar stuff