MsgBox appearing when not wanted

[Noz2k]

I have this code on the first textbox on a userform

Private Sub Textbox1_AfterUpdate()
If Not Textbox1.Value Like "*[ ]*" Then
MsgBox "Must include both First & Second Name"
Cancel = True
Textbox1.SetFocus
End If
End Sub

The problem I am having is because the focus is set to begin in this textbox after I send a record to excel, it generates the msgbox before the user has a chance to enter any data.

The same thing happens if the user clicks the little x in the corner of the form if the cursor is in this textbox at the time.

Is there a way to prevent this from happening?

 

[Noz2k]

From what I can work out, the restriction on the textbox is preventing the command button from doing anything at all. I think it's being blocked before the command click procedure is running. If that makes sense

 

[Andrew Poulsom]

Yes, the cmdCancel event will fire after the Textbox1_AfterUpdate, so disabling events there is too late. Why don't you just test that the TextBox isn't empty?

If Len(TextBox1.Text) = 0 Then Exit Sub

 

[Noz2k]

Because the problem then is that the user can move to the next textbox when the value of textbox1 = ""

It also wouldn't allow the user to exit the form if they had typed just a first name into the field

The only thing I want the user to be able to do if the textbox value does not = "*[ ]*" is exit the form.

I have a lot more textboxes on the form, every one is mandatory, and there are multiple restrictions on them. However the problem is that when a textbox is selected the restrictions are preventing the form from closing unless a valid value is entered.

 

[Andrew Poulsom]

Have you tried using the Exit event, or validating your TextBoxes in your OK button click event procedure?

 

[Noz2k]

Yes have tried the exit event

and the Ok click event does prevent any textboxes from being blank.

The problem with putting all the restrictions there is that some are quite complex, and if memory serves me correctly, all the if's and else on my ok click event are already quite confusing.

I'm thinking that maybe I could use some image controls to show either a tick or a cross if the textbox is filled in correctly so the user can see before clicking the ok button. Then just applying all my restrictions on the ok click event, even if that might be a bit of a headache.

Seems like the only way I can see to do it?

 

[Norie]

Can you explain what you are trying to do in simple terms?

What are these complex 'restrictions'?

Perhaps it might be worth taking a look at them if they are becoming confusing.

Also what other code do you have?

You seem to be saying you have code for validation in the OK button click event as well as validation code for the individual textbox(s).

 

[Noz2k]

In simple terms, when I click on the command button "cancel" I want the form to close. At the moment because of the restrictions on the exit events of some textboxes they prevent this from happening unless the value in the textbox is valid.

I don't know how complex they are really, but some are quite long for example

Private Sub txtDateOfAccident_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Not txtDateOfAccident.Value Like "##[/]##[/]####" Then
 
        MsgBox "Must Be Format dd/mm/yyyy"
        txtDateOfAccident.SetFocus
        txtDateOfAccident.Value = ""
        Cancel = True
 
