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

 

[My Aswer Is This]

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

You said:

Would it be possible for a command button Click to automatically minimize the userform and show userform modeless

Please define minimize.

Do you mean make the Userform smaller?
And how small do you want it?
And do you want this to happen when you click on a button on the sheet or a button on the UserForm

​

 

[NimishK]

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

Hi "My Answer is This". Thank you for your Question

Very new to API coding and the code above as found to minimise the Form when clicked on Minimise button. so the defination of minimise size i dont know.
i just want to minimise the form by clicking on command button instead of clicking on minimize button of UF and UF to be modeless so that i can work on sheet.

thankx once again

 

[My Aswer Is This]

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

I know nothing about API coding
I know how to write a script to make your Userform smaller.
If that is what you want I could write a Vba script to do that.
Let me know if that is what you want.
For example I could write a script which ever time you click the button your userform gets a little smaller until your happy or a script to make it some exactly size on one click of a button.
If you open the script like this: The userform will open Modeless so you can work on your workbook when userform visible

Userform1.Show Modeless

 

[Yongle]

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

1. Have you considered simply hiding the Userform without unloading it?

UserForm1.Hide

Then display again with values intact with

UserForm1.Show

2. What do you want to do in the worksheet whilst the form is still open?

3. Have you considered simply dumping all the values from the userform to a hidden sheet so that the form can be "temporarily" closed and then reopened with saved values?

 

[NimishK]

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

Reply to M A I T

I know nothing about API coding
I know how to write a script to make your Userform smaller.
If that is what you want I could write a Vba script to do that.
Let me know if that is what you want.
For example I could write a script which ever time you click the button your userform gets a little smaller until your happy or a script to make it some exactly size on one click of a button.
If you open the script like this: The userform will open Modeless so you can work on your workbook when userform visible

Userform1.Show Modeless

I don't know VBA script either. you can definately show what you have. If your VBA script can achieve what i want. Nothing like it
By the way i was not all impressed at below following code form Height getting smaller from the foll. link https://www.ozgrid.com/VBA/minimize-maximize.htm

Option Explicit
Dim dHeight As Double
Private Sub ToggleButton1_Click()
    If ToggleButton1.Value = True Then
        Me.Height = Me.Height * 0.25
    Else
        Me.Height = dHeight
    End If
End Sub

Private Sub UserForm_Initialize()
    dHeight = Me.Height
End Sub

Will await your VBA script when clicking the command button if the form gets is default Minimized size at one go and resolving the issue. Surely nothing like it

 

[NimishK]

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

Reply to Yongle

Yongle
1. Have you considered simply hiding the Userform without unloading it?

UserForm1.Hide

Then display again with values intact with

UserForm1.Show

2. What do you want to do in the worksheet whilst the form is still open?

3. Have you considered simply dumping all the values from the userform to a hidden sheet so that the form can be "temporarily" closed and then reopened with saved values?

Replies to your query

1. if hiding was so easy rather i would not have posted this thread

2. Scroll the worksheet rather. if required then modify the data on worksheet

3. I did not think of dumping all values from UF to hidden sheet. I dont have hidden sheet either

 

[Yongle]

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

1. if hiding was so easy rather i would not have posted this thread

It is that easy (try my quick test) ... but perhaps it is not a good solution for you (see below **)

In a test workbook
- create simple userform with one textbox and one command button (code Me.Hide)
- add a command button on the worksheet to show the userform (code UserForm1.Show)
- click on button to show userform
- amend textbox1 value
- hide the userform
- amend values in sheet
- click on button to show userform
- textbox1 value is retained

** Perhaps it is not a good solution for you ...
Are some userform values (or sheet values) being automatically updated with UserForm_Deactivate or UserForm_Activate? (etc)?
- consider making those updates via a command button on the userform

Hiding a userform is a valid solution but perhaps other userform code makes it impractical in your case

 

[Yongle]

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

I did not think of dumping all values from UF to hidden sheet. I dont have hidden sheet either

Another possible solution is to use a command button on the userform which
- stores the current values of all userform boxes on a hidden worksheet
- unloads the userform (this clears it from memory)
- allowing you to do whatever you want in the worksheet
- when you click on button to show the userform (IF boolean variable FormHidden is set to TRUE) UserForm_Initialize imports those values rather than default values into the userform

The example is very simplistic (containing only one textbox) and needs adapting to your situation.
Tgis solution may not even be practical if the userform is very complicated or is affected by other userforms etc

Place at TOP of a standard module

Public FormHidden As Boolean

In userform module

Private Sub CommandButton1_Click()    'button to allow you to work in worksheet
    '[I]amend boolean variable[/I]
        FormHidden = True
    '[I]store values in hidden sheet[/I]
        Sheets("HiddenSheet").Range("A1") = TextBox1.Value
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    If FormHidden = True Then
        [COLOR=#008080]'[I]get values from hidden sheet...[/I][/COLOR]
            TextBox1.Value = Sheets("HiddenSheet").Range("A1").Value
        '[I][COLOR=#008080]amend boolean variable[/COLOR][/I]
            FormHidden = False
        '[I][COLOR=#008080]delete values in hidden sheet[/COLOR][/I]
            Sheets("HiddenSheet").Range("A1").ClearContents
    Else
        '[I][COLOR=#008080]default userform values[/COLOR][/I]
    End If
End Sub

 

[NimishK]

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

Yongle

My Dear Buddy yours is the most sweetest solution. Long back had experimented the same what you suggested. This is also not wanted.

Are some userform values (or sheet values) being automatically updated with UserForm_Deactivate or UserForm_Activate? (etc)?
- consider making those updates via a command button on the userform

I've not yet reached the level of UF Activate or deactivate.
Frankly speaking was surprised when Minimize Maximize button were not seen on userform 1st time. was literally shaken up.
With these Minimize Maximize and Close button i feel userform complete and Thankx to C.Pearson's amazing code which makes the Userform complete. and thanks to those too who has still shortern the code as per my post #1 .
Just require click of command button to functionaly minimise the userform and become modeless

Hiding a userform is a valid solution but perhaps other userform code makes it impractical in your case

Impractical in OUR case because of limitations that we faced for userform in VBA for Excel