How to avoid a userform running in hidden mode, to be closed by other workbooks?

[keromero]

I have managed a userform connected excel sheet running in "vbModeless mode" and "hidden" by means of below code:

Set xl = Application
ThisWorkbook.Windows(1).Visible = False
If Workbooks.count = 1 Then
xl.Visible = False
Else
xl.Visible = True
End If

This form of user-form operation, imitates a stand-alone application as the excel window is hidden.

All sorted fine.. I can work on other workbooks at the same time while userform is showing.
Just one important point left.

When I want to close any other workbook by excel window (x close) it asks me whether to save the userfrom related workbook or not. In any case consequently exits user-from connected workbook aswell.

Is there any way to avoid other workbooks to close userform connected hidden workbook while quitting?

Best Regards,

 

[bobsan42]

To get things straight: it is not the other workbooks which are closing the hidden one, you are. You are quitting the application by pressing the X on the last visible window.
So if you close the application - all workbooks are closed, including the hidden one.
- If you press Cancel when you get the Save prompt then you hidden wb will not be closed.
- if you use Ctrl+W to close workbooks you will not close the application.
- another option is to use the Workbook BeforeClose event and cancel it conditionally, something like:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If some_condition Then 'for example if the resident userform is loaded
        Cancel = True
    End If
End Sub

 

[Logit]

Here is a small part of the code in my workbook project that should do what you want. Open the VBE to view the remainder of the code, where it is located and how it
interacts within the workbook.

Option Explicit

Private Sub CommandButton1_Click()
UserForm1.Hide
    Application.ScreenUpdating = False
    ThisWorkbook.Application.Visible = False
    ThisWorkbook.Application.WindowState = xlMinimized
    Workbooks("Open UserForm Only.xlsm").Close     '<---- change name of workbook here
    Application.ScreenUpdating = True
End Sub

Download workbook : Internxt Drive – Private & Secure Cloud Storage

 

[keromero]

To get things straight: it is not the other workbooks which are closing the hidden one, you are. You are quitting the application by pressing the X on the last visible window.
So if you close the application - all workbooks are closed, including the hidden one.
- If you press Cancel when you get the Save prompt then you hidden wb will not be closed.
- if you use Ctrl+W to close workbooks you will not close the application.
- another option is to use the Workbook BeforeClose event and cancel it conditionally, something like:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If some_condition Then 'for example if the resident userform is loaded
        Cancel = True
    End If
End Sub

Hi Bobsa, you are definitely right.
quitting the application by pressing the X on the last visible window makes the issue...

I have managed not the quit user form connected to "ThisWorkbook.Windows(1).Visible = False" workbook by any else workbook's quit event ( by pressing the X on the last visible window)

In case there is no change made for the else workbook and excel does not ask you to save / not save or cancel by following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Set xl = Application
If UserForms.count = 0 Then
Application.Quit
Else
ActiveWorkbook.Close False
ThisWorkbook.Windows(1).Visible = False
xl.Visible = False
End If
End Sub

However in the event of change in the workbook and excel asks to save / not save or cancel during qıit event of other workbook

Cancel = True

does not help.

We need in any time during quit event to block excel asking whether to: "save / not save or cancel" for the particular hidden workbook.

 

[bobsan42]

I am not sure exactly what your concept is, but try this in ThisWorkbook module of the hidden workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If UserForms.Count > 0 Then
        Cancel = True
    End If
End Sub

This should just cancel the Close without asking you anything.

 

[Jaafar Tribak]

Hi keromero,

This is an elusive and frustrating bug associated with the new Single Document Interface (SDI) feature introduced in Excel 2013. It has been widely reported by users experiencing similar issues. Unfortunately, I have yet to come across a reliable solution.

To observe the bug, follow these steps: First, open a modeless userform and hide the parent workbook (using ThisWorkbook.Windows(1).Visible = False). Next, open a new workbook, make some changes, and attempt to close the unsaved workbook. After performing these actions, you will be unexpectedly prompted to save both the newly opened workbook and the parent workbook of the userform.

In fact, there is an even more troublesome bug: When a modeless userform is displayed while another workbook is active, closing the latter will unexpectedly cause the userform to unload !!

In my view, there are two potential workarounds for this issue:

1. Open the userform and the other workbooks in separate Excel instances. While this solution may fix the problem, it does come with the drawback of complicating communication between the instances.
   
   
2. Rather than setting the Visible property of the userform's parent workbook to FALSE, implement a workaround\hacky trick by "faking" the hide operation with code that removes the clipping region. This can help avoid the issue while maintaining better control.
   ​

I have written a small class to simulate the workbook being hidden. It worked for me in both Excel 2013 (32-bit) and Excel 2016 (64-bit). However, I am unsure of how it will behave on other platforms.

Workbook Example:
FormParentFakeHide.xlsm


1- Class Code (C_StickyForm)

Option Explicit

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As Long
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As Long
        Private Const NULL_PTR = 0^
    #Else
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
        Private Const NULL_PTR = 0&
    #End If
    Private Declare PtrSafe Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function SetActiveWindow Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Private Declare PtrSafe Function ShowWindowAsync Lib "user32" (ByVal hwnd As LongPtr, ByVal nCmdShow As Long) As Long
    Private Declare PtrSafe Function IsIconic Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function CoLockObjectExternal Lib "ole32.dll" (ByVal punk As IUnknown, ByVal fLock As Boolean, Optional ByVal fLastUnlockReleases As Boolean) As Long
    Private Declare PtrSafe Function CreateRectRgn Lib "gdi32" (ByVal X1 As Long, ByVal Y1 As Long, ByVal X2 As Long, ByVal Y2 As Long) As LongPtr
    Private Declare PtrSafe Function SetWindowRgn Lib "user32" (ByVal hwnd As LongPtr, ByVal hRgn As LongPtr, ByVal bRedraw As Long) As Long
    Private Declare PtrSafe Function DeleteObject Lib "gdi32" (ByVal hObject As LongPtr) As Long
