Push one date forward

amna77

Active Member
Joined
May 9, 2002
Messages
251
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Active Member
Joined
May 9, 2002
Messages
251
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Active Member
Joined
May 9, 2002
Messages
251
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,070
Messages
5,599,607
Members
414,323
Latest member
Sacalumbo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top