Search code examples
vbaexceluserform

How to close a userform when another userform or a blank space is selected in excel VBA?

Whenever people clicks on the Debit Account or Credit Account fields on the userform, another userform with a Treeview will pop-out. However, the user will always have to press cancel in order to close the userform. Is there a way for the userform to automatically close when the Voucher Entry Form userform's area is selected?

Sample Userform

Solution

  • If the question is "How to unload a user-form if it is not more the active object?" then the user-form in question must be modeless. Because else it cannot be deactivated without closing it's window. And because it is not possible opening a modeless user-form from a modal userform, the main user-form also must be modeless.

    Example:

    Do having two user-forms:

    First user-form is named "MainForm" and has one button control and the following code applied:

    Private Sub CommandButton1_Click()
 Load SubForm
 SubForm.Show vbModeless
 SubForm.Left = Me.Left + 100
 SubForm.Top = Me.Top + 100
End Sub

    Second user-form is named "SubForm" and can be empty but has following code applied:

    Private Sub UserForm_Deactivate()
 Unload Me
End Sub

    Then following Sub within a default module shows the main form:

    Sub test()
  MainForm.Show vbModeless
End Sub

    Now after MainForm is shown, SubForm can be opened by button click. And if MainForm get the active form again (gets the focus again), the SubForm will unload.