#Else
    Private Enum LongPtr
        [_]
    End Enum
    Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
    Private Declare Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hwnd As LongPtr) As Long
    Private Declare Function SetActiveWindow Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Private Declare Function ShowWindowAsync Lib "user32" (ByVal hwnd As LongPtr, ByVal nCmdShow As Long) As Long
    Private Declare Function IsIconic Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare Function CoLockObjectExternal Lib "ole32.dll" (ByVal punk As IUnknown, ByVal fLock As Boolean, Optional ByVal fLastUnlockReleases As Boolean) As Long
    Private Declare Function CreateRectRgn Lib "gdi32" (ByVal X1 As Long, ByVal Y1 As Long, ByVal X2 As Long, ByVal Y2 As Long) As LongPtr
    Private Declare Function SetWindowRgn Lib "user32" (ByVal hwnd As LongPtr, ByVal hRgn As LongPtr, ByVal bRedraw As Long) As Long
    Private Declare Function DeleteObject Lib "gdi32" (ByVal hObject As LongPtr) As Long
    Private Const NULL_PTR = 0&
#End If

Private WithEvents AppEvents As Application
Private WithEvents cdbrsEvents As CommandBars

Private oForm As Object
Private hOwnerHwnd As LongPtr


Public Sub Init(Form As Object)
    If Val(Application.Version) >= 15& Then
        hOwnerHwnd = ThisWorkbook.Windows(1&).hwnd
        Call FakeHide(ThisWorkbook.Windows(1&), True)
        Call CoLockObjectExternal(Me, True)
        Set oForm = Form
        Set AppEvents = Application
        Set cdbrsEvents = Application.CommandBars
    End If
End Sub

Private Sub AppEvents_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
    Dim hwnd As LongPtr
    Call IUnknown_GetWindow(oForm, VarPtr(hwnd))
    If IsIconic(Wn.hwnd) Then
        Wn.WindowState = xlNormal
    End If
    Call SetOwner(hwnd, Wn.hwnd)
    Call SetActiveWindow(hwnd)
    Call ShowWindowAsync(hwnd, 1&)
    Set cdbrsEvents = Wn.Parent.Parent.CommandBars
End Sub

Private Sub AppEvents_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    Dim hwnd As LongPtr
    Call IUnknown_GetWindow(oForm, VarPtr(hwnd))
    If Wb Is ThisWorkbook Then
        Call SetOwner(hwnd, NULL_PTR)
        Call FreeMemory
    Else
        Call SetOwner(hwnd, hOwnerHwnd)
    End If
End Sub

Private Sub cdbrsEvents_OnUpdate()
    If FormExists = False Then
        Call FreeMemory
    End If
End Sub

Private Sub SetOwner(ByVal hwnd As LongPtr, Owner As LongPtr)
    Const GWL_HWNDPARENT = (-8&)
    Call SetWindowLong(hwnd, GWL_HWNDPARENT, Owner)
End Sub

Private Function FormExists() As Boolean
    Dim oUf As Object
    For Each oUf In VBA.UserForms
        If oForm Is oUf Then
            FormExists = True: Exit Function
        End If
    Next oUf
End Function

Private Sub FakeHide(ByVal wnd As Window, ByVal bClipped As Boolean)
    Call SetClipRegion(wnd.hwnd, bClipped)
    Call SetStyles(wnd.hwnd, bClipped)
End Sub

Private Sub SetClipRegion(ByVal hwnd As LongPtr, ByVal bClipped As Boolean)
    Dim hRgn As LongPtr
    If bClipped Then
        hRgn = CreateRectRgn(0&, 0&, 1&, 1&)
        Call SetWindowRgn(hwnd, hRgn, True)
        Call DeleteObject(hRgn)
    Else
        Call SetWindowRgn(hwnd, NULL_PTR, True)
    End If
End Sub

Private Sub SetStyles(ByVal hwnd As LongPtr, ByVal bSet As Boolean)

    Const GWL_EXSTYLE = (-20&), GWL_STYLE = (-16&)
    Const WS_EX_TOOLWINDOW = &H80&, WS_EX_LAYERED = &H80000, WS_THICKFRAME = &H40000
    Dim lStyle As Long, lExStyle As Long
    
    lStyle = GetWindowLong(hwnd, GWL_STYLE)
    lExStyle = GetWindowLong(hwnd, GWL_EXSTYLE)
    If bSet Then
        lStyle = lStyle And Not WS_THICKFRAME
        lExStyle = (lExStyle Or WS_EX_TOOLWINDOW) Or WS_EX_LAYERED
    Else
        lStyle = lStyle Or WS_THICKFRAME
        lExStyle = (lExStyle And Not WS_EX_TOOLWINDOW) And Not WS_EX_LAYERED
    End If
    Call SetWindowLong(hwnd, GWL_EXSTYLE, lExStyle)
    Call SetWindowLong(hwnd, GWL_STYLE, lStyle)

End Sub

Private Sub FreeMemory()
    Call CoLockObjectExternal(Me, False)
    Set oForm = Nothing
    Set cdbrsEvents = Nothing
    Debug.Print "Mem released."
    Call FakeHide(ThisWorkbook.Windows(1), False)
End Sub

2- Code Usage in the UserForm Module:

Option Explicit

Private Sub UserForm_Initialize()
    Dim oStickyForm As C_StickyForm
    Set oStickyForm = New C_StickyForm
    oStickyForm.Init Me
End Sub