How can I change a Modal Userform from Modal to Modeless at run time ? !

[Jaafar Tribak]

Hi all,

I am wondering if one can force a Modal UserForm to become Modeless after loading the UserForm.

Load the Userform as Modal => Click on a CommandButton on the Userform => The Userform now becomes Modeless.

Any ideas ?

Regards.

 

[ZVI]

Hi Jaafar,
Try:

Private Sub CommandButton1_Click()
  ' Modeless
  Me.Hide
  Me.Show 0
End Sub

Private Sub CommandButton2_Click()
  ' Modal
  Me.Hide
  Me.Show 1
End Sub

Vladimir

 

[Jaafar Tribak]

That's nice Vladimir. It never occurred to me to hide and show the userform. I tested the code and it works perfect.

one minor issue is the userform doesn't remember its last screen location but that's easy to solve with a bit of code.

Thank you for your help.

Reagards.

 

[ZVI]

Jaafar,
Here is also an API solution without changing of form’s position and without blinking:

' ZVI:2009-08-09 http://www.mrexcel.com/forum/showthread.php?t=408356
' Switching form to Modal / Modeless at runtime
' Put all code to userform code module
Private Declare Function EnableWindow Lib "user32.dll" (ByVal Hwnd As Long, ByVal fEnable As Long) As Long

Const Modal = 0, Modeless = 1

Private Sub CommandButton1_Click()
  ' Modeless
  EnableWindow Application.Hwnd, Modeless
End Sub

Private Sub CommandButton2_Click()
  ' Modal
  EnableWindow Application.Hwnd, Modal
End Sub

Regards,
Vladimir

 

[Jaafar Tribak]

Jaafar,
Here is also an API solution without changing of form’s position and without blinking:

' ZVI:2009-08-09 http://www.mrexcel.com/forum/showthread.php?t=408356
' Switching form to Modal / Modeless at runtime
' Put all code to userform code module
Private Declare Function EnableWindow Lib "user32.dll" (ByVal Hwnd As Long, ByVal fEnable As Long) As Long
 
Const Modal = 0, Modeless = 1
 
Private Sub CommandButton1_Click()
 ' Modeless
 EnableWindow Application.Hwnd, Modeless
End Sub
 
Private Sub CommandButton2_Click()
 ' Modal
 EnableWindow Application.Hwnd, Modal
End Sub

Regards,
Vladimir

Thanks vladimir for the interest.

I knew about using the EnableWindow API approach but although it makes possible to select cells it doesn't permit the selection of menus and more importantly excel loses the keyboard focus meaning you can't edit cells.

The challenge is to make this approach work while still allowing full control over the excel application window.

Regards.

 

[ZVI]

Jaafar,

The mentioned by you limitations are absent if the userform initially is loaded as modeless by the manual setting of its property ShowModal to False, or by loading with vbModeless parameter:

Sub Start()
  UserForm1.Show 0  'vbModeless = 0
End Sub

If modal is required at activation then just add to the code of post #4 this:

Private Sub UserForm_Activate()
  EnableWindow Application.hWnd, Modal
End Sub

Regards,
Vladimir

 

[Jaafar Tribak]

Thanks vladimir.

Maybe i misunderstood you but the EnableWindow API doesn't solve the problem if the userform is initially loaded modal.

The main limitations being the fact that you won't be able to edit the worksheet as you can't set the keyboard focus to the worksheet plus you can't select worksheet menus.

Give it a try and you will see.

I have been trying to solve this challenge for ages but with no luck .

Regards.

 

[ZVI]

Maybe i misunderstood you but the EnableWindow API doesn't solve the problem if the userform is initially loaded modal.

It is true, but you can initially load userform as modeless but in activation event code set it to the modal. In this case you can use ShowWindow API to switch modal / modeless without keyboard blocking and other limitations.

Here is the downloadable working example: ZVI_Modal_Modeless.xls

And its full code:
Code in standard module for loading of UserForm1

Sub Start()
  UserForm1.Show 0  'vbModeless = 0
End Sub

Code in UserForm1 with two command buttons:

' ZVI:2009-08-09 http://www.mrexcel.com/forum/showthread.php?t=408356
' Switching form to Modal / Modeless at runtime
' Code in userform module
Private Declare Function EnableWindow Lib "user32.dll" (ByVal hWnd As Long, ByVal fEnable As Long) As Long

Const Modal = 0, Modeless = 1

Private Sub CommandButton1_Click()
  ' Modeless
  EnableWindow Application.hWnd, Modeless
End Sub

Private Sub CommandButton2_Click()
  ' Modal
  EnableWindow Application.hWnd, Modal
End Sub

Private Sub UserForm_Activate()
  ' Activate as Modal
  EnableWindow Application.hWnd, Modal
End Sub

 

[Norie]

Vladimir

I don't know if it's really relevant to this but there is no hWnd property of the application in earlier versions of Excel.

 

[ZVI]

For safe exiting of user form this additional code is required in UserForm1:

Private Sub UserForm_Terminate()
  ' Safe termination as Modeless
  EnableWindow Application.hWnd, Modeless
  With Me
    .Hide
    .Show 0
  End With
End Sub

The downloadable example is updated now: ZVI_Modal_Modeless_01.xls

Application.Hwnd is available at least in Excel 2002, 2003, 2007.
It can be replaced by GetParent (FindWindow(vbNullString, Me.Caption))
with API functions:

Private Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetParent Lib "user32.dll" (ByVal hWnd As Long) As Long