Using Workbook Index as Condition to Auto Close

Hi, Luke,

where did you place the code? If it´s your personal workbook you will have 3 workbooks open. I would suggest you work with objects on opening the workbooks instead of their index (maybe using GetOpenFilename for the path and name of Workbook).

Ciao,
Holger

Hi, Luke,

maybe try this change which will count the number of workbooks in the instance:

Private Sub Workbook_Open()
If Workbooks.Count > 1 Then
    MsgBox ("PLEASE CLOSE ALL OTHER EXCEL FILES FIRST.")
    ThisWorkbook.Close
End If
End Sub

Maybe you should alter the code to open the second workbook from this procedure.

Private Sub Workbook_Open()
Dim FileToOpen As Variant
If Workbooks.Count > 1 Then
    MsgBox ("PLEASE CLOSE ALL OTHER EXCEL FILES FIRST.")
    ThisWorkbook.Close
Else
  FileToOpen = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
  If FileToOpen <> False Then
    Workbooks.Open FileToOpen
  End If
End If
End Sub

Ciao,
Holger

Originally Posted by lsteinbach

The code is placed in THISWORKBOOK. The reason I'm trying to stay away from file names is because there are around 15 different Master files that will be the first workbook open, then around 20 different subsidiary files that could be the second workbook open. So I'd like to stay away from naming all these different files in the coding. When it comes to coding, my thought process can be somewhat linear, so if I am not understanding what you suggested, can you please provide a brief example?
Also, the computers running these two workbooks will be dedicated kiosks so there is no interference with any other workbooks.
Danke für jede Hilfe (google translate).

Luke

If the "master" workbooks only have the macro then try something like this...

Sub MyMacro()
    
    Dim wbSubsidiary As Workbook
    
    If Workbooks(1) Is ThisWorkbook Then
        Set wbSubsidiary = Workbooks(2)
    Else
        Set wbSubsidiary = Workbooks(1)
    End If
    
    MsgBox "Master workbook = " & ThisWorkbook.Name & vbLf & _
           "Subsidiary workbook = " & wbSubsidiary.Name
        
End Sub

Alternatively, you could loop through each opened workbook and look for a difference (cell.value, sheet name) that distinguishes a Master from a Subsidiary.