Results 1 to 6 of 6

Thread: How to detect Userform 'X' or Cancel click from sub that loaded it

  1. #1
    Board Regular
    Join Date
    Feb 2010
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to detect Userform 'X' or Cancel click from sub that loaded it

    I have a Sub that loads a userform I created. In the sub, I change a label caption in the form and reuse it using a loop to capture the user's option selection for each new context (but with the same options presented).

    Code:
    Load frmExample
    
    With ActiveSheet
        For x = 1 To UBound(vValue)
            .Cells(inputRow, vValue(x)).Select
            frmExample.lblParameter.Caption = .Cells(inputRow - 1, vValue(x)).Value
            frmExample.Show
            
            'do things depending on which option button was selected
        Next x
    End With
    I'd like to know how to tell the Sub that the 'X' close button was clicked so it doesn't continue to re-display the Userform.

    I know the UserForm_QueryClose() Sub runs when the X is clicked but I don't know how to use that to tell the "calling" Sub that it was clicked. I tried creating a Public boolean variable in the code section for the userform and setting it to True when QueryClose would run but when it would return to the "calling" Sub, the variable wouldn't remain set to True.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,281
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: How to detect Userform 'X' or Cancel click from sub that loaded it

    Try this:

    In your Sub:
    Code:
    Public wQuit As Boolean     'at the beginning of all the code
    
    
    Sub test()
      '
      Load frmExample
      With ActiveSheet
        For x = 1 To UBound(vvalue)
          .Cells(inputRow, vvalue(x)).Select
          frmExample.lblParameter.Caption = .Cells(inputRow - 1, vvalue(x)).Value
          If wQuit = False Then
            frmExample.Show
          Else
            Exit For
          End If
          'do things depending on which option button was selected
        Next x
      End With
    End Sub
    In your userform:
    Code:
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
      If CloseMode = 0 Then wQuit = True
    End Sub
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to detect Userform 'X' or Cancel click from sub that loaded it

    Hi
    Declare public parameter
    Publuc bool_ClosedByControl as boolean (declare it above the sub!)
    At the beginning of your sub, before for statement set it to false, bool_ClosedByControl=false
    Then, open code of your form, select event on the form called QuerryClose and type in there the following code
    Code:
    If closemode=vbclosecontrolmenu then
       bool_ClosedByControl=true
    Else
       bool_ClosedByControl=false
    End if
    Then, based on the value bool_ClosedByControl you can show or not show the form
    Code:
    If bool_ClosedByControl = false then frmExample.Show
    Let me know if that helps you.

  4. #4
    Board Regular
    Join Date
    Feb 2010
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to detect Userform 'X' or Cancel click from sub that loaded it

    Thanks guys! I was kind of on the right track, I just needed the variable in a higher scope.

    For anyone who finds this later, "vbclosecontrolmenu" in Mentor82's post above should actually be "vbFormControlMenu". I certainly don't hold it against him though, I had to look it up here: https://docs.microsoft.com/en-us/off...eryclose-event

  5. #5
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to detect Userform 'X' or Cancel click from sub that loaded it

    You're welcome!
    You're right I misspelt it, of course, should be vbFormControlMenu
    Regards,
    Sebastian

    "When you've eliminated the impossible, whatever remains, however improbable, must be the truth."

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,281
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: How to detect Userform 'X' or Cancel click from sub that loaded it

    Quote Originally Posted by gijimbo View Post
    Thanks guys!
    Youre welcome, thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •