Stephen Bullen FormFun

[RB Smissaert]

I had a look at the workbook FormFun from Stephen Bullen's website:
http://www.oaltd.co.uk
With this I can add a minimize and maximize button to a userform.
Would it be possible to run these buttons with a keyboard shortcut, say
F9 for minimize and F10 for maximize?
Thanks for any advice.

RBS

 

[Stephen Bullen]

Hi RB,

I had a look at the workbook FormFun from Stephen Bullen's website:
http://www.oaltd.co.uk
With this I can add a minimize and maximize button to a userform.
Would it be possible to run these buttons with a keyboard shortcut, say
F9 for minimize and F10 for maximize?
Thanks for any advice.

Possibly, but I've never tried it (mainly because I don't really like the
way the minimize and maximize of a userform works). Obviously, we can use
Application.OnKey to respond to the F9 and F10 keys, but we'd have to use
API calls to tell the form to maximize/minimize. The easiest way is
probably to use ShowWindow:

Private Declare Function ShowWindow Lib "user32" Alias "ShowWindow"
(ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

Private Const SW_MAXIMIZE = 3
Private Const SW_MINIMIZE = 6


ShowWindow hWndForm, SW_MAXIMIZE

where hWndForm is the form's window handle, as found in the formfun class
module.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[RB Smissaert]

Stephen,

Thanks, will give that a try.
If find minimize is fine, but maximize I will have to use my own routine
that does this as you did
with formfun. What didn't you like about the userform minimize?

RBS

 

[Stephen Bullen]

Hi RB,

What didn't you like about the userform minimize?

That it minimizes to the bottom-left of the Windows desktop rather than
either (a) the Excel desktop (like a worksheet) or the task bar (like
other apps).

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[RB Smissaert]

This very simple bit of code will give me the minimize and maximize buttons
on
my userform:

Option Explicit
Private Declare Function FindWindow& Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName$,
ByVal lpWindowName$)
Private 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 ShowWindow& Lib "user32" _
(ByVal hWnd&, ByVal nCmdShow&)
Private hWnd As Long

Private Sub UserForm_Initialize()
hWnd = FindWindow(vbNullString, Me.Caption)
SetWindowLong hWnd, -16, &H20000 Or &H10000 Or &H84C80080
End Sub

Private Sub UserForm_Activate()
' Minimize in TaskBar (activate following lines)
'ShowWindow hWnd, 0
'SetWindowLong hWnd, -20, &H40101
'ShowWindow hWnd, 1
EnableWindow FindWindow(vbNullString, Application.Caption), 1
End Sub

Now how could I use this and add 2 more things:
Run a Sub when the maximize button gets pressed.
I am happy with the minimize result and would leave this as it is.
Make keyboard shortcuts for both the minimize and maximize actions.
I did post this in the VB API group, but maybe because it is VBA I got no
reply.
I am sure that if there was an answer for this many VBA coders would be
interested
in this.


RBS

 

[Jamie Collins]

Run a Sub when the maximize button gets pressed.

Subclass the userform and trap the WM_GETMINMAXINFO or WM_SIZE message.

Make keyboard shortcuts for both the minimize and maximize actions.

In a macro, use ShowWindow with the SW_MAXIMIZE or SW_SHOWMAXIMIZED
message and assign the macro to a keyboard shortcut.

Jamie.

--

 

[RB Smissaert]

OK, thanks for the reply, I will see if I can figure it out.
Could you by any chance point me to a ready made example?
At least it sounds it all doable and that would be very useful.

RBS