Msgbox will not close

brinabri · Apr 7th 2012

Hi,

I've been trying to get this to work for hours, but with no luck. I have a userform [UserForm1] which appears on start up of my excel spreadsheet

The userform requires the individual put in their email address and then press a command button to continue

I want a message box to pop up if the email address is blank when the command button is pressed.

So far I can get the message box to appear, but getting it to close is another story.

Since it won't close when I click ok on the message box, I have to exit the spreadsheet entirely (through ctr alt delete) and start again. What am I doing wrong?

This is my code

Code

Private Sub IAccept_Click[/I]IAccept = TrueIf TextBox3.Text = "" ThenMsg = "You must enter an email address to continue"Ans = MsgBox(Msg, vbOKOnly)If Ans = vbOK Then Cancel = TrueUnload UserForm1End Sub

Thankyou

jproffer · Apr 7th 2012

Re: Msg Box will not close

Looks like you were overthinking the problem.

Try this out and see if it does what you want it to do.

Code

Private Sub IAccept_Click()
If TextBox3.Text = "" Then
    Msg = "You must enter an email address to continue"
    MsgBox Msg
        TextBox3.SetFocus
Else
     IAccept = True
End If
End Sub

brinabri · Apr 7th 2012

Re: Msg Box will not close

That's just done the job. Thank you so much.

Added "Unload UserForm1" Before End If and it worked a treat.

Sabrina

**mikerickson** · Apr 7th 2012

Re: Msg Box will not close

I'm not clear about a few things:
1) is IAccept the name of a CommandButton? If not, how is the sub IAccept_Click run.
2) the line

Code

IAccept = True

is there also a variable IAccept? If so, what is its scope?
3) the variable Cancel. What is its purpose? If you are trying to pass that to the UserForm_QueryClose event, that won't work.

Assuming that there is a command button named IAccept and a public variable with the same name: there are a couple of ways you could go.
You could put code like this behind the command button.

Code

Private Sub IAccept_Click()
    If TextBox3.Text = vbNullString Then
        MsgBox "You must enter an e-mail address."
    Else
        IAccept = True
        Unload Me
    End If
End Sub

or you could disable the command button until something is entered into TextBox3.

Code

Private Sub IAccept_Click()
    IAccept = True
    Unload Me
End Sub


Private Sub TextBox3_Change()
    IAccept.Enabled = CBool(Len(TextBox3.Text))
End Sub


Private Sub UserForm_Initialize()
    IAccept.Enabled = False: Rem could be set at design time
End Sub

Display More

I have a couple of other concerns.

A) Since IAccept_Click unloads the userform when something is in TextBox3, that entry will be gone unless stored somewhere (a cell?) by the TextBox3.Change event or something similar. Is that where the verification of e-mail format is done?

B) What if the user doesn't want to enter their e-mail address?
How do they get rid of the userform? (When they do get rid of the UF without entering their e-mail address, do you want the workbook to close)
Users a situation where they must give you personal information or they can't do anything with their computer take extrem steps to get out of that trap. (Like force quitting, unplugging things, calling tech support...)

jproffer · Apr 7th 2012

Re: Msg Box will not close

I removed the Unload command because I figured you wanted them to go back and enter their email address. But however it suits you, it suits me

Glad it worked out.

Msgbox will not close

Participate now!

Share