With CommandButton would like to automatically minimize the Userform.

[NimishK]

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

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

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

 

[Jaafar Tribak]

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

Jaffer

Yes but additionally with Command Button. though We can click on minimized button

Hi NimishK,

Workbook example

See if this works for you :

1- In the UserForm Module :

Option Explicit

[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 Function GetForegroundWindow Lib "user32" () As LongPtr
    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 hwnd 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 Function GetForegroundWindow Lib "user32" () 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 hwnd 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 bRestored As Boolean
Private bClosing As Boolean


Public Sub ShowMe()
    Me.Show vbModeless
End Sub


Private Sub UserForm_Activate()
    
    [B][COLOR=#008000]'IMPORTANT !!![/COLOR][/B]
    [COLOR=#008000][B]'Execute any pre-existinng code here before calling the 'ToggleModalState' routine.[/B][/COLOR]
    
    Call ToggleModalState
    
End Sub


Private Sub UserForm_Terminate()
    bClosing = True
    SetWindowPos Application.hwnd, HWND_TOP, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE Or SWP_SHOWWINDOW
    EnableWindow Application.hwnd, 1
End Sub
        
[B][COLOR=#008000]'Minimize CommandButton.[/COLOR][/B]
Private Sub CommandButton1_Click()
    EnableWindow Application.hwnd, 1
    SendMessage hwnd, WM_SYSCOMMAND, SC_MINIMIZE, 0
    Do
        DoEvents
    Loop Until IsIconic(hwnd) = 0
    EnableWindow Application.hwnd, 0
End Sub

Private Sub ToggleModalState()

    bRestored = False
    WindowFromAccessibleObject Me, hwnd
    Call AddMinimizeButton
    Call AddToTaskBar
    EnableWindow Application.hwnd, 0

    Do
        If IsIconic(hwnd) Then
            If GetForegroundWindow <> Application.hwnd Then SetForegroundWindow Application.hwnd
            DoEvents
            bRestored = True
            EnableWindow Application.hwnd, 1
        Else
            If bRestored = True Then
                EnableWindow Application.hwnd, 0
            End If
        End If
        DoEvents
        
    Loop Until bClosing
    EnableWindow Application.hwnd, 1

End Sub


Private Sub AddMinimizeButton()
    SetWindowLong hwnd, GWL_STYLE, GetWindowLong(hwnd, GWL_STYLE) Or WS_MINIMIZEBOX
End Sub


Private Sub AddToTaskBar()
    SetWindowPos hwnd, HWND_TOP, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE Or SWP_NOACTIVATE Or SWP_HIDEWINDOW
    SetWindowLong hwnd, GWL_EXSTYLE, GetWindowLong(hwnd, GWL_EXSTYLE) Or WS_EX_APPWINDOW
    SetWindowPos hwnd, HWND_TOP, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE Or SWP_NOACTIVATE Or SWP_SHOWWINDOW
End Sub

2- This how you call the UserForm :

In a Standard Module :

Option Explicit

Public Sub ShowUserForm()
    UserForm1.ShowMe
End Sub

I've only tested this on Windows 10.

Note:
The code in the userform makes the form modeless before it is displayed so it means that even if the userform is initially Modal it becomes Modeless.

Having said that, the userform will always look Modal while on display (ie: you won't be able to work with excel) and Modeless while minimized as requested.

I said: will look Modal meaning it won't be truely modal therefore if you have any code after calling the userform, the code will run regardless which may interfere and cause the userform to fail.

Here is an example that illustrates how showing a simple MsgBox immediatly after calling the userform would cause the userform to stop working as intended :

Public Sub ShowUserForm()
    UserForm1.ShowMe
    MsgBox "hello"
End Sub

I have a workaround in mind for the above problem but I need to test it on a machine other than Windows 10.

 

[NimishK]

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

Jaffar Thanks and really Excellent Efforts

i am Using Excel 2013 and tested your code

1. I did not create any Command Button on worksheet to execute the code
2. Pressed F5 and Ran the Macro from VBE of Standrad Module : Form Loads
3. Also from userForm : Pressed F5 : Form Loads

Having said that, the userform will always look Modal while on display (ie: you won't be able to work with excel) and Modeless while minimized as requested.

above Point no 2 and 3 : When Clicked on Minimized button Form is not minimized but disappears because as it dissapeared i was not able to Type on sheet.

Also with above Point 2 when ran with below code with Msgbox There is No minimize button, maximize button shown but without msgbox
minimize button, maximize button are shown

Public Sub ShowUserForm()
    UserForm1.ShowMe
    MsgBox "Hello"
End Sub

Only thing when clicked on Minimise Button it does not minimize but disappears.

I created then CommandButton (as per your code for UF) on userform it works like above mentioned points and userform like above disappears. I think we just need to see the Minimise part rather Form being disappeared and able to work on worksheet.

 

[Jaafar Tribak]

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

Hi,

A couple of questions ?

1- Which version of Windows are you running ?
2- Are you calling the form via UserForm1.ShowMe or UserForm1.Show ?
3- Did you download the workbook example in the link and tested it ? Did it work as expected ?

 

[NimishK]

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

Jaffar

Replies
1. Excel 2013 as mentioned in my last post
2. As per your code Userform1.showme and not Userfom1.Show ---------> Will this make any difference ?
Yes it made difference with msgbox with UserForm1.showme Msgbox displays as it initiailise and with Userform1.show .msgbox displays
afterwards
3. I could not download your Workbook Example but copied the code from your post #31 . I cannot say fully as expected but somewhere near.

Any more doubts or testing needs to be done Please let me know

 

[Jaafar Tribak]

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

Jaffar

Replies
1. Excel 2013 as mentioned in my last post
Any more doubts or testing needs to be done Please let me know

I was referring to the Operating System Edition not the excel edition ... Are you using Windows 10, 7 XP etc .. ?
Also, Can you tell me the part of the code that is colored in red in the userform module ? This should give me a clue as to the bitness of your system.

3. I could not download your Workbook Example but copied the code from your post #31 . I cannot say fully as expected but somewhere near

I am assuming you followed the steps below to recreate the workbook example:

1- Add a new Workbook
2- Add a new UserForm to the VBAProject (UserForm1)
3- Place a commandbutton on the userform (CommandButton1)
4- Copy the first code in the UserForm module
5- Add a new Standard Module to the VBAProject
6- Copy the second code in the standard module
7- Add a button to a worksheet and assign to it the ShowUserForm Macro
8- Click the worksheet button to run the userform

The above steps should display a userform with a minimize box added to its titlebar and a commandbutton clicking on which should also minimize the modal userform and allow the user to work with the worksheet .

Restoring the minimized userform from the taskbar should display the userform back on screen and make it modal again.

 

[My Aswer Is This]

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

I have been following this posting all along. And gave some help in the beginning.
I'm just curious why the user could not just click on the Userform and the width of the userform would shrink down to all most nothing as would the height.
And then when user wanted he could again click on the userform and a script would put the userform size back to normal.

Like this example:

Private Sub UserForm_Click()
'Modified  10/20/2018  4:37:36 AM  EDT
If Me.Width > 200 Then
Me.Width = 50
Me.Height = 50
Else
Me.Width = 250
Me.Height = 250
End If
End Sub

 

[Jaafar Tribak]

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

Ok- I just tested the code on 2 new machines running Windows 7 Pro - Excel 2007 and excel 2013 and noticed that minimizing the userform via the minimize box on the titlebar causes the application to freeze sporadically.

Minimizing the userform via the commandbutton works flawlessly though.

I'll try to fix this issue and if anything comes up I'll post back.

 

[Jaafar Tribak]

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

Ok- I have updated the code in order to fix the above mentioned issues.

I have also updated the above uploaded workbook example

1- Code in the UserForm Module :

[COLOR=#008000]'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    '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 20/10/2018.
    
    'CAUTION !!!
    '===========
    'Unhandled errors can potentially leave the application window frozen/Disabled.
    'Use error handling in any code that you may subsequently add to the userform.
    
            'ERROR HANDLING EXAMPLE:
            '======================
            
            'Private Sub example()
            
            '    On Error GoTo ErrHandler
            
            '           YOUR CODE GOES HERE ....
            
            'ErrHandler:
            '    EnableWindow Application.hwnd, 1
            '    If Err Then Err.Raise Err
    '        End Sub
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
[/COLOR]
Option Explicit

[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 hwnd 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 hwnd 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 bRestored As Boolean
Private bClosing As Boolean


[COLOR=#008000]'Public Method.[/COLOR]
Public Sub ShowMe()
    Me.Show vbModeless
    Do
        DoEvents
    Loop Until bClosing
End Sub


Private Sub UserForm_Activate()
    
    [COLOR=#008000]'IMPORTANT !!!
    'Execute any pre-existinng code here before calling the 'ToggleModalState' routine.[/COLOR]
    
    Call ToggleModalState
    
End Sub


Private Sub UserForm_Terminate()
    bClosing = True
    SetWindowPos Application.hwnd, HWND_TOP, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE Or SWP_SHOWWINDOW
    EnableWindow Application.hwnd, 1
End Sub

        
[COLOR=#008000]'Minimize the UserForm via a CommandButton.[/COLOR]
Private Sub CommandButton1_Click()
    EnableWindow Application.hwnd, 1
    SendMessage hwnd, WM_SYSCOMMAND, SC_MINIMIZE, 0
    Do
        DoEvents
    Loop Until IsIconic(hwnd) = 0
    EnableWindow Application.hwnd, 0
End Sub


Private Sub ToggleModalState()

    On Error GoTo ErrHandler

    bRestored = False
    WindowFromAccessibleObject Me, hwnd
    Call AddMinimizeButton
    Call AddToTaskBar
    
    EnableWindow Application.hwnd, 0
 
    Do
        If IsIconic(hwnd) Then
            If bRestored = False Then
                SetForegroundWindow Application.hwnd
                bRestored = True
                EnableWindow Application.hwnd, 1
                keybd_event vbKeyEscape, 0, 0, 0
                keybd_event vbKeyEscape, 0, KEYEVENTF_KEYUP, 0
            End If
        Else
            If bRestored = True Then
                bRestored = False
                EnableWindow Application.hwnd, 0
            End If
        End If
        
        DoEvents
        
    Loop Until bClosing
    
ErrHandler:

    EnableWindow Application.hwnd, 1
    
    If Err Then Err.Raise Err

End Sub


Private Sub AddMinimizeButton()
    SetWindowLong hwnd, GWL_STYLE, GetWindowLong(hwnd, GWL_STYLE) Or WS_MINIMIZEBOX
End Sub


Private Sub AddToTaskBar()
    SetWindowPos hwnd, HWND_TOP, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE Or SWP_HIDEWINDOW Or SWP_NOACTIVATE
    SetWindowLong hwnd, GWL_EXSTYLE, GetWindowLong(hwnd, GWL_EXSTYLE) Or WS_EX_APPWINDOW
    SetWindowPos hwnd, HWND_TOP, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE Or SWP_SHOWWINDOW Or SWP_NOACTIVATE
End Sub


[COLOR=#008000]'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    'Calling the UserForm from a Standard module.
    '===========================================
    
    'Public Sub ShowUserForm()
    '    UserForm1.ShowMe
    'End Sub
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\[/COLOR]

2- Code for calling the UserForm in a Standard Module :

Option Explicit

Public Sub ShowUserForm()
    UserForm1.ShowMe
End Sub

 

[NimishK]

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

Jaffar Awesome. Just the Perfect one.

Tested and Observations
1. When First time clicked on Command Button or on Minimize button Userform1 disappeared which actually should not. Then again i had to click on Sheet Command button to resume back the Userform

2. After resuming the userform again when i clicked Again on Command Button of Userform1 or on Minimize button. Userform1 gets minimized and is seen Minimized.

Question on Point 1. Why 1st time when clicked the UF disappears and not seen Minimized. Any ideas how this can be Avoided. Otherwise this is Just the perfect one

 

[Jaafar Tribak]

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

Question on Point 1. Why 1st time when clicked the UF disappears and not seen Minimized. Any ideas how this can be Avoided. Otherwise this is Just the perfect one

I think you are not looking at the correct place... The UF doesn't disappear.... It is simply minimized to the Windows TaskBar along where the little excel icon appears accross the bottom of your screen... If you just point with the mouse pointer at the excel application icon located in the windows taskbar, you should be able to see the minimized UF and be able to click on it in order to restore it.

You shoud also be able to see it via ALT+TAB .

I haven't tried this yet but one could also add a seperate small icon for the UF in the Windows System Tray clicking on which would also activate\restore the minimized UF.

The Windos System Tray is not to be confused with the Windows TaskBar .. The System Tray is normally located at the bottom right of the screen where the system clock and network icons appear.