VBA - How do I stop a Routine if the user cancels one of the Userforms

[mrmcmillanrevis]

I have a Sub Routine to help users add a new tab to a spreadsheet. It starts by opening a userform where they enter the name of the new tab. The userform then closes and the VBA then searches to see if there is already a tab with that name - if there is the user gets a message to say it already exists and the sub stops. If there is not already a tab with that name, they then see a new Userform asking for more information. There are then a couple more steps which are needed before creating the new tab. However, I've noticed that if the user clicks the 'red X' to close any of the userforms, the Sub Routine carries on running and this leads to some incomplete information being entered.

So, my question is - what do I need to do so that if the user clicks the red X in any of the user forms it effectively jumps back to the main Sub and tells it to just stop running completely?

I am very new to userforms, so am just learning.

It feels like there should be something that effectively says "IF the 'red X' is clicked in this userform then stop the whole sub"

Many Thanks

 

[dmt32]

Hi,
providing you have another method (Close Button?) to close your userform you can disable the red X if this would solve your issue

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = CloseMode = 0
End Sub

But repeat, only do this if you have another method to unload the userform

Dave

 

[mrmcmillanrevis]

Hi,
providing you have another method (Close Button?) to close your userform you can disable the red X if this would solve your issue

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = CloseMode = 0
End Sub

But repeat, only do this if you have another method to unload the userform

Dave

Hi,

If I were to do this, where would the code go? I presume it would be attached to the Userform, rather than the main sub?

 

[dmt32]

Hi,

If I were to do this, where would the code go? I presume it would be attached to the Userform, rather than the main sub?

yes it goes in your Userforms code page but do heed the warning - make sure you have another method to unload the form

Dave

 

[mrmcmillanrevis]

Also, if I set up a 'cancel' button in the userform, how do I tell the main sub that

yes it goes in your Userforms code page but do heed the warning - make sure you have another method to unload the form

Dave

Ok, I am setting up a 'cancel' button as a commandbutton. But my question is now, how do I tell the main sub that the cancel button was clicked? From what I understand a 'click' is not stored in memory.

To make the main sub stop after cancel has been pressed I imagine I want something like "IF 'cancel button was clicked' Then Exit Sub", but I'm not sure how to reference this?

 

[dmt32]

To make the main sub stop after cancel has been pressed I imagine I want something like "IF 'cancel button was clicked' Then Exit Sub", but I'm not sure how to reference this?

To answer the question it would be helpful if you could share your code

Dave

 

[mrmcmillanrevis]

My main sub starts off like this:

Sub InsertNewPage()
    
Dim Sht As Worksheet
Dim NewCourseCode As String

'The 'NewCourseForm' is the userform where they are asked to enter data
'which will become the name of a new tab in the spreadsheet.
NewCourseForm.Show

'This is the bit I'm getting stuck on. I want to say if they click cancel the sub ends.
'If NewCourseForm.cmdCancel1_Click = True Then Exit Sub

'This bit of code is designed to ensure they have to enter a value
If NewCourseForm.txtCourseCode.Value = "" Then
MsgBox "You must Enter a Course Code"
End If

'If they don't enter a value the sub ends and they have to start again.
If NewCourseForm.txtCourseCode.Value = "" Then Exit Sub

 

[dmt32]

Hi
untested but see if following helps you

Make a backup & then place ALL following codes in your userforms code page

Dim UserAction As Variant
Private Sub cmdCancel1_Click()
    UserAction = True
    Me.Hide
End Sub

Private Sub cmdEnter_Click()
    UserAction = Me.txtCourseCode.Value
    Me.Hide
End Sub

Private Sub txtCourseCode_Change()
    Me.cmdEnter.Enabled = Len(Me.txtCourseCode) > 0
End Sub

Private Sub UserForm_Initialize()
        Me.cmdEnter.Enabled = False
        Me.txtCourseCode.SetFocus
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = CloseMode = 0
End Sub

Function NewCourse() As Variant
    Me.Show
    NewCourse = UserAction
    Unload Me
End Function

I have guessed the name of your enter button shown in bold - this should be change as required
Note the variable at the TOP - this must be placed at very TOP of your forms code page OUTSIDE any procedure.

Your updated code in standard module

Sub InsertNewPage()
    Dim NewCourseCode As Variant
    
    'If NewCourseForm.cmdCancel1_Click = True Then Exit Sub
    
    NewCourseCode = NewCourseForm.NewCourse
    
    If NewCourseCode = True Then
    
        MsgBox "Cancelled"
    
        Exit Sub
        
    Else
    
    'rest of code here
    MsgBox NewCourseCode
        
    End If

End Sub

Dave