Using Excel 365, I created a userform, but now need to enhance the functionality

[auto.pilot]

My userform has a textbox input for the date and 10 different criteria. Everything is working fine, except I'd like to have some error handling.

The input for TextDateInput should be limited to only a date, using format mm/dd or mm/dd/yyyy. I'd also like to have a checking feature so that users can't input a date that has already been used. My dates are on a sheet called 'ClinicalTracker' in column A.

How can I do this?

Here's what I have so far....

Private Sub ButtonSave_Click()

Dim L_Row As Long

L_Row = Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Range("A" & L_Row).Value = TextDateInput.Value

Range("B" & L_Row).Value = TxtScreening.Value
Range("C" & L_Row).Value = TxtAssessDiag.Value
Range("D" & L_Row).Value = TextTreatment.Value
Range("E" & L_Row).Value = TextReportsNotesBilling.Value
Range("F" & L_Row).Value = TextFamClientConsultation.Value
Range("G" & L_Row).Value = TextFamClientCounseling.Value
Range("H" & L_Row).Value = TextIEPOther.Value
Range("I" & L_Row).Value = TextInServ.Value
Range("J" & L_Row).Value = TextTraining.Value
Range("K" & L_Row).Value = TextPresentations.Value

End Sub

 

[auto.pilot]

I've spent quite a bit of time searching for the answer. I found a few examples that check for duplicate dates, but I am unable to get them to work, simply because I don't understand the code well enough to make the necessary changes. Any thoughts?

 

[dmt32]

I've spent quite a bit of time searching for the answer. I found a few examples that check for duplicate dates, but I am unable to get them to work, simply because I don't understand the code well enough to make the necessary changes. Any thoughts?

Hi
see if this update to your code will do what you want

Private Sub ButtonSave_Click()
    Dim NewRow             As Long
    Dim DateEntry          As Variant
    Dim wsClinicalTracker  As Worksheet
   
    Const DateFormat As String = "mm/dd/yyyy"
   
    Set wsClinicalTracker = ThisWorkbook.Worksheets("ClinicalTracker")
   
    DateEntry = Me.TextDateInput.Value
    If Not IsDate(DateEntry) Then Exit Sub
   
    DateEntry = DateValue(DateEntry)
   
    With wsClinicalTracker
        If IsError(Application.Match(CLng(DateEntry), .Columns(1), 0)) Then
       
            NewRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
           
            With .Cells(NewRow, 1)
               
                .Value = DateEntry
                .NumberFormat = DateFormat
               
                .Offset(, 1).Value = Me.TxtScreening.Value
                .Offset(, 2).Value = Me.TxtAssessDiag.Value
                .Offset(, 3).Value = Me.TextTreatment.Value
                .Offset(, 4).Value = Me.TextReportsNotesBilling.Value
                .Offset(, 5).Value = Me.TextFamClientConsultation.Value
                .Offset(, 6).Value = Me.TextFamClientCounseling.Value
                .Offset(, 7).Value = Me.TextIEPOther.Value
                .Offset(, 8).Value = Me.TextInServ.Value
                .Offset(, 9).Value = Me.TextTraining.Value
                .Offset(, 10).Value = Me.TextPresentations.Value
               
            End With
           
            MsgBox DateEntry & Chr(10) & "New Record Entered", 64, "New Record"
           
        Else
           
            MsgBox DateEntry & Chr(10) & "Date Exists", 48, "Date Exists"
           
        End If
    End With
   
    Me.TextDateInput.SetFocus
End Sub

Don't worry too much about the format user enters the date in your textbox as any valid date entered - code should apply the required format to the cell.

Dave