Hello,
I'm currently using a User form in a project Portfolio spreadsheet, and i'm having issues with dates (Communial groan)
I have a field with the below logic applied to it:
Code:
Private Sub Requested_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'Confirms date format, and sets to standard format if valid date
If IsDate(Requested) Then
Requested = CDate(Requested)
End If
End Sub
Private Sub Requested_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Confirms date format, and sets to standard format if valid date
If IsDate(Requested) Then
Requested = CDate(Requested)
Else
Requested.Value = ""
MsgBox "Date requested / logged must be a valid date (dd/mm/yyyy)", vbInformation + vbOKOnly, "Date Incorrect"
End If
End Sub
If a user enters a date such as "15/12/2014", i.e. it can only be format dd/mm/yyyy, everything is ok. If they enter an 'ambiguous' date, such as '01/12/2014' the form freezes on exit of the field.
If a place a break point in the code at any point, it makes no difference. The code doesn't even it get called before the freeze, it seems. the only way to get out of this is killing the excel process in task manager. killing the program itself doesn't work, as it's still expecting data in the user form.
i've tried building test cases, using the same data, but NOT in a user form, and i think it's an 'overflow' error message, but that doesn't help me. Somebody suggested changing the 'CDate' to 'Cdbl' but as i suspected, this just caused a mismatch error. Anyone had the same issue, and managed to work around it?