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

 

[footoo]

Maybe :

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If textbox1.Value = vbNullString Then
    Exit Sub
ElseIf Not IsDate(CDate(textbox1.Value)) Then
    Cancel = True
    textbox1.Value = vbNullString
    textbox1.SetFocus
    MsgBox "Invalid date, please re-enter", vbCritical
    Exit Sub
End If
dDate = DateSerial(Year(Date), Month(Date), Day(Date))
textbox1.Value = Format(CDate(textbox1.Value), "dd-mmm-yyyy")
End Sub

 

[Fluff]

Another option

Option Explicit
Public dDate As Date
Dim DisableEvents As Boolean


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   If DisableEvents Then Exit Sub
   If Mid(TextBox1.Value, 4, 2) > 12 Or Not IsDate(Me.TextBox1.Value) 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

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
DisableEvents = True
End Sub

 

[NimishK]

Fluff
Yes 100% accurate.
Thanks you so much

Footoo Dear,

Tried yours got Type Mismatch at
ElseIf Not IsDate(CDate(TextBox1.Value)) Then

Sincere request Footoo Please try the code at your End and reply. Anyways Thanks

 

[Fluff]

You're welcome & thanks for the feedback

 

[footoo]

Maybe :

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If textbox1.Value = vbNullString Then
    Exit Sub
ElseIf Not IsDate(CDate(textbox1.Value)) Then
    Cancel = True
    textbox1.Value = vbNullString
    textbox1.SetFocus
    MsgBox "Invalid date, please re-enter", vbCritical
    Exit Sub
End If
dDate = DateSerial(Year(Date), Month(Date), Day(Date))
textbox1.Value = Format(CDate(textbox1.Value), "dd-mmm-yyyy")
End Sub

Don't know how it happened but the Msgbox line got moved. Should be :

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If textbox1.Value = vbNullString Then
    Exit Sub
ElseIf Not IsDate(CDate(textbox1.Value)) 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

 

[footoo]

Footoo Dear,

Tried yours got Type Mismatch at
ElseIf Not IsDate(CDate(TextBox1.Value)) Then

Sincere request Footoo Please try the code at your End and reply. Anyways Thanks

ElseIf Not IsDate(TextBox1.Value) Then

 

[NimishK]

Yes footoo this time perfect Dear. One cheers to you too Thankx