' Switching form to Modal / Modeless at runtime
' ZVI:2009-08-09 http://www.mrexcel.com/forum/excel-questions/408356-how-can-i-change-modal-userform-modal-modeless-run-time.html
' ZVI:2018-09-15 added 64 bit compatibility, fixed code.
' A bit modified code of Jan Karel Pieterse is used for switching form between workbooks in Excel 2013+
' see more details in his web page https://www.jkp-ads.com/Articles/keepuserformontop02.asp
' Code of UserForm with controls:
' ComboBox1 - to show modal form in another workbook
' CommandButton1 - set form to modal mode
' CommandButton2 - set form to modeless mode
' CommandButton3 - close form
Option Explicit
#If VBA7 Then
Private Declare PtrSafe Function EnableWindow Lib "user32" (ByVal hWnd As LongPtr, ByVal fEnable As Long) As Long
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
Private Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal hWnd As LongPtr) As Long
Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
#Else
Private Declare Function EnableWindow Lib "user32.dll" (ByVal hWnd As Long, ByVal fEnable As Long) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
#End If
Dim WithEvents App As Application
Dim FrmHwnd As Variant
Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
' Repopulate ComboBox1
ComboBox1_DropButtonClick
' Attach form to Wn
AttachFormToWindow Wn
End Sub
Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
#If VBA7 Then
' Prevent form closing at closing of active workbook
SetWindowLong FrmHwnd, -8&, 0&
#End If
End Sub
Private Sub UserForm_Initialize()
' For catching a window activating
Set App = Application
' Save wibdow's handle of the UserForm
FrmHwnd = FindWindow("ThunderDFrame", Me.Caption)
' Populate ComboBox1 by the names of visible workbooks for navigation in modal mode
ComboBox1_DropButtonClick
'Caption
Me.Caption = Me.Caption & " [Modeless]"
' Uncomment the below line to initialize modal mode
'SetModal True
End Sub
Private Sub ComboBox1_Change()
' Activate the choosen workbook (required for modal mode)
On Error Resume Next
Workbooks(Me.ComboBox1.Value).Activate
End Sub
Private Sub ComboBox1_DropButtonClick()
' Populate ComboBox1 by names of visible workbooks
Dim a(), i As Long, j As Long, Wb As Workbook
i = Workbooks.Count
ReDim a(1 To i)
For Each Wb In Application.Workbooks
If Wb.Windows(1).Visible Then
j = j + 1
a(j) = Wb.Name
End If
Next
If j = 0 Then
Workbooks.Add
j = 1
a(j) = ActiveWorkbook.Name
End If
If j < i Then ReDim Preserve a(1 To j)
Me.ComboBox1.List = a()
Me.ComboBox1.Value = ActiveWorkbook.Name
End Sub
Private Sub CommandButton1_Click()
SetModal False
End Sub
Private Sub CommandButton2_Click()
SetModal True
End Sub
Private Sub CommandButton3_Click()
Unload Me
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
' Safe closing
SetModal False
End Sub
Private Sub AttachFormToWindow(Wn As Window)
#If VBA7 Then
If Val(Application.Version) > 14 Then
SetWindowLong FrmHwnd, -8&, Wn.hWnd
SetForegroundWindow FrmHwnd
End If
#End If
End Sub
Private Sub SetModal(Optional Flag As Boolean)
Dim AppHwnd As Variant, i As Long, Mode As Long, Wn As Window
If Flag Then Mode = 0 Else Mode = 1
' Change mode
If Val(Application.Version) < 15 Then
' MDI - set current window which is global
EnableWindow Application.hWnd, Mode
Else
' SDI - set each window because of its unique Hwnd
For Each Wn In Application.Windows
EnableWindow Wn.hWnd, Mode
Next
End If
' Show mode in UserForm's caption
With Me
i = InStr(.Caption, " [Mod")
If i = 0 Then i = Len(Caption) + 1
.Caption = Left(.Caption, i - 1) & " [" & IIf(Flag, "Modal]", "Modeless]")
End With
End Sub
