And if you come back...

I created a userform with a combobox and two buttons (ok/cancel).

This code was in a General module:
Option Explicit
Public OtherWorkbook As Workbook
Sub testme()
Set OtherWorkbook = Nothing
UserForm1.Show
If OtherWorkbook Is Nothing Then
Beep
Else
MsgBox OtherWorkbook.Name
End If
End Sub

This code is behind the userform:
Option Explicit
Private Sub ComboBox1_Change()
Me.CommandButton1.Enabled = CBool(Me.ComboBox1.ListIndex -1)
End Sub
Private Sub CommandButton1_Click()
Set OtherWorkbook = Application.Workbooks(Me.ComboBox1.Value)
Unload Me
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim wkbk As Workbook
For Each wkbk In Application.Workbooks
If IsAWindowVisible(wkbk) Then
Me.ComboBox1.AddItem wkbk.Name
End If
Next wkbk
With Me.CommandButton1
.Enabled = False
.Caption = "Ok"
End With
With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
End With
End Sub
Function IsAWindowVisible(wkbk As Workbook) As Boolean
Dim myWindow As Window
IsAWindowVisible = False
For Each myWindow In wkbk.Windows
If myWindow.Visible = True Then
IsAWindowVisible = True
Exit For
End If
Next myWindow
End Function

You could add what ever you needed to--so you could avoid certain workbooks--or
include only certain workbooks. I only chose those workbooks that had at least
one visible window.



Barb Reinhardt wrote:

Dave,

I think I have something. If I have specific questions, I'll come back

Thanks,
Barb

"Dave Peterson" wrote:

Or you could pop up a userform that has a combobox with all the open workbooks.
Then tell the user to select from that dropdown.

Barb Reinhardt wrote:

I'm in a bit of a quandry and I'm sure someone here can help me out.

I have the following snippet of code:

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xls"
.FilterIndex = 1
.Title = "Please Select a PIID Workbook to open"
If .Show = False Then Exit Sub
sFile = .SelectedItems(1)
End With

ShortName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))

If ShortName = aWB.Name Then
MsgBox ("You've chosen to update the active workbook." & vbNewLine & _
"Choose another workbook to update")
GoTo EndSub

End If

On Error Resume Next
Set oPIIDWB = Nothing
Set oPIIDWB = Workbooks(ShortName)
On Error GoTo 0

If oPIIDWB Is Nothing Then
Application.StatusBar = "Opening " & ShortName
Set oPIIDWB = Workbooks.Open(sFile, UpdateLinks:=False)
End If

and this works if I'm opening the PIID workbook from somewhere on my
computer. HOWEVER ... this workbook is stored on a TeamSite. I run the
code from a workbook I'm referring to as aWB. How do I identify another
open workbook as OWB without opening it programmatically.

Thanks,
Barb Reinhardt

--

Dave Peterson


--

Dave Peterson