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

 

[NimishK]

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

Posting Code of Stackoverflow does the execution of image as sent in post #20 image UF-minMaxPict1. this is OK
Desired is the coding for the result wanted as per image UF-minMaxPict2 i.e
Coding required is to click on command button and userform minimises as shown in image UF-minMaxPict2
coding required in CommandButton_click() which will enable me to automatically minimise the userform.
So StackOverflow's code i took as basic refernce and since i am new to this type of technical coding. I am finding it difficult to implement the same in Command_Click with modifications required to achieve what i want.

 

[Yongle]

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

Try this instead
- I found this here and it worked first time for me
- it adds minimise and maximise icons on the form

In a NEW workbook
- add Active-X Command Button to sheet1
- add a simple userform (a few textboxes perhaps)
- add the code

Test
- click on Command Button to show the form
- amend values in textboxes
- click on minimise icon ( _ ) at top right of form
- the form minimises in normal way
- amend values in worksheet
- restore the form by click on Restore (see bottom of screen - Restore icon is on left of FormTaskbar )

Code for Active-X Command Button

Private Sub CommandButton1_Click()
    UserForm1.Show vbModeless
End Sub

In Userform code

Private Sub UserForm_Activate()
  AddToForm MIN_BOX
  AddToForm MAX_BOX
End Sub

In a Module

'credit to
'https://www.ozgrid.com/forum/forum/help-forums/excel-general/122825-api-to-create-minimise-and-maximise-buttons-on-a-userform

Option Explicit

Private Const GWL_STYLE As Long = -16
Public Const MIN_BOX As Long = &H20000
Public Const MAX_BOX As Long = &H10000
Const SC_CLOSE As Long = &HF060
Const SC_MAXIMIZE As Long = &HF030
Const SC_MINIMIZE As Long = &HF020
Const SC_RESTORE As Long = &HF120

Private Declare Function GetWindowLong Lib "user32.dll" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32.dll" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
'Redraw the Icons on the Window's Title Bar
Private Declare Function DrawMenuBar Lib "user32.dll" (ByVal hwnd As Long) As Long
'Returns the Window Handle of the Window accepting input
Private Declare Function GetForegroundWindow Lib "user32.dll" () As Long

Public Sub AddToForm(ByVal Box_Type As Long)
Dim BitMask As Long, Window_Handle As Long, WindowStyle As Long, Ret As Long
    If Box_Type = MIN_BOX Or Box_Type = MAX_BOX Then
        Window_Handle = GetForegroundWindow()
        WindowStyle = GetWindowLong(Window_Handle, GWL_STYLE)
        BitMask = WindowStyle Or Box_Type
        Ret = SetWindowLong(Window_Handle, GWL_STYLE, BitMask)
        Ret = DrawMenuBar(Window_Handle)
   End If
End Sub

 

[My Aswer Is This]

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

I really do not know how to do what you want.
And do it the way you want.

And I really do not like using code I do not understand

 

[Yongle]

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

@NimishK
Did you test the solution provided in post#22?

 

[Jaafar Tribak]

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

I have quickly read through the posts and got confused as to what is needed .

Do you want the userform to be minimized via adding the minimize icon on its titlebar via the API calls as shown in previous posts and then once the userform is minimized the userform should become modeless so the user can work with excel while being minimized OR _ do you want this to be achieved via clicking a commandbutton on the userform ?

And then waht happens when the user restores back (ie: maximizes) the userform ? Do you want the userform to go back to modal ?

 

[NimishK]

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

Yongle
Definately credit to Ozgird' s thread 122825. Good one

Test
- click on Command Button to show the form
- amend values in textboxes
- click on minimise icon ( _ ) at top right of form
- the form minimises in normal way
- amend values in worksheet
- restore the form by click on Restore (see bottom of screen - Restore icon is on left of FormTaskbar )

- click on minimise icon ( _ ) at top right of form--------------> I want Command Button (On userform) to do exactly this. Rather than click on minimise icon of userform. Whether Possible or Not ?

Jaffer Thanks for your questions but kindly refer My reply to Yongle. this will clarify the whole thing

 

[Jaafar Tribak]

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

Yongle
Definately credit to Ozgird' s thread 122825. Good one




Jaffer Thanks for your questions but kindly refer My reply to Yongle. this will clarify the whole thing

Sorry I got confused reading through so many posts

You want the form to be modal when displayed and modeless when minimised - Right ?

 

[Yongle]

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

I cannot help you further.

 

[NimishK]

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

Jaffer

You want the form to be modal when displayed and modeless when minimised - Right ?

Yes but additionally with Command Button. though We can click on minimized button
Yongle : Thank you so much for your hard Efforts and helping out. Nevertheless .
MyAnswerisThis: Thanks for your inputs too.

Now it's high time if any MVP or moderator can look into this possibility and help us out.

 

[NimishK]

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

in VB6 we have following syntax

FormName.WindowState = vbMinimized

Something on above syntax required in Command_Click