UserForm Textbox date between TODAY and 60 days earlier

[03856me]

On my user form, I would like the input limited to between TODAY and 60 days earlier.
I know how to write a formula for this on a worksheet, but not in a Userform.

Thank you for your help.

    Private Sub txt_date_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        'If txt_date = vbNullString Then Exit Sub
    
        If IsDate(txt_date) Then
            txt_date = Format(txt_date, "Short Date")
        Else
            MsgBox "Not a valid date, try again!"
            Cancel = True
            txt_date.Value = ""
            frm_dataInput.txt_date.SetFocus
        End If
 
    End Sub

 

[NoSparks]

Cancel = True is supposed to cancel moving on and keep the focus where it is but have always had trouble with it in the Exit event
as it seems focus leaves the text box anyway and when/if can't be found (even when you try to set focus) I think it's the form itself that ends up with focus.
I would use BeforeUpdate

Private Sub txt_date_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    ' allow to continue if blank
    If txt_date = "" Then Exit Sub
    ' check what was entered
    If IsDate(txt_date) Then
        If DateValue(txt_date) <= Date And DateValue(txt_date) >= Date - 60 Then
            ' it's an OK date
            txt_date = Format(txt_date, "Short Date")
        Else
            ' it's not an OK date
            MsgBox "Not a valid date, try again!"
            Cancel = True
            txt_date.Value = ""
        End If
    Else
        ' it's not a date
        MsgBox "Sorry, that's not a date"
        Cancel = True
        txt_date.Value = ""
    End If
End Sub

Hope that helps

 

[Sahak]

Hi,
Try this, Add to your code

            Dim UzerDt As Date
            Dim CurrDt As Date
            Dim DtDif As Single
            UzerDt = CDate(txt_date.Value)
            CurrDt = Date
            If CurrDt - UzerDt > 60 Then
                MsgBox "Ivalid date, Over-Limit!"
                Cancel = True
                txt_date.Value = ""
                frm_dataInput.txt_date.SetFocus
            End If

 

[03856me]

That bit of code worked perfectly.....thank you

 

[Sahak]

You are welcome

 

[NoSparks]

@Sahak
you should check your suggestion for the possibility of tomorrow's date being entered

 

[Sahak]

Hi NoSparks, thank you for your reply.
He was asking for between TODAY and 60 days earlier

 I would like the input limited to between TODAY and 60 days earlier

​

 

[NoSparks]

He was asking for between TODAY and 60 days earlier

That's right and that's what you programmed for, in the earlier direction only.
No date in the future will be between TODAY and 60 days earlier
and therefore should also display the invalid date message.

 

[Sahak]

OK, let's add another "If"

           If CurrDt < UzerDt Then
                MsgBox "Invalid date, Grater than current!"
                Cancel = True
                txt_date.Value = ""
                frm_dataInput.txt_date.SetFocus
            End If

complete VBA code will be

If IsDate(txt_date) Then
            txt_date = Format(txt_date, "Short Date")
            
            Dim UzerDt As Date
            Dim CurrDt As Date
            Dim DtDif As Single
            UzerDt = CDate(txt_date.Value)
            CurrDt = Date
            If CurrDt - UzerDt > 60 Then
                MsgBox "Invalid date, Over-Limit!"
                Cancel = True
                txt_date.Value = ""
                frm_dataInput.txt_date.SetFocus
            End If
            If CurrDt < UzerDt Then
                MsgBox "Invalid date, Grater than current!"
                Cancel = True
                txt_date.Value = ""
                frm_dataInput.txt_date.SetFocus
            End If
        Else
            MsgBox "Not a valid date, try again!"
            Cancel = True
            txt_date.Value = ""
            frm_dataInput.txt_date.SetFocus
        End If
    End Sub

Is it OK now?