2

As title. This is driving me mad and I can't find any reference to it anywhere else on the web.

I have a VBA-laden workbook which is normally hidden through use of:

ThisWorkbook.Windows(1).Visible = False

However, when the workbook is closed I need it to revert to a default state whereby its window is visible and a "special" worksheet which instructs the user on enabling macros is shown. This is to provide guidance should the next user open the workbook with macros disabled. When the workbook is opened with macros enabled, the Workbook_Open event takes care of hiding this message again, at which point normal operation proceeds.

My problem is that when I change the .Windows().Visible property from the workbook's _BeforeClose event (which only occurs whenever Excel is quitting) the workbook simply remains on screen. I have confirmed that it is definitely setting the .Windows().Visible property that is causing this behaviour.

I have tried using the ThisWorkbook.IsAddin property, since this has the same effect of "hiding" the workbook from the user's view, but that causes exactly the same behaviour.

I have also tried simply not changing the window's visibility in any way, in the hope that subsequently opening the workbook with macros disabled will cause the property to be ignored, but this is not the case.

The only thing that I can do to cause the desired behaviour is to have ThisWorkbook.Close as the last line of the _BeforeClose event itself, but this causes instability which manifests as an occasional "Excel has closed unexpectedly, do you want to recover/restart?" message after Excel has quit.

Any help/pointers gratefully accepted. I don't have access to a later version of Excel.

My code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.ScreenUpdating = False

    Call cleanUp

    Application.ScreenUpdating = True

End Sub

and

Private Sub cleanUp()

Dim s As Worksheet

    shtZNM.Visible = xlSheetVisible

    For Each s In ThisWorkbook.Sheets
        If Not s Is shtZNM Then s.Visible = xlSheetHidden
    Next s

    With ThisWorkbook.Windows(1)
        .DisplayWorkbookTabs = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
        .Visible = True '<==REMOVING THIS ALLOWS _BEFORECLOSE TO WORK AS EXPECTED
    End With

    On Error Resume Next
    Application.CommandBars("QCS").Delete
    Err.Clear
    Application.CommandBars("Worksheet Menu Bar").Controls("QCS").Delete

    Me.Save

End Sub
Community
  • 1
  • 1
blackworx
  • 537
  • 5
  • 21
  • Running hidden excel instances or trying to run it without a UI is generally a recipe for trouble... see http://support.microsoft.com/kb/257757 – James Snell Feb 13 '13 at 13:49
  • Hi James and thanks for your response. I'm not actually hiding Excel or trying to remove its UI - I am only hiding a *workbook* window within Excel. Sorry if this was not clear from my question. – blackworx Feb 13 '13 at 14:17
  • Incidentally one option with this is that if you change the visible property then you should cancel the close event and reschedule it (perhaps using ontime to delay the execution for a second to finish off). I don't have code or I'd post it as an answer. – James Snell Feb 13 '13 at 14:37
  • my bad, it was clear I was just being a bit hard of thinking. Though generally hiding stuff across macros and events like this is still troublesome as you're finding out. – James Snell Feb 13 '13 at 14:39
  • Yeah I have a bit of experience with it over the years and I'm getting to the stage where I'm just fed up with it. If the workbook was mine to redesign I'd do away with the requirement somehow but that's not an option. Regarding OnTime: it can come to the rescue in many situations even if it does make things a bit kludgey, but in this case I can't use it. Because the problematic workbook is hidden, the only way the user normally has of closing it is by quitting Excel. As a result, any ontime solution would have to programatically quit Excel, which I definitely want to avoid. – blackworx Feb 13 '13 at 15:02

0 Answers0