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?

 

[Andrew Poulsom]

You need to disable events:

http://www.cpearson.com/Excel/SuppressChangeInForms.htm

 

[Norie]

You could use a different event to check what's been entered in the textbox.

eg the Exit event.

 

[Noz2k]

Thanks for your help.

However I don't think that I am fully understanding this.

What I have changed is this:

the Cancel command button now says

Private Sub cmdCancel_Click()
 
    Application.EnableEvents = False
    Unload Me
End Sub

I have added

Application.EnableEvents = True

to the Userform Initialize Event

and I have added this code

Private Sub Textbox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Application.EnableEvents = False Then
          Exit Sub
      End If
End Sub

but I'm obviously doing something wrong as it isn't working

 

[Andrew Poulsom]

Look at the link I posted again and read what it says.

 

[Noz2k]

I have read through what it says. The problem is that I do not understand it. Have only been using VB a week, so everything I am doing is new to me.

When it says

To suppress events in a form, you can create a variable at the form's module level called "EnableEvents" and set that to False before changing a property that will cause an event to be raised.

What does at the Module level mean?

I just click on the tab with the objects in my userform, clicked on General and then placed

 Public EnableEvents As Boolean

at the top.

added when cmdCancel is clicked Me.EnableEvents = False

when Userform is Initialized Me.EnableEvents = True

and added to the after_update for textbox1

If Me.EnableEvents = False
Then Exit Sub

Where am I going wrong? What step am I missing out?

 

[Andrew Poulsom]

All the code in Chip's examples goes in the UserForm module. Example:

Public EnableEvents As Boolean
 
Private Sub UserForm_Initialize()
'   Enable events
    Me.EnableEvents = True
'   Disable events
    Me.EnableEvents = False
'   Do something with TextBox
    TextBox1.Text = ""
'   Reenable events
    Me.EnableEvents = True
End Sub
 
Private Sub Textbox1_AfterUpdate()
'   Exit if events disabled
    If Me.EnableEvents = False Then Exit Sub
    If Not TextBox1.Value Like "*[ ]*" Then
        MsgBox "Must include both First & Second Name"
        TextBox1.SetFocus
    End If
End Sub
 
Private Sub CommandButton1_Click()
'   Disable events
    Me.EnableEvents = False
'   Do something with TextBox
    TextBox1.Text = ""
'   Reenable events
    Me.EnableEvents = True
End Sub

 

[Noz2k]

What is the UserForm Module? Because I think I'm doing all that with a lot of code in between but it's still not working. All it seems to have done is disable the restrictions on my textbox. Which suggests to me that the EnableEvents is showing as False, but since Userform Initialize ends with Me.EnableEvents=True I don't understand why this is.

Does the order of the sub procedures make a difference? Because Userform_Initialize is right at the bottom in my code

 

[Andrew Poulsom]

The UserForm module is the module that contains your UserForm code - what you see if you right click on the UserForm and choose View Code. The order of the procedures in that module is of no consequence.

 

[Noz2k]

Then I don't see what I'm doing wrong.

It works in terms of putting the restriction on my textbox, if I change the value from true or false. If False it restricts if True it doesn't.

What isn't working is when I click my command button it doesn't seem to change enableevents to false.

this is the code in the command button

Private Sub cmdCancel_Click()
 
    Me.EnableEvents = False
 
    Unload Me
 
    Me.EnableEvents = True
 
End Sub

When I click the command button I just get the error message from the textbox

I really appreciate that you're trying to help btw, so thank you