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

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,493
Office Version
2016
Platform
Windows
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
Hi Jaafar,
Try:
Rich (BB code):

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

Well-known Member
Joined
Dec 5, 2002
Messages
7,493
Office Version
2016
Platform
Windows
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
Jaafar,
Here is also an API solution without changing of form’s position and without blinking:
Rich (BB code):

' 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

Well-known Member
Joined
Dec 5, 2002
Messages
7,493
Office Version
2016
Platform
Windows
Jaafar,
Here is also an API solution without changing of form’s position and without blinking:
Rich (BB code):
' 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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
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:
Rich (BB code):

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:
Rich (BB code):

Private Sub UserForm_Activate()
  EnableWindow Application.hWnd, Modal
End Sub
Regards,
Vladimir
 
Last edited:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,493
Office Version
2016
Platform
Windows
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
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
Rich (BB code):

Sub Start()
  UserForm1.Show 0  'vbModeless = 0
End Sub
Code in UserForm1 with two command buttons:
Rich (BB code):

' 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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
For safe exiting of user form this additional code is required in UserForm1:
Rich (BB code):

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:
Rich (BB code):
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
 
Last edited:

Forum statistics

Threads
1,082,253
Messages
5,364,045
Members
400,776
Latest member
JimmyLee

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top