With CommandButton would like to automatically minimize the Userform.

N

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
684
Hi

This is my first time working on API for Minimizing, Maximizing Buttons on Userform and Found the below following code at https://stackoverflow.com/questions/50853247/excel-vba-userform-minimise-button

Would it be possible for a command button Click to automatically minimize the userform and show userform modeless
'Code in Module
Code: 
Option Explicit
Public Declare Function FindWindowA& Lib "user32" (ByVal lpClassName$, ByVal lpWindowName$)
Public Declare Function GetWindowLongA& Lib "user32" (ByVal hwnd&, ByVal nIndex&)
Public Declare Function SetWindowLongA& Lib "user32" (ByVal hwnd&, ByVal nIndex&, ByVal dwNewLong&)
' Déclaration des constantes
Public Const GWL_STYLE As Long = -16
Public Const WS_MINIMIZEBOX = &H20000
Public Const WS_MAXIMIZEBOX = &H10000
Public Const WS_FULLSIZING = &H70000
'Attention, envoyer après changement du caption de l'UF

Public Sub InitMaxMin(mCaption As String, Optional Max As Boolean = True, Optional Min As Boolean = True _
        , Optional Sizing As Boolean = True)

Dim hwnd As Long
    hwnd = FindWindowA(vbNullString, mCaption)
    If Min Then SetWindowLongA hwnd, GWL_STYLE, GetWindowLongA(hwnd, GWL_STYLE) Or WS_MINIMIZEBOX
    If Max Then SetWindowLongA hwnd, GWL_STYLE, GetWindowLongA(hwnd, GWL_STYLE) Or WS_MAXIMIZEBOX
    If Sizing Then SetWindowLongA hwnd, GWL_STYLE, GetWindowLongA(hwnd, GWL_STYLE) Or WS_FULLSIZING

End Sub

Code in Userform
Code: 
Private Sub UserForm_Initialize()
    InitMaxMin me.Caption
End Sub

Private Sub CommandButton1_Click()
    userform1.show vbModeless
End Sub

what needs to be coded when clicked on command button: Automatically the Userform minimises automatically rather then clicking on
minimize button on userform and show userform modeless

thanks
NimishK
 
Last edited:
Sort by date Sort by votes
Re: With Commnand_Button would like to automatically minimize the Userform.

(y)Jaffar Tribak (y)

Thank you so much for your Help. I think this is one of the best way of very neat representations when Minimising the Userform of Excel while refering or working on worksheet.
Under the circumstances(i.e. limitations in VBE for not providing directly to us of Userform minimize, maximize buttons), you have done a really Marvelous and Fantastic work. Will always appreciate. Will recommend other Forum members to use the code provided by you.

Mercie Vous.

NimishK
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Re: With Commnand_Button would like to automatically minimize the Userform.

NimishK said:
(y)Jaffar Tribak (y)

Thank you so much for your Help. I think this is one of the best way of very neat representations when Minimising the Userform of Excel while refering or working on worksheet.
Under the circumstances(i.e. limitations in VBE for not providing directly to us of Userform minimize, maximize buttons), you have done a really Marvelous and Fantastic work. Will always appreciate. Will recommend other Forum members to use the code provided by you.

Mercie Vous.

NimishK
Click to expand...

Thanks for the feedback and am glad I could help.

I'll post later an improved update of the code which will prevent the application from freezing in case an unhandled error occurs while the useerform is running.
 
Upvote 0
Re: With Commnand_Button would like to automatically minimize the Userform.

Thanks for the feedback and am glad I could help.
I'll post later an improved update of the code which will prevent the application from freezing in case an unhandled error occurs while the useerform is running.
Click to expand...
Surely Will Await
 
Upvote 0
Re: With Commnand_Button would like to automatically minimize the Userform.

Hopefully, this version should be a more stable than the previous one.

Updated workbook example

There are 3 main fixes in this version:

A- I moved all the API code to a standard module to remove all the clutter from the form and leave more room for other code that the user may want to add in the userform module.

