[VBA/Excel] Minimize and Maximize Buttons for UserForm
Solved
alecomte
Posted messages
37
Status
Membre
-
alecomte Posted messages 37 Status Membre -
alecomte Posted messages 37 Status Membre -
Hello,
The code below adds minimize and maximize buttons next to the red cross on a UserForm.
However, my project contains not one but four forms.
After testing, the lines UserForm1.Hide and UserForm1.Show seemed useless, so I deleted them.
With a project that has only one form, the code still works, no problems.
But with multiple forms:
- the maximize button works fine
- But the minimize button has issues.
If I click the minimize button once, it works the first time. But if I click the button again, it suddenly closes the Excel workbook, and I therefore lose all my unsaved data.
How can I make this code work with multiple UserForms? (and more specifically the minimize button)?
Thank you for your help!
In a module
In the UserForm module
The code below adds minimize and maximize buttons next to the red cross on a UserForm.
However, my project contains not one but four forms.
After testing, the lines UserForm1.Hide and UserForm1.Show seemed useless, so I deleted them.
With a project that has only one form, the code still works, no problems.
But with multiple forms:
- the maximize button works fine
- But the minimize button has issues.
If I click the minimize button once, it works the first time. But if I click the button again, it suddenly closes the Excel workbook, and I therefore lose all my unsaved data.
How can I make this code work with multiple UserForms? (and more specifically the minimize button)?
Thank you for your help!
In a module
Public Declare Function FindWindow& _ Lib "user32" Alias "FindWindowA" _ (ByVal lpClassName$, ByVal lpWindowName$) Public Declare Function GetWindowLong& _ Lib "user32" Alias "GetWindowLongA" _ (ByVal hwnd&, ByVal nIndex&) Public Declare Function SetWindowLong& _ Lib "user32" Alias "SetWindowLongA" _ (ByVal hwnd&, ByVal nIndex&, ByVal dwNewLong&) Private Declare Function EnableWindow& _ Lib "user32" _ (ByVal hwnd&, ByVal fEnable&) Private Declare Function CallWindowProc& _ Lib "user32" Alias "CallWindowProcA" _ (ByVal lpPrevWndFunc&, ByVal hwnd&, ByVal Msg&, ByVal wParam&, ByVal lParam&) Public Const GWL_WNDPROC& = -4&, WM_SYSCOMMAND& = &H112& Public BaseUFProc&, BaseXLProc&, AncState& Function UFProc&(ByVal hwnd&, ByVal uMsg&, ByVal wParam&, ByVal lParam&) Dim HwndXL& Const SC_MINIMIZE& = &HF020& If uMsg = WM_SYSCOMMAND Then If wParam = (SC_MINIMIZE And &HFFF0&) Then HwndXL = FindWindow("XLMAIN", Application.Caption) EnableWindow HwndXL, True UserForm1.Hide AncState = Application.WindowState Application.WindowState = xlMinimized BaseXLProc = SetWindowLong(HwndXL, GWL_WNDPROC, AddressOf XLProc) UFProc = 1& Exit Function End If End If UFProc = CallWindowProc(BaseUFProc, hwnd, uMsg, wParam, lParam) End Function Function XLProc&(ByVal hwnd&, ByVal uMsg&, ByVal wParam&, ByVal lParam&) Const SC_MAXIMIZE& = &HF030&, _ SC_RESTORE& = &HF120&, SC_CLOSE& = &HF060& If uMsg = WM_SYSCOMMAND Then If wParam = (SC_MAXIMIZE And &HFFF0&) Or wParam = (SC_RESTORE _ And &HFFF0&) Or wParam = SC_CLOSE Then SetWindowLong hwnd, GWL_WNDPROC, BaseXLProc Application.WindowState = AncState UserForm1.Show XLProc = 1& Exit Function End If End If XLProc = CallWindowProc(BaseXLProc, hwnd, uMsg, wParam, lParam) End Function In the UserForm module
Option Explicit Private HandleUF& Private Sub UserForm_Initialize() Const WS_MAXIMIZEBOX& = &H10000, _ WS_MINIMIZEBOX& = &H20000, GWL_STYLE& = -16& HandleUF = FindWindow(vbNullString, Me.Caption) SetWindowLong HandleUF, GWL_STYLE, _ GetWindowLong(HandleUF, GWL_STYLE) Or WS_MAXIMIZEBOX Or WS_MINIMIZEBOX BaseUFProc = SetWindowLong(HandleUF, GWL_WNDPROC, BaseUFProc) End Sub Private Sub UserForm_Terminate() SetWindowLong HandleUF, GWL_WNDPROC, BaseUFProc End Sub
12 réponses
To reach a consensus ;)
Simplicity and efficiency:
In a standard module
and in each UserForm:
And because I don’t intend to take credit for someone else's work: https://codes-sources.commentcamarche.net/#6
thank you to ucfoutu!
Simplicity and efficiency:
In a standard module
Private Declare Function FindWindow Lib "User32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) 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 GetWindowLong Lib "User32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long Private Const GWL_STYLE As Long = (-16) 'The offset of a window's style Private hWnd, IStyle Public Sub toto(F As Object) hWnd = FindWindow(vbNullString, F.Caption) iStyle = GetWindowLong(hWnd, GWL_STYLE) Or &H70000 SetWindowLong hWnd, GWL_STYLE, iStyle End Sub
and in each UserForm:
Private Sub UserForm_Initialize() toto Me End Sub
And because I don’t intend to take credit for someone else's work: https://codes-sources.commentcamarche.net/#6
thank you to ucfoutu!
Good evening alecomte, lermite222,
Okay, the code works very well for the four forms, but lermite222 is right.
Realize for yourself, his code only takes a few lines, with a tiny call per form... when it comes to weight, clarity, and efficiency, there's nothing better!
It's up to you!
Okay, the code works very well for the four forms, but lermite222 is right.
Realize for yourself, his code only takes a few lines, with a tiny call per form... when it comes to weight, clarity, and efficiency, there's nothing better!
It's up to you!
Hello,
Actually, what you want are the system buttons to minimize and maximize your UF?
If so, there's a much simpler way to achieve that
If you're using Excel 97-2003, you can remove the lines related to zoom, which is not available in these versions, at least in 2000.
See you later
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
NOTE: I do not respond to DMs for technical questions.
Actually, what you want are the system buttons to minimize and maximize your UF?
If so, there's a much simpler way to achieve that
If you're using Excel 97-2003, you can remove the lines related to zoom, which is not available in these versions, at least in 2000.
See you later
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
NOTE: I do not respond to DMs for technical questions.
Well, try my suggestion and you won't have any more problems; at least take the time to consider it. But hey, it's up to you.
After all, if you prefer 50 lines of code instead of three... Grrr
If you hit a pot and it sounds hollow, it’s not necessarily the pot that's empty. ;-)(Confucius)
NOTE: I do not respond to private messages for technical questions.
After all, if you prefer 50 lines of code instead of three... Grrr
If you hit a pot and it sounds hollow, it’s not necessarily the pot that's empty. ;-)(Confucius)
NOTE: I do not respond to private messages for technical questions.
Assuming keeping the lines,
UserForm1.Hide
UserForm1.Show
I thought that maybe a loop like this:
Dim Usf as UserForm
For i = 1 to 4
Usf & i ...
but the variable definition As UserForm requires using a class module which then throws an error on
Public Const GWL_WNDPROC& = -4&, WM_SYSCOMMAND& = &H112&
due to the impossibility of having a constant in a class module..
UserForm1.Hide
UserForm1.Show
I thought that maybe a loop like this:
Dim Usf as UserForm
For i = 1 to 4
Usf & i ...
but the variable definition As UserForm requires using a class module which then throws an error on
Public Const GWL_WNDPROC& = -4&, WM_SYSCOMMAND& = &H112&
due to the impossibility of having a constant in a class module..
Good evening alecomte,
I don’t understand what the problem is, because everything works great for me.. the only issue is when we minimize the form, it places it on the desktop (bottom left corner).. not very pretty, but oh well.
So, if tests need to be done, we will have to send the orders.. the workbook BUT without the confidential data, as usual, for example on https://www.cjoint.com/
Looking forward to it.
I don’t understand what the problem is, because everything works great for me.. the only issue is when we minimize the form, it places it on the desktop (bottom left corner).. not very pretty, but oh well.
So, if tests need to be done, we will have to send the orders.. the workbook BUT without the confidential data, as usual, for example on https://www.cjoint.com/
Looking forward to it.
@Heliotte
What do you do with the lines UserForm1.Hide and UserForm1.Show?
Because I want to specify that I'm having a bug when I have multiple forms only!
And strangely only with the minimize button of the form on the second click?!
(whereas with a single form in my project, everything works perfectly)
I’ll try again by logging in and I'll send you a test file via https://www.cjoint.com/
@lermitte22
I’m using both Excel 2007 and 2010 (at home) and 97/2003 (at work).
But I'm encountering the same bug on all 3 versions!
Thanks to both of you
What do you do with the lines UserForm1.Hide and UserForm1.Show?
Because I want to specify that I'm having a bug when I have multiple forms only!
And strangely only with the minimize button of the form on the second click?!
(whereas with a single form in my project, everything works perfectly)
I’ll try again by logging in and I'll send you a test file via https://www.cjoint.com/
@lermitte22
I’m using both Excel 2007 and 2010 (at home) and 97/2003 (at work).
But I'm encountering the same bug on all 3 versions!
Thanks to both of you
Thank you for your responses (and your promptness!) I'm looking into it and I'll keep you updated ;)
lermite222, I find your brief code very good, well done!
However, the zoom is a bit exaggerated and especially uneven for my form: some characters enlarge more than others (even if this detail is negligible).
The advantage of the code modified by Heliotte is that by avoiding zoom, the full-screen enlargement allows me to display more of the form's content on the screen.
Furthermore, I find the reduce function more suitable. One might wonder where the form went with your method... even though I quickly found it thanks to Heliotte's comment.
In the end, I think everyone will decide based on their own form (and maybe their eyesight lol)! I will personally keep both options and will judge on a case-by-case basis! So a big thank you to both of you :) !
P.S.: That said, I believe I read that your code lermite222 only works from Excel version 2007... so with my Excel 2003 at work, I think I will lean towards the first code for the current form.
However, the zoom is a bit exaggerated and especially uneven for my form: some characters enlarge more than others (even if this detail is negligible).
The advantage of the code modified by Heliotte is that by avoiding zoom, the full-screen enlargement allows me to display more of the form's content on the screen.
Furthermore, I find the reduce function more suitable. One might wonder where the form went with your method... even though I quickly found it thanks to Heliotte's comment.
In the end, I think everyone will decide based on their own form (and maybe their eyesight lol)! I will personally keep both options and will judge on a case-by-case basis! So a big thank you to both of you :) !
P.S.: That said, I believe I read that your code lermite222 only works from Excel version 2007... so with my Excel 2003 at work, I think I will lean towards the first code for the current form.
Hello alecomte,
I want to reassure you.
The code of lermite222 works really well on an Excel workbook version 2003.
I want to reassure you.
The code of lermite222 works really well on an Excel workbook version 2003.
This is really to f*** with people's G****. grrrf<f&zhuzuzoulou
This is exactly what I propose in my demo, with the added bonus that we can choose the buttons we want to add. And to top it off, the double arrows for user resizing.
I’m keeping your pseudo aside, and trust me, I won't be trying to help you again, and given your post on CS, you still have a lot of gaps.
A++
This is exactly what I propose in my demo, with the added bonus that we can choose the buttons we want to add. And to top it off, the double arrows for user resizing.
I’m keeping your pseudo aside, and trust me, I won't be trying to help you again, and given your post on CS, you still have a lot of gaps.
A++
I'm sorry, but I have noticed that this code, like yours, does not allow reducing the workbook but only the form. So you should take a good look at the link, I am still trying to modify both codes (your proposal and that of ucfoutu) to really get the handle of the Excel window to minimize it ;)
The lines of code not being rigorously the same in your two codes (but I end up getting lost), I thought you would like to know the method that was mentioned to me elsewhere. So far be it from me to offend you, which even surprises me...
The lines of code not being rigorously the same in your two codes (but I end up getting lost), I thought you would like to know the method that was mentioned to me elsewhere. So far be it from me to offend you, which even surprises me...
I'm sorry, my bad, I went back over your code and it is indeed rigorously the same. I must have got my wires crossed when I was taking the different codes in my working file. But I do have gaps in my knowledge, hence my post; otherwise, it would have been pointless. So thank you again for your help and I’m sorry, it was coming from a good place to share the proposal on CS with you ;)
Yeah, don't try to land on your feet, it's too late.
And you'd better look at how forums work.
On CS I doubt your "friend" will reply to you again, you didn't even bother to validate his answer even though he asked you to.
And here you didn't take the time to mark your post as resolved, I had to do it for you.
Another point, for those who reply to you, nothing is more disheartening than seeing you ask the same question on multiple forums and then come back to us with the same answer that we already gave you.
And it's a shame, the problem you mention is so simple, but have fun.
And you'd better look at how forums work.
On CS I doubt your "friend" will reply to you again, you didn't even bother to validate his answer even though he asked you to.
And here you didn't take the time to mark your post as resolved, I had to do it for you.
Another point, for those who reply to you, nothing is more disheartening than seeing you ask the same question on multiple forums and then come back to us with the same answer that we already gave you.
And it's a shame, the problem you mention is so simple, but have fun.
Well, following your apologies via PM, I will consider that these misunderstandings are the result of your inexperience on forums.
Some fundamental rules nonetheless...
When Ucfoutu gives you a function that suits you, you transcribe it and notice errors because you put Option Explicit and he omitted ("Oh horrible mistake") to declare the variables. Your response, instead of being a thank you, is, alright thanks, but rather the reproach of saying... you can’t even give me something decent..
Your response...
You forgot the definitions of the variables
Private hWnd, IStyle
before the Public Sub toto...
Otherwise impeccable THANK YOU!!!
Do you think it’s pleasant to receive that?? Ask yourself the question. AND avoid being clever, don’t forget that you are the one asking.
2) Ask a question on ONE forum and FOLLOW IT, if they give you leads, FOLLOW THEM.
If in the end, you don’t have the desired answers, look elsewhere but not to 2 or 3 at the same time, that’s also disrespectful to the people who are busting their guts to answer you.
Never forget, the people who reply to you are volunteers, they help you based on their knowledge and their availability, avoid making them feel worthless even if the answer they give you is not the right one. A question can often be interpreted in multiple ways and it is often the helper who hasn’t clearly stated their problem.
Finally, to finish, as it’s you who is learning, I recommend "humility," "kindness," "patience"
I hope you will take this to heart and to show in the end that I hold no grudge against you..
in the resize event of the UF.
you find the line
you add..
But it’s not quite right.. search and if it’s not how you want it say...
Come on.. finally no hard feelings.
Note: I’m not used to writing an entire novel but I think you are young and that given your PMs, you deserve, at least from me, a second chance.
A+
--
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that is empty. ;-)(Confucius)
NOTE: I do not respond to PMs for technical questions.
Some fundamental rules nonetheless...
When Ucfoutu gives you a function that suits you, you transcribe it and notice errors because you put Option Explicit and he omitted ("Oh horrible mistake") to declare the variables. Your response, instead of being a thank you, is, alright thanks, but rather the reproach of saying... you can’t even give me something decent..
Your response...
You forgot the definitions of the variables
Private hWnd, IStyle
before the Public Sub toto...
Otherwise impeccable THANK YOU!!!
Do you think it’s pleasant to receive that?? Ask yourself the question. AND avoid being clever, don’t forget that you are the one asking.
2) Ask a question on ONE forum and FOLLOW IT, if they give you leads, FOLLOW THEM.
If in the end, you don’t have the desired answers, look elsewhere but not to 2 or 3 at the same time, that’s also disrespectful to the people who are busting their guts to answer you.
Never forget, the people who reply to you are volunteers, they help you based on their knowledge and their availability, avoid making them feel worthless even if the answer they give you is not the right one. A question can often be interpreted in multiple ways and it is often the helper who hasn’t clearly stated their problem.
Finally, to finish, as it’s you who is learning, I recommend "humility," "kindness," "patience"
I hope you will take this to heart and to show in the end that I hold no grudge against you..
in the resize event of the UF.
you find the line
If Me.Width < 300 Or Me.Height < 200 Or Fini Then Exit Sub
you add..
If Me.Width < 300 Or Me.Height < 200 Then application.WindowsState=0 : Exit Sub
But it’s not quite right.. search and if it’s not how you want it say...
Come on.. finally no hard feelings.
Note: I’m not used to writing an entire novel but I think you are young and that given your PMs, you deserve, at least from me, a second chance.
A+
--
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that is empty. ;-)(Confucius)
NOTE: I do not respond to PMs for technical questions.