Excel Window & UserForm Minimize and Maximize Functions

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.

我目前在excel实例的顶部打开了一个userform，并且它被设置为除了userform之外你不能与任何东西进行交互。由于无法最小化此程序，因此我在userform上创建了一个“Minimize”按钮，当单击该按钮时，会隐藏userform并按预期收缩excel窗口。

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).

但是，当我单击最小化的Excel应用程序以恢复窗口时，我希望再次显示userform并且excel窗口像以前一样出现在它后面（现在只显示excel窗口）。

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:

是否有任何我可以监听的功能或触发器，这将允许我在单击并恢复excel实例时再次显示我的用户窗体？这就是我目前最小化一切的方式：

Private Sub CommandButton15_Click()
Me.Hide
Application.WindowState = xlMinimized
End Sub

1 个解决方案

#1

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

当您显示表单时，您可能会执行以下操作：

UserForm.Show

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:

Show方法采用可选参数，是否显示Modal或Modeless形式。模态显示是默认的，不允许与工作表/工作簿对象进行交互。相反，当您显示表单时，请执行以下操作：

UserForm.Show vbModeless

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.

这将允许用户与工作表/工作簿进行交互，从而减少了对自定义按钮的需求，您无需执行Me.Hide。最小化应用程序将最小化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.

如果必须使用UserForm的vbModal显示（并且在许多应用程序中这是故意要求阻止用户与工作簿/工作表交互），请告诉我。可能有一些事件或应用程序事件可以更好地捕获最小化/最大化。

UPDATE

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.

或者，你可以做这样的事情。此方法隐藏Excel应用程序，缩小用户窗体的大小，然后在单击用户窗体并再次显示Excel应用程序时调整其大小。

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

#1