Coding a Cancel button

[Pacman52]

Hi all could someone advise me on a soultion to canceling a userform with data already in it please - bearing in mind my VB knowledge is very basic.

So I have a button on a form 'frm1'. The form has 23 textboxes all of which a user is required to put some data in. Once all the textboxes have been completed the user clicks the 'Save' button and the data is written to the worksheet. (all of this is working fine)

But if a user wants to cancel the form without saving anything, they should simply click the Cancel button.

What I have so far is the Cancel button coded to run a msgbox "are you sure you want to cancel etc etc" and the VB Yes No option set.

The problem I'm having is as all the textboxes on the form require something entered in each of them as soon as I press the cancel button, the code behind the first textbox runs i.e. a msgbox "you must enter a value".

So my question is how do I stop this happening? Do i need to 'disable' the textboxes first or is there a simple way to do this?

Many thanks for any advice offered

Paul

 

[RoryA]

No, I mean the full code of the button.

 

[Pacman52]

sorry Rory I misunderstood you full button code is

Private Sub cmbCancel_Click()
    
    Cancelled = True
    
    Dim AnswerYes As String
    Dim AnswerNo As String
    
    AnswerYes = MsgBox("Are you sure you want to cancel this?" & vbCrLf & _
    "If you select Yes, you will lose all data entered on the form.", vbYesNo)
    
    If AnswerYes = vbYes Then
    Unload Me
    
    End If

End Sub

 

[RoryA]

Oh wait - is the button's TakeFocusOnClick property set to true? If it is, the Exit event of your current textbox will be triggered before the button code. Set it to False.

 

[Pacman52]

I haven't used the 'TakeFocusOnClick property' anywhere in the module or form but using the form called 'frmNewEvent' as an example (where the cancel button problem also occurs), this is all the code for the entire form:-

Option Explicit

    Dim Cancelled As Boolean
 
Private Sub UserForm_Activate()

    cmbSave.Enabled = False
    
End Sub

Private Sub txtEventName_Change()
        
    Me.txtEventName.Value = Application.WorksheetFunction.Proper(Me.txtEventName.Value)
        
End Sub
    
Private Sub txtEventName_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If Cancelled Then Exit Sub

    If txtEventName.Text = "" Then
    Cancel = True
    MsgBox "You must enter an Event Name", vbExclamation, "Event Name Is Required"
    txtEventName.SetFocus
    txtEventName.BackColor = RGB(3, 252, 136)
    
    Else
    
    txtEventName.BackColor = RGB(255, 255, 255)
    
    End If

End Sub

Private Sub sDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If Cancelled Then Exit Sub

    If sDate.Text = "" Then
    Cancel = True
    MsgBox "You must enter the event start date.", vbExclamation, "Start Date Required"
    sDate.SetFocus
    sDate.BackColor = RGB(3, 252, 136)
    
    Else
    
    sDate.BackColor = RGB(255, 255, 255)
    cmbSave.Enabled = True
    
    End If
    
End Sub

Private Sub sDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    
    If Cancelled Then Exit Sub
    
    If Not IsDate(Me.sDate) Then
    MsgBox "Please enter the date in the correct format e.g. 'DD/MM/YYYY'", vbExclamation, "Invalid Entry"
    Cancel = True
    Me.sDate.SetFocus
    Me.sDate = ""
    
    Else
    
    Me.sDate = Format(CDate(Me.sDate), "DD/MM/YYYY")
    
    End If

End Sub

Private Sub sDate_Change()

    Dim txtEventName As String
    Dim sDate As Date

    Me.txtEid.Value = Trim(Me.sDate.Value) & " - " & Me.txtEventName.Value
    Me.txtEventName.Value = Application.WorksheetFunction.Proper(Me.txtEventName.Value)

End Sub

Sub ClearNewEventForm()

    txtEventName.Value = ""
    sDate.Value = ""
    txtEid.Value = ""
    
End Sub

Private Sub cmbSave_Click()

    Dim EventName As String
    Dim StartDate As Date
    Dim ECode As String
    
    EventName = txtEventName.Value
    StartDate = sDate.Value
    ECode = txtEid.Value
    
    
    
    Dim Events As Worksheet
    Set Events = ThisWorkbook.Sheets("Events")
    
    
    Dim NextRow As Integer
    NextRow = Events.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    Events.Range("B" & NextRow).Value = EventName
    Events.Range("C" & NextRow).Value = StartDate
    Events.Range("D" & NextRow).Value = ECode
    

    Call ClearNewEventForm
    
    Unload Me
    
End Sub

Private Sub cmbCancel_Click()
    
    Cancelled = True
    
    Dim AnswerYes As String
    Dim AnswerNo As String
    
    AnswerYes = MsgBox("Are you sure you want to cancel this?" & vbCrLf & _
    "If you select Yes, you will lose all data entered on the form.", vbYesNo + vbInformation)
    
    If AnswerYes = vbYes Then
    Unload Me
    
    End If

End Sub

 

[RoryA]

Select the Cancel button in design view and look at the properties Window:

Make sure that property is set to False.

 

[Pacman52]

Checked that and it is set to False - here are all the button property settings...

 

[RoryA]

It’s set to true in that picture

 

[Pacman52]

I'm sorry Rory - I was looking at the wrong property and confinced myself it was set to false - I've been in front of my screen for so long my eyes and mind are getting blurred with all the various pages opened but glad to say the correct property has been changed to false now and its working.

Again thank you so much for taken the time to help getting this sorted.

Paul

 

[RoryA]

No worries - glad we got it sorted out!