2

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.

Community
  • 1
  • 1
Mertinc
  • 793
  • 2
  • 13
  • 27

3 Answers3

3

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
jkpieterse
  • 2,727
  • 1
  • 9
  • 18
  • the logic is wonderful but I wasn't able to use the routine how can I apply this? My code is in workbook_open event (the real code is in my previous post which I have mentioned in the beginning of this question) I tried to use this routine as separate and called it into the workbook_open event (instead of workbook.name.visible = true line) but didn't work. I also tried to define the workbook name as a string name and then tried calling this name but it didn't work too. – Mertinc May 02 '17 at 14:00
  • Instead of Windows(ThisWorkbook.Name).Visible = True you use ShowaWindow(ThisWorkbook.Name) – jkpieterse May 02 '17 at 20:42
  • Wow, that works like a charm! And could you please explain what did those codes exactly do? Why simply `Dim name1 as String name 1= ThisWorkbook.name Windows(name1).Visible = True` code didn't work? (I also voted your answer as useful but my point is not enough to increase it sorry for that. Thanks for solving my this big issue) – Mertinc May 03 '17 at 01:04
  • My code works because it first finds the workbook object itself and then simply activates its first window, rather than depending on activating a window by using its caption (the mehtod you used), which may or may not be identical to the name of the workbook you want to activate. – jkpieterse May 03 '17 at 14:17
  • What's the point of looping through `Workbooks` to find the `oWb` with name matching `ThisWorkbook.Name` if you have the reference to the workbook you need (`ThisWorkbook`) to begin with? I think my answer below is cleaner. – Nickolay Nov 08 '17 at 10:41
  • Upvoted for the reference to `Workbook.Windows()` though -- I didn't know that one existed, thanks! – Nickolay Nov 08 '17 at 10:42
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)
Nickolay
  • 31,095
  • 13
  • 107
  • 185
  • Thanks for this wide answer Nickolay, that's very clear one. @jkpieterse 's solution was also based on workbook.windows(1) which already solved the issue. – Mertinc Nov 09 '17 at 10:57
  • Right, all credit for the solution goes to @jkpieterse. I wrote this answer to have somewhere to point to from the commit message, since there seemed to be no satisfying description of how this works anywhere.. – Nickolay Nov 09 '17 at 12:34
  • 1
    I like that you avoid `Windows("MyWorkbook")` but I think iterating through the relevant windows is a more general solution (encompassing the special case of one window only), as I wrote in my answer: https://stackoverflow.com/a/58047995/6306782 – z32a7ul Sep 22 '19 at 09:47
  • @z32a7ul Well, sure, if you have the time and need to properly support multiple windows - go for it! In my case I had a number of uses of `Windows`, including some unmaintained code and hacks like [this](https://stackoverflow.com/a/51601399/1026), so I decided to go with the warning. – Nickolay Sep 23 '19 at 22:49
0

In case of Win10-Excel 2016 combination WorkBook.Activate before WorkBook.Parent.Windows.(WoorkBook.Name).Visible = True

Did the trick for error '9'

mario marcec
  • 1
  • 1