Getting Msgbox Error displayed Twice when closing userform

[NimishK]

Hello

i am getting Msgbox Error Twice when i close the userform

Can someone check and help me to get rid of the MsgboxError when i close the userform

Basically if the date is wrong for which
I puroposely incorporated Cancel = True in the If EndIf so that textbox1 is set focussed

but when i close the userform after the executing the above. The msgbox Error is displayed not once but Twice

if the date is right and closing the userform then no message

Code in Userform

Option Explicit
Public dDate As Date


Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If Mid(TextBox1.Value, 4, 2) > 12 Then
        Cancel = True
        MsgBox "Invalid date, please re-enter", vbCritical
        TextBox1.Value = vbNullString
        TextBox1.SetFocus
        Exit Sub
    End If
    dDate = DateSerial(Year(Date), Month(Date), Day(Date))
    TextBox1.Value = Format(CDate(TextBox1.Value), "dd-mmm-yyyy")
End Sub

Thanks NimishK

 

[Fluff]

What did textbox1 contain?

 

[NimishK]

I just typed 23-13-2009 in textbox1 to execute the error

 

[Fluff]

That's an invalid date & so should have shown the msgbox & quit the sub.
Which means that you should never have got to the line you said gave an error.

 

[NimishK]

I tried because anything greater than 12 month Should give Error. By mistake if some types 13 or 15 in months place. Should msg the error.
My sincere request can you try the code at your end.

 

[Fluff]

I have & I got the msgbox & the code quit.

 

[NimishK]

Then Why Error Type Mismatch

 

[Fluff]

With the "date" you showed in post#12 I don't know, as I didn't get that, but if you entered something like 30-2-2009 you would because it's not a valid date.

 

[NimishK]

Yes I tried 30-2-2009. here it gave type mismatch Error. So what needs to be done specifcally here too

As per post#12 i tried 23-13-2009 to display the msgbox invalid date. It did display Msgbox when tried to close the userform again the same issue as Textbox1_beforeupdate

1. which ever event you put the code either textbox1_beforeUpdate or Textbox1_exit
it is the same MSGbox error displayed when closing the userform
Really i am going Crazy.

Three issues needs to be fixed
1. Textbox1 to be focussed if invalid date is typed to re-enter the date
2. Userform1 needs to be closed without any Msgs
3. date like 30-2-2009 needs to be controlled. As i did not realize untill you pointed

 

[Fluff]

How are you closing the userform?

 

[NimishK]

How are you closing the userform?

1. Just clicking on Close [ X] button.
I checked after typing a date correctly and closing userform. it works perfectly
this only happens when wrong date typed and closed the userform
2. have not written any code in Userform_queryclose()
3. Niether unloading Userform1 nor hiding userform1

This is it.