Push one date forward

[amna77]

In column A I put the data validation method, like my user are only allowed to enter 3 same dates, if they try to enter 4th one, then they get error message.

Here is what I used.
=COUNTIF(A:A,A1)<4
and error message.

Ok What I am trying to do is now, that once they get error message then I want to look for that date, for example if they try to enter 3-Mar, 4th time, then I get an message that I can not enter that. Now I want to take the 3-Mar, and convert that to 4-Mar, simply I want to Push that date one day forward. is it possible to do it like that?

Thanks in advance for help.

Thanks

 

[lenze]

Here's 1 way using a little coding
Say your dates are in Column A. If column B is used, insert a new column B. Enter this in B1 =IF(ISBLANK(A1),"",COUNTIF($A:$A,A1)). Copy down Column B as far as needed. Now place this code in the Sheet Module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
If Target.Offset(0, 1) > 3 Then
MsgBox ("The date you entered has been used 3 times." & Chr(13)&"It will be adjusted one day")
Target.Value = Target.Value + 1
End If
Application.EnableEvents = True

End Sub

You must have some real instructionally challenged users to need something like this.
Also, what do you do about weekends?

By the way , you can hide column B

 

[amna77]

In my case, my users enter data in column A, once they try to enter date then I want to show message box, plus push one date forward. Don't want let them enter 4th same date in cell.

Any help please.
Thanks

 

[lenze]

The macro I gave you will automatically add one day to the date they entered. All they have to do is push OK. You could us a Yes No option to give them a choice, but you indicated you didn't want to do that.

 

[amna77]

I try to do that, but it does not work. I don't know what I am doing wrong here.
In my column A I am entring dates, but it allows me to enter more then 4 same dates.
It should give me message box, when I try to enter 4th date.
Any idea why is that?

 

[lenze]

Where did you place the code? It needs to go in the WorkSheet module. Right Click on the sheet tab and choose View code. Paste the macro

Then Choose Insert Module
Paste this code in the new module in case your events are disabled.

Sub FixEvents()
Application.EnableEvents=True
End Sub

Close the VBE and run the macro FixEvents from the Tool menu > Marcos

Now the code should work.

HTH