excell 2007 macro validate data entered into cell and show msgbox if incorrect

Question

Please can someone help with the following code. it gives me an error at the following line:

Set range = "C5:L14"

This is the complete code:

Private Sub Worksheet_Change(ByVal Target As Excel.range)
Dim ws As Worksheet
Dim range As Worksheet

Set ws = Application.ActiveSheet
Set range = "C5:L14"

If Not Application.Intersect(Target, range("C5:L14")) Is Nothing Then

    If range("C5:L14").Value = "" Then Exit Sub

    If range("C5:L14").Date = "< today()" Then Exit Sub

    If range("C5:L14").Date = "> today()" Then MsgBox ("Future dates not allowed!")

    Else
        MsgBox ("Please enter date as follows yyyy-mm")
    End If

End Sub

The date is formatted to "2013 Jan" on the cells. Future dates are not allowed and the user should only type in the date as "2013-01". The format should change it correctly. If they type in "2013 Jan" the Conditional formatting does not pick it up. Have tried DATA VALIDATION but it only limits me to one.

I need the macro to make sure a user doesn't enter an incorrect date in the cells specified.

`Set Rng = ws.Range("C5:L14")` Also do not use the `Range` word as a variable Also change `Dim range As Worksheet` as `Dim rng As Range` — Siddharth Rout, Jan 31 '13 at 14:41
BTW you don't need VBA for this. You can manually set the DataValidation ;) — Siddharth Rout, Jan 31 '13 at 14:43

Siddharth Rout · Accepted Answer · 2013-01-31T16:29:54.320

What you are trying can be solved without VBA as well. However I am showing you both the methods. Take your pick

NON VBA

Select the cell where you want to apply Data Validation and then follow these steps.

Step 1

enter image description here

Step 2

enter image description here

Step 3

enter image description here

Step 4

enter image description here

In Action

enter image description here

VBA

I have commented the code so you will not have any problem in understanding it

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range
    Dim aCell As Range

    '~~> The below two lines are required. Read up more on
    '~~> http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640
    On Error GoTo Whoa
    Application.EnableEvents = False

    '~~> Set your range
    Set rng = Range("C5:L14")

    If Not Application.Intersect(Target, rng) Is Nothing Then
        '~~> Loop through all cells in the range
        For Each aCell In rng
            If aCell.Value <> "" Then
                If aCell.Value > Date Then
                    aCell.ClearContents
                    MsgBox "Future date not allowed in cell " & aCell.Address
                ElseIf IsDate(aCell.Value) = False Then
                    aCell.ClearContents
                    MsgBox "Incorrect date in cell " & aCell.Address
                Else
                    aCell.Value = Format(aCell.Value, "yyyy-mm")
                End If
            End If
        Next
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Hope this helps?

EDIT:

A slight change. In the Step 4 of the Non VBA Method, I typed "yyyy mm" by mistake. Change that to "yyyy-mm"

thanks for all the responses, tried all of them and they all work better than expected. i am learning more and more each day from this site and all your answers. thank you for the quick responses, will go and learn more now. — user1955214, Feb 01 '13 at 07:20
just 1 problem on the VBA method that i dont understand. if i clear any cell in the range, it clears the whole spreadsheet with the msgbox "future dates not allowed" one cell at a time. there will never be a reason to delete anything on the spreadsheet but it is bothering me and would like to understand why for future VBA codes. thanks. — user1955214, Feb 01 '13 at 07:41
It will only clear the cells which have future dates or invalid dates. This is required so that if the user does a copy paste in multiple cells then all those cells will be handled properly — Siddharth Rout, Feb 01 '13 at 07:44
found it, it was the format of the date. mine was set to YYYY MMM and the VBA code checked for YYYY-MM. now it works, sorry for the bother. thank you Siddharth — user1955214, Feb 01 '13 at 08:07

excell 2007 macro validate data entered into cell and show msgbox if incorrect

1 Answers1

Linked