Results 1 to 2 of 2

Thread: Exit main macro if frmMsgBox equals no, else run main macro
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Exit main macro if frmMsgBox equals no, else run main macro

    Hello!
    I have a large main macro. I would like for the very first thing it does is to call a custom msg box that will ask the user whether or not they wish to exit the macro, or if they wish to continue.
    If they wish to exit, I need to exit out of the custom msgbox sub AND also out of the main macro.
    If they wish to continue, then I need to leave the custom msgbox and continue through the main macro.
    This is what I've cobbled together from several sources:

    Code:
     Sub frmMsgBoxWCE()Dim myForm As frmCustomMsgBox
      Set myForm = New frmCustomMsgBox
      myForm.Caption = "West Complex  Unit E  Staff Callout"
            myForm.Show
            Select Case myForm.Tag
              Case 1
                GoTo lbl_Exit
              Case 2
              Exit Sub
            End Select
            'End If
      
    lbl_Exit:
        Unload myForm
        Set myForm = Nothing
    Exit Sub
    End Sub
    Case 1 (the "I wish to exit") is button 1 on my form.
    Case 2 (the "I wish to continue") is button 2 on my form.

    I hope this makes sense to someone. I have my fingers crossed...and I'll eat another cookie as moral support.

    -Christine

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,604
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Exit main macro if frmMsgBox equals no, else run main macro

    You could put everything inside the userform code module. This example has a Lable and the two command buttons.

    Code:
    ' in code module for frmCustomMessageBox
    
    Private Sub CommandButton1_Click()
        Rem cancel button
        Unload Me
    End Sub
    
    Private Sub CommandButton2_Click()
        Rem OK button
        Me.Tag = "OK"
        Me.Hide
    End Sub
    
    Private Sub UserForm_Initialize()
        Rem these can be set at design time (or passed as arguments to the Value property)
        Me.Caption = "West Complex  Unit E  Staff Callout"
        Me.CommandButton1.Caption = "Cancel"
        Me.CommandButton2.Caption = "OK"
    End Sub
    
    Public Function Value(Optional Prompt As String) As VbMsgBoxResult
        With Me
            .Label1 = Prompt
            .Show
        End With
        
        With frmCustomMessageBox
            If .Tag = "OK" Then
                Value = vbOK
            Else
                Value = vbCancel
            End If
        End With
        Unload frmCustomMessageBox
    End Function
    It would be called from the main routine like this

    Code:
    If frmCustomMessageBox.Value("Shall I Continue?") = vbCancel Then Exit Sub
    Note that pressing the corner X is the same as Cancel button.

    Also, you can pass other arguments to the Value property depending on how custom you want the message box.
    Last edited by mikerickson; Sep 22nd, 2019 at 09:19 PM.

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
  •