preventing user from entering prior date in userform

Question

Im tying to prevent a user from entering an effective date that is prior to todays date in a text box up at the top of the userform.

the logic here seems fit, but im getting the msgbox even if the date is after todays date.

Private Sub txtEffective_Date_Change()
    If IsDate(txtEffective_Date) Then
        If cdate(txtEffective_Date) < Date Then
            MsgBox "Date chosen is prior to today's date"
        End If
    End If
End Sub

I figure it has something to do with the cdate vs. date, but not entirely sure what's going wrong here.

Answer 1

The change event is firing for every keystroke, causing the issue you are experiencing. Simply move this code to another event, such as LostFocus or Validate, and you will be fine.

Edit: since this is VBA and not VB6, try the Exit event.