Show Modeless Form

[Michael Malinsky]

I've been working through the NG trying to perfect this, but to no
avail. I'm trying to show a "Please Wait" modeless form (UserForm2) as
my workbook has numerous occurrences of a UDF that are all recalculated
when a user form (UserForm1) is closed. Here's my code:

Private Sub CommandButton3_Click()

UserForm1.Hide 'since I cannot show a modeless form while a
modal form is showing
UserForm2.Show vbModeless
Unload UserForm1

End Sub

'Code for UserForm2
Private Sub UserForm_Activate()

UserForm2.Caption = "Please wait"
Call Calculate
Unload UserForm2

End Sub

Private Sub Calculate()

Application.Calculate

End Sub
'End code

Thanks,
Mike.

 

[Jim Thomlinson]

While it is not a solution to your modeless issue why not just display a
message right on your UserForm1 asking the user to wait or use the
Application.StatusBar to display a message to the user. That seems a lot
easier.

 

[Michael Malinsky]

You are right, both are easy, but I was looking for "pretty." The
problem I'm running into is getting UserForm1 to unload before
UserForm2 loads.

Any other ideas?

 

[Leith Ross]

Hello Michael

Add a VBA module to project and copy this code into it. In th
*UserForm_Activate()* code add this to call the macro..

MAKENONMODA

Code
-------------------


'Declare API calls and constant
Private Declare Function FindWindow
Lib "User32.dll"
Alias "FindWindowA"
(ByVal lpszClass As String,
ByVal lpszWindow As String) As Lon

Private Declare Function FindWindowEx
Lib "User32.dll"
Alias "FindWindowExA"
(ByVal hWndParent As Long,
ByVal hWndChildAfter As Long,
ByVal lpszClass As String,
ByVal lpszWindow As String) As Lon

Private Declare Function EnableWindow
Lib "User32.dll"
(ByVal hWnd As Long,
ByVal wCmd As Long) As Lon

Private Declare Function GetWindow
Lib "User32.dll"
(ByVal hWnd As Long, ByVal wCmd As Long) As Lon

'Returns the Window Handle of the Active Windo
Public Declare Function GetActiveWindow
Lib "User32.dll" () As Lon

Const GW_OWNER As Long = &H
Const WS_ENABLE As Long = &HFFFFFFF


Public Sub MakeNonModal(

Dim RetVa
Dim hWkb As Lon
Dim hWks As Lon
Dim OwnerWindow As Lon

'Owner Window is Exce
OwnerWindow = GetWindow(GetActiveWindow(), GW_OWNER

'Get Window Handle of Workbook Windo
hWkb = FindWindowEx(OwnerWindow, 0&, "XLDESK", vbNullString

'Get Window Handle to the Worksheet Windo
hWks = FindWindowEx(hWkb, 0&, "EXCEL7", vbNullString

RetVal = EnableWindow(OwnerWindow, WS_ENABLE

End Su