Restricting Data Entry to an End of Month Date Only

[Kogersdad]

Is there any way to restrict a cell's value to an End of Month Date only? For example, in Column D of a spreadsheet, I would like other users to enter a date but they can only enter an end of month date (1/31/2013, 2/28/2013, etc).
I realize I could perform this by creating a drop-down list but I would like to avoid this option if at all possible.

I would like to take it even further by allowing them to enter any date and then have the spreadsheet convert the entered date to the EOMonth date in the same cell. Example, if they entered 1/28/2013 in D1, it would appear as 1/31/2013 in D1 (same for each other day in each month).
In other words, can Excel "round up" to the end of month automatically in the same cell as the entered date?

thanks
Tim

 

[Joe4]

I would like to take it even further by allowing them to enter any date and then have the spreadsheet convert the entered date to the EOMonth date in the same cell. Example, if they entered 1/28/2013 in D1, it would appear as 1/31/2013 in D1 (same for each other day in each month).
In other words, can Excel "round up" to the end of month automatically in the same cell as the entered date?

You can do this with VBA, specifically with Event Procedure Code that is automatically triggered upon some event happening.
Below is some sample code that will do that for all entries in Column D.

Just right-click on the Sheet tab name at the bottom of your screen, select "View Code", and paste this code in the resulting code window.

Private Sub Worksheet_Change(ByVal Target As Range)

'Update all new entries in column C so that they return the end of the month entered
    If Target.Column = 4 Then
        Application.EnableEvents = False
        If IsDate(Target) Then
            Target.Value = Application.WorksheetFunction.EoMonth(Target.Value, 0)
        Else
            Target.Clear
            MsgBox "Only date entries allowed!", vbOKOnly, "INVALID ENTRY!"
        End If
        Application.EnableEvents = True
    End If
    
End Sub

 

[Snakehips]

Kogersdad,

Perhaps this in your sheet's code module.

Assumes dates in range D2:D10000 to allow 2 rows of headers? Edit to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("D2:D10000")) Is Nothing Then Exit Sub
If Not IsDate(Target) Then
MsgBox "Please enter a date"
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
Exit Sub
End If
If Not Day(Target + 1) = 1 Then
Do Until Day(Target + 1) = 1
Target = Target + 1
Loop
End If
End Sub

Hope that helps.


EDIT** Posts overlapped. Both work Joe's is cleaner. I was not aware of the EOMONTH() function!!! Every day's a school day!!!!1

 

[Kogersdad]

not only did this solve my problem, you guys even anticipated my second question (regarding the headings) and answered it!!
Thanks so much!!
Tim

 

[Kogersdad]

A couple more questions,
1) is there any way to "co-mingle" the two VBA's above so that it would use the "header" function as well as the EOMonth function?
2) how would I use the same code to affect another column in the same worksheet - for example Range D2:D10000 and Range N2:N10000?
Tim

 

[Joe4]

Private Sub Worksheet_Change(ByVal Target As Range)

'Update all new entries in column C so that they return the end of the month entered
    If (Target.Column = 4 or Target.Column = 14) And (Target.Row > 1) Then
        Application.EnableEvents = False
        If IsDate(Target) Then
            Target.Value = Application.WorksheetFunction.EoMonth(Target.Value, 0)
        Else
            Target.Clear
            MsgBox "Only date entries allowed!", vbOKOnly, "INVALID ENTRY!"
        End If
        Application.EnableEvents = True
    End If
    
End Sub

 

[Kogersdad]

Exactly what i needed! thank you so much!