Referring to two different open worksheets in code

[Barb Reinhardt]

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]

Is there something unique in that other workbook that you could check? A hidden
name or a nice unique value in one of the cells?

If no, then you could ask the users to click on a cell in one of the worksheets
in the workbook that you want treated as owb.

Tell them to use the Window option on the worksheet menu bar to swap between
workbooks.

Dim oWb as workbook
on error resume next
set owb = application.inputbox(Prompt:="select a cell",type:=8) _
.areas(1).cells(1).parent.parent
on error goto 0

if owb is nothing then
msgbox "nothing clicked"
exit sub '???
end if

 

[Dave Peterson]

Or you could make it that it assumes that the activeworkbook is the workbook you
want??????

 

[Dave Peterson]

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]

I think I like this option the best. It's been a bit since I've done a user
form. How would I populate it with the open workbooks.

 

[Susan]

dave -
i'm glad to see i'm not the only one who keeps thinking AFTER they hit
"send"!

susan

 

[Barb Reinhardt]

Dave,

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

Thanks,
Barb

 

[Dave Peterson]

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.

 

[Dave Peterson]

Those OhnoSeconds are painful--painful to realize and painful to watch!