'Specifies the format of the textbox, generates error message and clears textbox if wrong
 
    Else:
 
    If Mid(txtDateOfAccident.Value, 4, 2) < 13 And Mid(txtDateOfAccident.Value, 4, 2) > 0 Then
 
            If Mid(txtDateOfAccident.Value, 4, 2) = "04" Or Mid(txtDateOfAccident.Value, 4, 2) = "06" Or Mid(txtDateOfAccident.Value, 4, 2) = "09" Or Mid(txtDateOfAccident.Value, 4, 2) = "11" Then
 
                If Left(txtDateOfAccident.Value, 2) > 30 Or Left(txtDateOfAccident.Value, 2) < 1 Then
                    MsgBox "Day must be valid"
                    txtDateOfAccident.SetFocus
                    Cancel = True
 
                End If
 
 'Checks if the month of the date is a 30 day month
 
        Else:
 
            If Mid(txtDateOfAccident.Value, 4, 2) = "02" Then
 
                If ((Mid(txtDateOfAccident.Value, 7, 4) \ 4) * 4) = ((Mid(txtDateOfAccident.Value, 7, 4) / 4) * 4) Then
 
                    If Left(txtDateOfAccident.Value, 2) > 29 Or Left(txtDateOfAccident.Value, 2) < 1 Then
                        MsgBox "Day must be valid"
                        txtDateOfAccident.SetFocus
                        Cancel = True
                    End If
 
  'Checks if the month of the date is Febuary and what to do if it is a leap year
 
                Else:
 
                       If Left(txtDateOfAccident.Value, 2) > 28 Or Left(txtDateOfAccident.Value, 2) < 1 Then
                        MsgBox "Day must be valid"
                        txtDateOfAccident.SetFocus
                        Cancel = True
                    End If
                End If
 
  'Specifies what to do if it's not a leap year
 
             Else:
 
 
 
                If Left(txtDateOfAccident.Value, 2) <= 31 And Left(txtDateOfAccident.Value, 2) > 0 Then
                txtTimeOfAccident.SetFocus
 
             Else:  MsgBox "Day must be valid"
                    txtDateOfAccident.SetFocus
                    Cancel = True
 
   'Specifies what to do if the month is a 31 day month
 
        End If
        End If
        End If
 
    Else:    MsgBox "Month must be 1-12"
        txtDateOfAccident.SetFocus
        Cancel = True
 
  'Specifies that the month value must be between 1 and 12
 
    End If
    End If
End Sub

I understand there are probably better ways of doing certain things, but I'm only just learning, and have been using these forums all week to aid me in building the code.

The ok command button sends the data in the textboxes to a table. It also has the userform initialize code written into it, as using 'Call Userform_Initialize' added duplicate items to my combo boxes, so that's what I was advised to do.

The only code validation in the ok button is to prevent the textboxes from being blank, otherwise you could send the form without entering all textboxes and therefore not encountering their individual restrictions. This also has other if statements in it however as some textboxes are only visible dependant on what option boxes are chosen.

The ok button will also later be used to build reports based on the data in the worksheet (once I have the user input working as desired).

I hope this makes sense

 

[Noz2k]

I think the way I'm going to get around the problem is by using image controls.

Instead of restricting the user from moving to the next textbox, I will instead use 'ticks' and 'crosses' to indicate whether the input is valid or invalid.

I'll then use a click procedure on the X's to show the error and on the click event of the OK button I'll just say if (any of the crosses are visible) then give error message.

I think that should work

 

[Norie]

So what is all that code meant to do in simple terms?

Check that the user has entered a valid date?

Why not use IsDate, and perhaps DateValue?

Very simple check, won't check for a specific format but will check a valid date has been entered.

Private Sub txtDateOfAccident(Cancel As MSForms.Boolean
 
  If Not IsDate(txtDateOfAccident) Then
      Msgbox "Please enter a valid date"
      Cancel = True ' stop the user moving to the next control
   Else
    ' valid date has been entered 
    ' any other validation can be done here, see below for an example
   End If
 
End Sub
 
' check date not in future
    If DateValue(txtDateOfAccident) > Date() Then
           Msgbox "You've entered a date in the future"
           Cancel = True
    End If

If you do need to have the date in a specific format you could just do that once you've validated it.

' validation - passed
 
txtDateOfAccident.Text =Format(DateValue(txtDateOfAccident), "dd/mm/yyyy"

If you do want to indicate an entry is incorrect don't use image controls, just change the background colour of the textbox to something like red.

I think I'd even notice that.

By the way I don't think you need to call the Initialize event, if you want to reset a form try this.

Unload Me ' unload the current form
 
MyUserForm.Show ' then show the form again, with all the textboxes clear, comboboxes reset etc

Hope I've kind of understood a but what you are trying to do.

 

[Noz2k]

Thanks, that is so much simpler. Wasted a lot of time figuring out how to do it that way, was unaware there was a built in function.

I'm going to stick with the image controls (mainly because I've already implemented them now).

The only thing I can't get to work is putting the argument to check whether the date is in the future into the change event.