VBA - need to minimize Excel from UserForm

[Guest]

I have a VBA application that displays a UserForm that has a Minimize button
on it. When I click it, the UserForm minimizes as it should but the Excel
workbook that contains the UserForm does not. I can not even click on the
minimize button on the workbook because it is grayed out (I think because the
UserForm has focus).

How do I make the Excel workbook that contains the UserForm that was
launched also minimize when I minimize the UserForm?

Thanks,

Gary

 

[Guest]

to the best of my knowledge, no event is fired when a userform is minimized,
so you probably should hide the application when the form is shown and
display it when it is hidden or unloaded.

 

[Jake Marx]

Hi Gary,

I have a VBA application that displays a UserForm that has a Minimize
button on it. When I click it, the UserForm minimizes as it should

First of all - how are you adding a minimize button on the UserForm? Is it
custom code that resizes the UserForm?

but the Excel workbook that contains the UserForm does not. I can
not even click on the minimize button on the workbook because it is
grayed out (I think because the UserForm has focus).

I don't think you'll be able to minimize the Excel application window unless
the UserForm was shown modelessly:

UserForm1.Show vbModeless

How do I make the Excel workbook that contains the UserForm that was
launched also minimize when I minimize the UserForm?

If your code to minimize the UserForm is resizing it, you could try using
the Resize event of the UserForm.

Private Sub UserForm_Resize()
Application.WindowState = xlMinimized
End Sub

If you are using API calls to minimize the UserForm, I'm not sure that the
Resize event will fire when it's minimized. You would probably have to trap
the event using more API calls, but I'm not sure about that.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[Michel Pierron]

Hi gary,
Private Declare Function IsIconic& Lib "user32" (ByVal hWnd&)

Private Sub UserForm_Resize()
Dim s&: s = Application.WindowState
Dim h&: h = FindWindow(vbNullString, Application.Caption)
If IsIconic(hWnd) Then
Application.WindowState = xlMinimized
Else
Application.WindowState = s
End If
End Sub

MP