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

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

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" wrote in message
...
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

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

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


"RB Smissaert" wrote in message
...
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

RB Smissaert wrote:
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.

--

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



"Jamie Collins" wrote in message
oups.com...

RB Smissaert wrote:
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.

--