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]

all the textboxes on the form require something entered in each of them

Userform controls don't have a property like that so it must be something you coded. What event (or events) are being used?

 

[Pacman52]

Hi Rory thanks for the quick reply the example pasted below is one of the textboxes but all of the others are bascially the same.

Private Sub txtInternet_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If Trim(txtInternet.Value) = "" And Me.Visible Then
    MsgBox "This box requires a numeric value." & vbNewLine & _
    "If this box is not relevent, please enter 0 (zero).", vbExclamation, "No Value Entered"
    Cancel = True
    frmEventExp.txtInternet.Value = ""
    frmEventExp.txtInternet.SetFocus
    ElseIf Not IsNumeric(txtInternet.Text) Or Trim(txtInternet.Value) = " " Then
        MsgBox "It looks like you entered a text value." & vbNewLine & _
        "Please enter only a numeric value or a 0 (zero).", vbExclamation, "Text Value Entered"
     Cancel = True
    frmEventExp.txtInternet.Value = ""
    frmEventExp.txtInternet.SetFocus
   Else
   frmEventExp.txtInternet.Value = Format(txtInternet.Value, "£0.00")

End If
End Sub

Paul

 

[RoryA]

Personally, I would do the validation in the Save routine, but, given that you have it coded this way already, you could simply add a module level Boolean variable that each validation routine tests and then simply exits if it is true. Then your cancel routine can simply set the variable to true and unload the form.

 

[Pacman52]

Thanks Rory I should point out that my knowledge of VBA is very very basic and I'm trying my best to learn usually via Youtube. saying that, I sort off understand what you mean by valdating everything in the save routine but I coded each box on its own so that the user can't move to another box before its criteria has been met rather than having the user entering data on each box that hasn't met its critera at the end of the form - mainly for 'workflow' but also because I'm trying to make data entry on the form as simple as possible so that once the data has been transferred over to the worksheet there can't be any errors. (the main user of this form has very little knowledge of spreadsheets).

What I lossed on is what you mean by 'add a module level Boolean variable' could you give me a basic example of what you mean - although I think I understand what this would be doing - I just don't know how to acheive it in code.

Thanks Paul

 

[RoryA]

At the top of the code module for the userform add:

Dim Cancelled as Boolean

then in your Cancel button code, you need a line that sets that to True:

Cancelled = True

and in each of your Exit events you'd need to add a line to check that - e.g.:

Private Sub txtInternet_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Cancelled Then Exit Sub
    If Trim(txtInternet.Value) = "" And Me.Visible Then
    MsgBox "This box requires a numeric value." & vbNewLine & _
    "If this box is not relevent, please enter 0 (zero).", vbExclamation, "No Value Entered"
    Cancel = True
    frmEventExp.txtInternet.Value = ""
    frmEventExp.txtInternet.SetFocus
    ElseIf Not IsNumeric(txtInternet.Text) Or Trim(txtInternet.Value) = " " Then
        MsgBox "It looks like you entered a text value." & vbNewLine & _
        "Please enter only a numeric value or a 0 (zero).", vbExclamation, "Text Value Entered"
     Cancel = True
    frmEventExp.txtInternet.Value = ""
    frmEventExp.txtInternet.SetFocus
   Else
   frmEventExp.txtInternet.Value = Format(txtInternet.Value, "£0.00")

End If
End Sub

 

[Pacman52]

Hi Rory, sorry for the delay in replying - many thanks for the example and explanation. I tested this in a new form and it works perfectly.

Paul

 

[Pacman52]

Hi Rory I hope you dont mind me posting a follow up to this thread It but I've hit a problem with the Cancel Button. What is happening is the first text box on the form has the following code behind it...

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)
    
    End If
    
End Sub

All the other boxes on the form are also coded so that something has to be entered

The issue I am getting is as soon as I press the cancel button the msgbox code above runs but after I enter something the cancel button works even though all the the other boxes all require something to be entered.

I tried the cancel button code on 2 different userforms and the same thing happened i.e. the cancel button would'nt work until I put something in the frist text box of each form, after I did, it worked perfectly.

Any Ideas on how to resolve this?

Many thanks Paul

 

[RoryA]

What exactly is the code for your Cancel button?

 

[Pacman52]

Its as you kindly suggested above in the thread.

At the top of the form

Dim Cancelled as Boolean

The Button

Cancelled = True

In all the various Exit events

[B]If Cancelled Then Exit Sub[/B]