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.
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
Step 2
Step 3
Step 4
In Action
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"