5

I currently have a userform open on top of an excel instance and it is set so you can't interact with anything except for the userform. Since there is no way to minimize this program, I created a 'Minimize' button on the userform that, when clicked, hides the userform and shrinks the excel window as expected.

However, when I click on the minimized excel application to restore the window, I would like both the userform to appear again and the excel window appear behind it as before (right now only the excel window appears).

Is there any function or trigger I can listen for which will allow me to show my userform again when the excel instance is clicked and restored? This is how I am currently minimizing everything:

Private Sub CommandButton15_Click()
Me.Hide
Application.WindowState = xlMinimized
End Sub
Glynbeard
  • 1,389
  • 2
  • 19
  • 31

1 Answers1

7

When you display the form, you are likely doing something like:

UserForm.Show

The Show method takes an optional argument, whether to display the form Modal or Modeless. Modal display is default, and does not allow interaction with the worksheet/workbook objects. Instead, when you display the form, do:

UserForm.Show vbModeless

This will allow the user to interact with the worksheet/workbook, which alleviates the need for your custom button and you will not need to do Me.Hide. Minimizing the Application will minimize the UserForm. Maximizing the Application will re-display the workbook and the userform.

If you must use the vbModal display of the UserForm (and in many applications this is a deliberate requirement to prevent user from interacting with the Workbook/Worksheets), let me know. There may be some events or application events that could better trap the minimize/maximize.

UPDATE

Alternatively, you could do something like this. This approach Hides the Excel Application, and shrinks the size of the UserForm, then resizes it when you click back on the user form and shows the Excel Application again.

Private Sub CommandButton15_Click()
'Hide Excel and minimize the UserForm
    Application.Visible = False
    Me.Height = 10
    Me.Width = 10

End Sub

Private Sub UserForm_Click()
'Show Excel and resize the UserForm
    Application.Visible = True
    Me.Height = 180
    Me.Width = 240
End Sub

Private Sub UserForm_Terminate()
'Ensure that the Application is visible and the form resized
    UserForm_Click
End Sub
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • Yes, I do not want the user to be able to interact with the actual Excel worksheets etc. if possible. Keeping it vbModal would be ideal. – Glynbeard Aug 16 '13 at 15:03
  • This looks like it should do what you want, http://social.msdn.microsoft.com/Forums/office/en-US/94dbe32c-d0b2-4875-b098-f27aabd2b1ae/excel-deactivate-event but ideally this sort of thing is usually avoided. It's not usually ideal to minimize the application and these WinAPI calls are tricky to work with. There is no native Event in Excel that traps the Application minimize/maximize. – David Zemens Aug 16 '13 at 15:26
  • I have an alternative idea that might work. I will update my answer. – David Zemens Aug 16 '13 at 15:27
  • 1
    Although this wasn't exactly what I had in mind, I think this will work. Or at least give me enough of an idea of how to progress with this issue. Thanks! – Glynbeard Aug 16 '13 at 16:02