Windows(ThisWorkbook.Name).Visible = True causes e

Question

2 months ago I've asked this question: Autosaved file error on Macro Enabled Excel file

Thanks to @YowE3K he referred me to Workbook_Open event as the problem was occurring when I try to recover(re-open) the file. Today I deeply focused on it and found that the reason I was getting:

 Run-time error '9'

 Subscript out of range

error is:

Windows(ThisWorkbook.Name).Visible = True

I don't know how it became False but, when I try to open auto-recover file, I realize that file is opening normally, my form is opening normally, but when form disappears, the error comes through, the name of the file changes to Microsoft Excel, everything disappear from screen, all of the Excel ribbon freeze, I can only go to Developer tab, nothing changes the situation.

So I think that,excel somehow can't turn Windows(ThisWorkbook.Name).Visible to True after Form (my Excel Splash Screen) disappears.

How can I avoid this? I don't want to delete my fancy splash screen but I sometimes need to use auto-recover files as well.

Similar issue from Microsoft's page and XL2003: Changing ThisWorkbook.Windows().Visible property during Workbook_BeforeClose event prevents workbook from closing are not helping me at all.

Answer 1

The code fails because when Excel recovers a file, it adds some text to the caption of the window, so that "FileName.xlsx" becomes something like "FileName.xlsx [Version last saved by user]". So use a routine like:

Sub ShowaWindow(sFileName As String)
    Dim oWb as Workbook
    For Each oWb In Workbooks
        If lCase(owb.Name) = lCase(sFileName) Then
            oWb.Windows(1).Visible = True
            Exit For
        End If
    Next
End Sub

Answer 2

Windows(ThisWorkbook.Name) is a common, yet utterly wrong way to get the workbook's window, which will give you Run-time error '9' Subscript out of range sooner or later.

The right solution (I think, we'll see how it goes) is to use the Workbook.Windows() collection.

Since Excel supports multiple windows ("views" into the workbook, see an explanation here), doing it the right way requires thinking about which window or windows you need to operate on. In particular, ActiveSheet may be different in two different windows for the same workbook...

Given that most people are unaware of this functionality, I decided to always use the first window (Workbook.Windows(1)), like this:

Private Sub FreezePanes()
    With ThisWorkbook.Windows(1)
        If .FreezePanes Then .FreezePanes = False
        .SplitColumn = 0
        .SplitRow = 1
        .FreezePanes = True
    End With
End Sub

To ensure this doesn't lead to weird results, I wanted to do something when multiple windows are opened for my workbook.

Initially I tried to close the extra windows just before accessing wb.Windows(1), but quickly found that closing the wrong window and continuing to run VBA code can lead to Excel crashing and decided to take a safer approach: before doing anything else I check if there are multiple windows for the workbook, and if there are, suggest to close them and ask the user to try again:

Public Function CheckForExtraWindowsAndWarn(wb As Workbook) As Boolean
    If wb.Windows.Count > 1 Then
        If MsgBox("... Close the extra windows?", vbQuestion + vbYesNo, APP_TITLE) = vbYes Then
            While wb.Windows.Count > 1
                wb.Windows(wb.Windows.Count).Close
            Wend
        End If
        CheckForExtraWindowsAndWarn = True
    Else
        CheckForExtraWindowsAndWarn = False
    End If
End Function

' in the top-level macro, before doing anything:
If CheckForExtraWindowsAndWarn(ThisWorkbook) Then Exit Sub

Related information:

• When accessing the Application.Windows() collection via a string index, it appears to look up the window by its Caption (the best source on this is this sentence from the documentation: "This example names window one in the active workbook "Consolidated Balance Sheet." This name is then used as the index to the Windows collection."). When multiple windows have the same Caption, the returned window seems to be the most recently active.
• Run-time error '9' Subscript out of range error is known to happen when you try to use Windows(ThisWorkbook.Name) and:
  • The workbook was "repaired" (since Excel indicates that in Caption: "WorkbookName [Repaired]")
  • If multiple windows with default captions are opened for the workbook (since their captions are: "WorkbookName:1", "..:2", and so on)
  • If "Hide extensions of known file types" is enabled in Windows explorer's settings (I couldn't reproduce this one though)