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]

Try :
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
Application.EnableEvents = False
TextBox1.Value = vbNullString
TextBox1.SetFocus
Application.EnableEvents = True
Exit Sub
End If
dDate = DateSerial(Year(Date), Month(Date), Day(Date))
TextBox1.Value = Format(CDate(TextBox1.Value), "dd-mmm-yyyy")
End Sub

 

[NimishK]

Footoo Dear

No Success. Did you try at your End ?

 

[footoo]

Try :

[COLOR=#333333]Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)[/COLOR]
[COLOR=#FF0000]Application.EnableEvents = False
[/COLOR][COLOR=#333333]If Mid(TextBox1.Value, 4, 2) > 12 Then[/COLOR]
[COLOR=#333333]Cancel = True[/COLOR]
[COLOR=#333333]MsgBox "Invalid date, please re-enter", vbCritical[/COLOR]
[COLOR=#333333]TextBox1.Value = vbNullString[/COLOR]
[COLOR=#333333]TextBox1.SetFocus[/COLOR]
[COLOR=#ff0000]Application.EnableEvents = True[/COLOR]
[COLOR=#333333]Exit Sub[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]dDate = DateSerial(Year(Date), Month(Date), Day(Date))[/COLOR]
[COLOR=#333333]TextBox1.Value = Format(CDate(TextBox1.Value), "dd-mmm-yyyy")[/COLOR]
[COLOR=#FF0000]Application.EnableEvents = True
[/COLOR][COLOR=#333333]End Sub[/COLOR]

If still no good, put a breakpoint on the first line (F9) then step through the code (F8) to see where the macro is being triggered twice.

 

[NimishK]

No Footoo Everything tried but still no success

It exits at Exits Sub and cant see the macro being triggered twice

 

[Norie]

How are you closing the userform?

PS Application.EnableEvents has no bearing on a userform, but what you could do is introduce your own code to disable events.

 

[NimishK]

Well Norie this is the only code in Userform.

PS Application.EnableEvents has no bearing on a userform, but what you could do is introduce your own code to disable events.

I thought so as nothing happened
What is the code to disable events. in just simple matter like this

 

[NimishK]

just tried

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

What happens is now not triggering twice Ok. but focus on textbox1 is not there anymore
Any where Cancel = False you incorporate above, the Focus from Textbox1 is not there

 

[Fluff]

Try running it as an Exit event

Private Sub TextBox1_Exit(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

 

[NimishK]

Yes Fluff tried in exit event but error at following line
Type Mismatch
TextBox1.Value = Format(CDate(TextBox1.Value), "dd-mmm-yyyy")