Prev: Converting a “Delete Column” Macro to a “Delete Row” Macro
Next: Going through Named Ranges in only specific Sheets in my WorkBook
From: Dave Peterson on 15 May 2010 10:50
One more...

I created a small userform with a combobox and two commandbuttons.

This was the code behind the userform:

Option Explicit
Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex < 0 Then
Me.CommandButton2.Enabled = False
Else
Me.CommandButton2.Enabled = True
End If
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Application.Workbooks(Me.ComboBox1.Value).Activate
End Sub
Private Sub UserForm_Initialize()

Dim myWin As Window
Dim wkbk As Workbook

With Me.ComboBox1
.Style = fmStyleDropDownList
End With

With Me.CommandButton1
.Caption = "Cancel"
.Enabled = True
.Cancel = True
.TakeFocusOnClick = False
End With

With Me.CommandButton2
.Enabled = False
.Default = True
.Caption = "Activate Workbook"
.TakeFocusOnClick = False
End With

Me.Caption = "Please select a workbook"

For Each wkbk In Application.Workbooks
For Each myWin In wkbk.Windows
If myWin.Visible = True Then
Me.ComboBox1.AddItem wkbk.Name
Exit For
End If
Next myWin
Next wkbk

End Sub



On 05/13/2010 15:49, Budget Programmer wrote:
> Hello,
>
> I need to get a list of all the workbooks that the user currently has open,
> give that list to the user, let them select one, and then Activate that
> selected workbook and continue with processing.
> I can generate a list of workbooks in a given directory, but the requirement
> here is to get a list of workbooks that are currently open.
>
> Many thanks for all your help.
> Phil
From: Budget Programmer on 2 Jun 2010 15:32
Hi Chip,
Sorry it took me a while to get back to you. My priorities were changed for
a little while.
Your suggestion did the trick. Many Thanks!
--
Programmer on Budget


"Chip Pearson" wrote:

> The following function will prompt the user to select a workbook by
> number and if a valid selection is made, return the name of the
> workbook. If an invalid selection is made, the result is vbNullString.
>
>
> Function PromptForWorkbook() As String
> 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 a workbook by number." & _
> vbNewLine & S, Type:=1)
> If N <= 0 Or N > Workbooks.Count Then
> PromptForWorkbook = vbNullString
> Else
> PromptForWorkbook = Workbooks(N).Name
> End If
> End Function
>
>
> You can use this in code like
>
> Sub AAA()
> Dim T As String
> T = PromptForWorkbook
> If T = vbNullString Then
> MsgBox "user cancel"
> Else
> Workbooks(T).Activate
> End If
> End Sub
>
>
>
>
> On Thu, 13 May 2010 13:49:01 -0700, Budget Programmer
> <BudgetProgrammer(a)discussions.microsoft.com> wrote:
>
> >Hello,
> >
> >I need to get a list of all the workbooks that the user currently has open,
> >give that list to the user, let them select one, and then Activate that
> >selected workbook and continue with processing.
> >I can generate a list of workbooks in a given directory, but the requirement
> >here is to get a list of workbooks that are currently open.
> >
> >Many thanks for all your help.
> >Phil
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Converting a “Delete Column” Macro to a “Delete Row” Macro
Next: Going through Named Ranges in only specific Sheets in my WorkBook