User form goes behind Excel Window When Message Box Pops Up

[keromero]

I have managed to code a standalone like VBA userform, by the great help of MrExcel professional users assistance and almost done.

it seems Just one issue left to manage.

The user form works such as a standalone application by the help of "Windows.Visible = False" command:

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

xl.Visible = False
Else
xl.Visible = True
End If

As long as the excel sheet fires and the: "Windows.Visible" goes to False mode.

Any message box pops up, shows over userform without an issue.

However in case another excel file (workbook) is opened and screen layering states as follows:

Top: Userform
Behind : Other Excel Workbook's Excel window

And a user-form related message box pops up; then userform goes behind the: "Other Workbook's Excel window". And the screen layering is as follows until message box is closed:

Top: Userform related message box
Middle : Other Workbook's Excel window
Behind: User-Form

How can we ensure all the time: "Other Workbook's Excel window" to stay behind of userform and userform related message box during any user-from related message box popup like follows:

Top: Userform related message box
Middle : User-Form
Behind: Other Workbook's Excel window

Thanks for the help.

 

[Jaafar Tribak]

Some time ago, I wrote some code to ensure that a userform always stays on top regardless of the active workbook.
Check out this post and see if you can adapt it to your specific needs.

 

[keromero]

Hi Jaafar, thanks for the prompt response. Below is simplified form of the generated code by yourself earlier.
Code works very well. However this code shows "all the time" on top.

So what I need, userform to stay top when a message box appears. I tired to move the code i put in "userform initialize" to message box calling event no success.
Is there a way such as putting the code:


Private mclsFormOnTop As cFormOnTop

Private Sub UserForm_Initialize()
Set mclsFormOnTop = New cFormOnTop
Set mclsFormOnTop.TheUserform = Me
mclsFormOnTop.InitializeMe
End Sub

into a module or a different method please.

Thanks & Best REgards,

In a class called cFormOnTop, add this code:

Option Explicit

'Object variable to trigger application events
Private WithEvents XLApp As Excel.Application

#If VBA7 Then
Dim mXLHwnd As LongPtr 'Excel's window handle
Dim mhwndForm As LongPtr 'The userform's window handle
Private Declare PtrSafe Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
#If Win64 Then
Private Declare PtrSafe Function SetWindowLongA Lib "user32" Alias "SetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
#Else
Private Declare PtrSafe Function SetWindowLongA Lib "user32" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
#End If
Private Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
#Else
Dim mXLHwnd As Long 'Excel's window handle
Dim mhwndForm As Long 'The userform's window handle
Private Declare Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SetWindowLongA Lib "user32" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
#End If

Const GWL_HWNDPARENT As Long = -8

Private moTheUserform As Object

Public Sub InitializeMe()
If Val(Application.Version) >= 15 Then 'Only makes sense on Excel 2013 and up
Set XLApp = Application
End If
End Sub

Private Sub Class_Terminate()
Set XLApp = Nothing
Set moTheUserform = Nothing
End Sub

Private Sub XLApp_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
If Val(Application.Version) >= 15 And mhwndForm <> 0 Then 'Basear o form na janela ativa do Excel.
mXLHwnd = Application.hwnd 'Always get because in Excel 15 SDI each wb has its window with different handle.
SetWindowLongA mhwndForm, GWL_HWNDPARENT, mXLHwnd
SetForegroundWindow mhwndForm
End If
End Sub

Private Sub XLApp_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window)
If Not moTheUserform.Visible Then moTheUserform.Show vbModeless
End Sub

Private Sub XLApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
SetWindowLongA mhwndForm, GWL_HWNDPARENT, 0&
End Sub

Public Property Set TheUserform(ByVal oNewValue As Object)
Set moTheUserform = oNewValue
mhwndForm = FindWindowA("ThunderDFrame", moTheUserform.Caption)
End Property


After adding this class to your project, this is all code you need to add to your userform

Private mclsFormOnTop As cFormOnTop

Private Sub UserForm_Initialize()
Set mclsFormOnTop = New cFormOnTop
Set mclsFormOnTop.TheUserform = Me
mclsFormOnTop.InitializeMe
End Sub

 

[Jaafar Tribak]

Who is calling the Msgbox? The UsrForm or some code in a different workbook?

 

[keromero]

Hi Jaafar, this below code in a "ComboBox Change Event" which is in "the same worksheet" calling the message box. As per your past advise I used application on time delay (to allow enough fold-up time for combobox) and put msgbox in a separate module.

Private Sub ComboBox9_Change()

If InStr(ComboBox9.Text, "!") Then

Application.OnTime Now + TimeValue("00:00:01"), "MesajMacro"

End If

End Sub


Public Sub MesajMacro()

'MsgBox "Insufficient Refrigeration Capacity!!!....", vbInformation, "Acolator Units"

End Sub

 

[Jaafar Tribak]

Is ComboBox9 embedded in a worksheet or on the userform ? and if it is embedded on a worksheet, is it a worksheet in the workbook that has the userform or on a worksheet in a separate workbook ?
We need to have more specific information.

 

[keromero]

Hi Jaafar, ComboBox9 is embedded on the userform. More detaily, the worksheet holds no VBA element. Only database to feed userform.

The userform is running like a stand alone tool by means of:

Private Sub Workbook_Open()

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

' Runs when the workbook is opened or immediately after macros are enabled for the first time
' after the workbook is opened.
'
' Created by: TeachExcel.com
'

' Helper to show you when it runs.
'MsgBox "Open"

' Make the macro run faster by disabling screen updating
Application.ScreenUpdating = False

' Make sure no other events like this one run while we are working here
Application.EnableEvents = False

' Call the macro to show the worksheets
Call Show_Sheets

' Enable screen updating so Excel works like normal.
Application.ScreenUpdating = True
' Enable events again so everything works as expected
Application.EnableEvents = True

End Sub

So all the userform stands windows visible = false mode.

Everything operates fine until I load another workbook. In this case the other workbook's window acts like a parent screen and each time a message box shows, userform goes behind the other workbook's excel window and message box stays at top until I hit the messagebox "ok" then userform comes back to top.

There should be a trick to resolve this.

Instead of message boxes I tried to imitate message box by means of a new "user form
(replace a message box) but nothing changed.

 

[keromero]

userform holding Combobox9 is operating at "vbModeless"

Why I need this, as during userbox operation other workbooks shall be accessible and operable.

 

[keromero]

Basically a pop-up message or a sub user form connected to a: "parent user-form" which is running at a windows visible false mode tends to bind itself to else workbooks window as "parent"
This is something we need to overcome I believe.

 

[Jaafar Tribak]

Hi keromero,
Sorry for not responding sooner. I was busy with some little vba project.
I will take a look later on and will get back to you.