B- I made use of the UserForm_Resize built-in event to avoid the need for using the ToggleModalState routine which had a constant loop running in it.

C- I Setup a timer to monitor unhandled runtime errors .. This should prevent excel from freezing should an unexpected error occur.


1- Code in a Standard Module :
Code: 
Option Explicit

[COLOR=#008000]'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    'Error-Safe version.
    'Code to minimize an excel modal userform to the taskbar.
    'Once the userform is minimized, the user can interact with excel.
    
    'Written by Jaafar Tribak @ MrExcel.com on 22/10/2018.
    
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\[/COLOR]

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  Win64 Then
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    Private Declare PtrSafe Function WindowFromAccessibleObject Lib "Oleacc" (ByVal pacc As IAccessible, phwnd As LongPtr) As Long
    Private Declare PtrSafe Function SetWindowPos Lib "user32" (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, ByVal x As Long, ByVal y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As LongPtr
    Private Declare PtrSafe Function IsIconic Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Private Declare PtrSafe Function EnableWindow Lib "user32.dll" (ByVal hwnd As LongPtr, ByVal fEnable As Long) As Long
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Sub keybd_event Lib "user32.dll" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
    Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Private Declare PtrSafe Function IsWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function SetProp Lib "user32" Alias "SetPropA" (ByVal hwnd As LongPtr, ByVal lpString As String, ByVal hData As LongPtr) As Long
    Private Declare PtrSafe Function GetProp Lib "user32" Alias "GetPropA" (ByVal hwnd As LongPtr, ByVal lpString As String) As LongPtr
    Private Declare PtrSafe Function RemoveProp Lib "user32" Alias "RemovePropA" (ByVal hwnd As LongPtr, ByVal lpString As String) As LongPtr

    Private FrmHwnd As LongPtr

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
    Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Private Declare Function WindowFromAccessibleObject Lib "oleacc" (ByVal pacc As IAccessible, phwnd As Long) As Long
    Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
    Private Declare Function IsIconic Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
    Private Declare Function EnableWindow Lib "user32.dll" (ByVal hwnd As Long, ByVal fEnable As Long) As Long
    Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Sub keybd_event Lib "user32.dll" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
    Private Declare Function SetTimer Lib "user32" (ByVal Hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Private Declare Function KillTimer Lib "user32" (ByVal Hwnd As Long, ByVal nIDEvent As Long) As Long
    Private Declare Function IsWindow Lib "user32" (ByVal Hwnd As Long) As Long
    Private Declare Function SetProp Lib "user32" Alias "SetPropA" (ByVal hwnd As Long, ByVal lpString As String, ByVal hData As Long) As Long
    Private Declare Function GetProp Lib "user32" Alias "GetPropA" (ByVal hwnd As Long, ByVal lpString As String) As Long
    Private Declare Function RemoveProp Lib "user32" Alias "RemovePropA" (ByVal hwnd As Long, ByVal lpString As String) As Long

    Private FrmHwnd As Long

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private Const GWL_EXSTYLE = (-20)
Private Const GWL_STYLE = (-16)
Private Const WS_EX_APPWINDOW = &H40000
Private Const WS_MINIMIZEBOX = &H20000
Private Const WM_SYSCOMMAND = &H112
Private Const SC_MINIMIZE = &HF020&
Private Const SWP_NOACTIVATE = &H10
Private Const SWP_HIDEWINDOW = &H80
Private Const SWP_SHOWWINDOW = &H40
Private Const SWP_NOMOVE = &H2
Private Const SWP_NOSIZE = &H1
Private Const HWND_TOP = 0
Private Const KEYEVENTF_KEYUP = &H2

Private bClosing As Boolean


Public Sub MakeFormModeless(ByVal Form As Object)
    Form.Show vbModeless
    bClosing = False
    Set Form = Nothing
    'Run a loop to fake Modal UserForm.
    Do
        DoEvents
    Loop Until bClosing
    bClosing = False
End Sub


Public Property Let EnabelExcel(ByVal Enable As Boolean)
    EnableWindow Application.hwnd, CLng(Enable)
End Property


Public Function GetFormHwnd(ByVal Frm As Object)
        WindowFromAccessibleObject Frm, FrmHwnd
        SetProp Application.hwnd, "HWND", FrmHwnd
        Set Frm = Nothing
End Function


Public Sub AddMinimizeButton(Optional ByVal Dummy As Boolean)
    SetWindowLong FrmHwnd, GWL_STYLE, GetWindowLong(FrmHwnd, GWL_STYLE) Or WS_MINIMIZEBOX
End Sub


Public Sub AddToTaskBar(Optional ByVal Dummy As Boolean)
    SetWindowPos FrmHwnd, HWND_TOP, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE Or SWP_HIDEWINDOW Or SWP_NOACTIVATE
    SetWindowLong FrmHwnd, GWL_EXSTYLE, GetWindowLong(FrmHwnd, GWL_EXSTYLE) Or WS_EX_APPWINDOW
    SetWindowPos FrmHwnd, HWND_TOP, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE Or SWP_SHOWWINDOW Or SWP_NOACTIVATE
End Sub


[COLOR=#008000]'Routine to prevent excel from freezing should an unhandled error occur.[/COLOR]
Public Sub RunSafetyRoutine(Optional ByVal Dummy As Boolean)
    SetTimer Application.hwnd, 0, 2000, AddressOf SafetyRoutine
End Sub


Public Property Get FormIsMinimized(Optional ByVal Dummy As Boolean) As Boolean
    FormIsMinimized = CBool(IsIconic(FrmHwnd))
End Property


Public Sub BringExcelToFront(Optional ByVal Dummy As Boolean)
    SetForegroundWindow Application.hwnd
    SetWindowPos Application.hwnd, HWND_TOP, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE Or SWP_SHOWWINDOW
End Sub


Public Sub PressESCKey(Optional ByVal Dummy As Boolean)
    If CBool(IsIconic(FrmHwnd)) Then
        keybd_event vbKeyEscape, 0, 0, 0
        keybd_event vbKeyEscape, 0, KEYEVENTF_KEYUP, 0
    End If
End Sub


Public Sub MiniMizeForm(Optional ByVal Dummy As Boolean)
    SendMessage FrmHwnd, WM_SYSCOMMAND, SC_MINIMIZE, 0
End Sub


Public Property Let FormClosing(ByVal Closing As Boolean)
    bClosing = Closing
End Property


Public Sub CleanUp(Optional ByVal Dummy As Boolean)
    KillTimer Application.hwnd, 0
    bClosing = True
    EnabelExcel = True
    Application.CellDragAndDrop = CBool(GetProp(Application.hwnd, "CellDragAndDrop"))
    RemoveProp Application.hwnd, "CellDragAndDrop"
    RemoveProp Application.hwnd, "HWND"
End Sub


Private Sub SafetyRoutine()
    If IsWindow(GetProp(Application.hwnd, "HWND")) = 0 Then
        Call CleanUp
    Else
        SetProp Application.hwnd, "CellDragAndDrop", 1
    End If
End Sub

2- Code in the UserForm Module:
Code: 
Option Explicit

[COLOR=#008000]'Public Method to call the UserForm.[/COLOR]
Public Sub ShowMe()
    Call MakeFormModeless(Me)
End Sub


Private Sub UserForm_Activate()
    EnabelExcel = False
    Call GetFormHwnd(Me)
    Call AddMinimizeButton
    Call AddToTaskBar
   [COLOR=#008000] 'Routine to prevent excel from freezing should an unhandled error occur.[/COLOR]
    Call RunSafetyRoutine
End Sub


Private Sub UserForm_Terminate()
    FormClosing = True
    Call BringExcelToFront
    EnabelExcel = True
End Sub


Private Sub UserForm_Resize()
    Static bMinimizedBefore As Boolean

    If FormIsMinimized() Then
        EnabelExcel = True
        Call BringExcelToFront
        Application.CellDragAndDrop = False
        If bMinimizedBefore = False Then
            bMinimizedBefore = True
            Call PressESCKey
        End If
    Else
        EnabelExcel = False
    End If
End Sub


[COLOR=#008000]'Minimize the UserForm via a CommandButton.[/COLOR]
Private Sub CommandButton1_Click()
    EnabelExcel = True
    Call MiniMizeForm
    Do
        DoEvents
    Loop Until FormIsMinimized() = False
    EnabelExcel = False
End Sub


[COLOR=#008000]'For testing only.
'Intentionally raise an error to demonstrate that excel doesn't crash\freeze after the error.[/COLOR]
Private Sub CommandButton2_Click()
    Err.Raise 11
End Sub

3- Code in a module to call the UserForm :
Code: 
Option Explicit

Public Sub ShowUserForm()
    If VBA.UserForms.Count = 0 Then
        UserForm1.ShowMe
    End If
End Sub
 
Upvote 0
Re: With Commnand_Button would like to automatically minimize the Userform.

Jaffar Definately your last code is really much better than previous one. (y)
Appreciated your Hard efforts for a better representation.

only thing the userform minimized to TaskBar is bit confussing. Any chance of avoinding this. if possible let me know else forget it.

Thanks
NimishK
 
Last edited:
Upvote 0
Re: With Commnand_Button would like to automatically minimize the Userform.

Jaffar

I tried your last code with Two userforms
and on Second userform i.e UF2 all the UF code as is and UF2 ShowModal property = True
On uf1 i addtionally put another command button to load another UF2
Code: 
Private Sub CmdButton2_click()
load UF2
UF2.Show

  EnabelExcel = True
    Call MiniMizeForm
    Do
        DoEvents
    Loop Until FormIsMinimized() = False
   EnabelExcel = False
End Sub

Observations
1. When UF2 is minimized i cant work on UF1 neither i can minimize UF1 as it seems i need to close UF2 to work on UF1
2. Also I tried the below code seperately
Code: 
Private Sub CmdButton2_click()
    If VBA.UserForms.Count = 0 Then
         uf2.ShowMe
    End if
'Got an Error Method or Data member not found
End sub
I am sure you can come up with totally new version for Mutliple Userforms Getting Minimized and get accessed to work on other UForms and worksheets as the case may be
 
Last edited:
Upvote 0
Re: With Commnand_Button would like to automatically minimize the Userform.

NimishK said:
I am sure you can come up with totally new version for Mutliple Userforms Getting Minimized and get accessed to work on other UForms and worksheets as the case may be
Click to expand...

That would be more complicated and would need some rethinking of the overall code design.

It would certainly be nice to have some generic code that would flexibly work with multiple loaded userforms but I am not sure it would be easy or possible.

I'll give this some thought when I have time and will let you know if anything comes up.

Regards.
 
Upvote 0
Re: With Commnand_Button would like to automatically minimize the Userform.

Oh Sure will await not in hurry:)
 
Upvote 0
Re: With Commnand_Button would like to automatically minimize the Userform.

May Be a mind blowing one :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
minimize and Maximize
Replies
4
Views
680
NdNoviceHlp
NdNoviceHlp
H
  • Solved
Catching the Mouse Click Error in Modal User Form
Replies
4
Views
327
huiettcm
H
A
  • Question
What is wrong with this VBA code: userForm get hand ico for pointer on hover
2
Replies
15
Views
560
RoryA
RoryA
N
  • Question
Getting Compile error: Use on 64-Bit systems ... PtrSafe Attribute for Form Minimize and Maximize
Replies
6
Views
959
NimishK
N
R
  • Solved
Resizing Userforms in 64-bit excel
Replies
5
Views
2K
mwoollen
M

Forum statistics

Threads
1,215,365
Messages
6,124,511
Members
449,166
Latest member
hokjock

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top