How to lock / protect the cells in different columns based on a date

Hi
I am new to the forum.
Trying to incorporate the following in a worksheet:
Lock the cells automatically in the columns that are older than 2 days or more
Lock the cells corresponding to today and yesterday using a command button after entering X
Copy the sheet multiple times with the same features
Please help
Nak

Hi there,

It shouldn't be too difficult to lock (protect) all cells which relate to dates which are more than two days old.

When you say:

Lock the cells corresponding to today and yesterday using a command button after entering X

do you mean that ALL cells which relate to today and yesterday should be locked, or that only the cells which contain an X should be locked?

Please let me know and I'll see what I can do for you.

Regards,

Greg M

Customer Visit Report.xlsx
Thanks Greg !
I would like to optionally keep the latest 2 days open for data entry , but the whole column could be blocked electively after entering data if the user decides to do so.
I will now upload another sheet which will be more specific and self explanatory.

Please see the requirements below:

I have to do a lot of simple field force activities analyses
I don’t have a web based query system to pull data.
So I use simple excel sheets link them and do the necessary analysis in the best possible manner I can J
Here is an excel sheet
All cells except I6:AM365 are protected. When a customer is visited, the user is required to put “X”or “Yes “in the corresponding date cell ( in the column ) against the name of the customer.
Now I want the cells I6:125 to get locked, IF I5 >$G$4 and similarly for columns J: AM
Because I want the user not to alter yesterday’s data after today. This allows a the user only 24 hrs time to report yesterday’s visits at the most.
I have conditionally formatted the cells also. They turn grey if older than 1 day and are to be automatically locked and yesterday’s cells turn light pink because they are still unlocked. Today’s active column will be highlighted in dark pink. Columns representing tomorrow and beyond are open and non-colored.
Can you help me with this ?
I have used the date as =TODAY () in G2
Day value in G3 - Day () returns the serial number
Day value – 1 in G4
Month is current month in G1
For certain reasons I have given the dates in cells I5: AM5 serial numbers ( I don’t want real date formats there)
Now I want the cells I6:AM325 to get locked as the dates are passed but with 1 day grace period
Is there an optional way that though the cells up to day before yesterday get locked automatically, if the user enters the data in yesterday’s / todays columns ( if he is very punctual J- normally not ) and save the data by clicking a command button or so that the data cannot be altered by any one.
N Vaidyar
Also do you know if I can pull data from these sheets used by users remotely and consolidate them ? Via web or so ?

Hi again Noushad,

A suggestion for you - would it be advisable to protect ALL of the "Visit Cells" EXCEPT those which refer to today and yesterday - i.e. to prevent visits being recorded in advance? I can implement this or I can implement what you asked for originally - neither of these options is very complicated, so just let me know which you prefer.

Now a question regarding the way the workbook will be used in operation - when do you want the locking/unlocking operation to take place? Doing it each time the workbook is opened is quite easy, but it won't be applied if the user leaves the workbook open continuously. I can try to have the operation performed automatically at 23:59 each day, but my experience to date with the OnTime function has been less than 100% successful - maybe that's my fault or maybe it's due to using earlier versions of Excel.

Regarding the User's button to lock the "Visit Cells" for today and yesterday - do you want the button to lock ALL of the cells for today and yesterday, or just those cells which have already had an X entered in them? Either option is equally easy to provide.

The worksheet in your workbook is protected, but not password-protected. I presume you will want the final version to be password-protected. If so, will an administrator (you?) need a button to conveniently Unprotect/Protect the worksheet without having to enter the password each time? If so, let me know the UserName of the administrator(s) - e.g. Noushad - and I can include a button which will be visible only if the workbook has been opened by an administrator.

If you like, I can also provide formatting which will hide the columns for days 29, 30 and 31 depending on the month involved.

Please let me know what you need for each of the above.

Regards,

Greg M

Thanks Greg ! Great Help Indeed!

Hi again Greg
Now that you have been able to sort out all my issues so systematically !!!!! , one final request more:
Can I add another sheet as a Daily Call Report ?
Please see the attached sample
By entering the day value, I should be able to generate the Daily Report for that particular date.
Then generate a PDF of the report and mail it to the address specified in the cells and cc
I think a INDEX IFNA function and VBA code will be rquired for this
Sorry to bother you again

Hi again Noushad,

Take a look at the attached workbook and see what you think of it.

A few points:

The cell protection routine will be run automatically each time the workbook is opened, and also at midnight if the workbook happens to be open at that time.

Only the "Visit Cells" associated with days up to and including Today are visible - cells associated with future dates are hidden.

Only the "Visit Cells" associated with Yesterday and Today are unlocked - all other cells on the worksheet are locked.

The "Lock" button (which the User uses to lock the "Visit Cells" for Yesterday and/or Today) moves along the row so that it is always positioned above the "Day Cells" for Yesterday and Today.

When the User presses the "Lock" button, the "Visit Cells" are checked for entries - if entries are detected in a column, the User is prompted to confirm that the cells should be locked. No prompt is generated if no entries are detected. This is done for each of the two columns, Yesterday and Today.

Near the top of the VBA CodeModule there is a statement which reads:

Please Login or Register  to view this content.

You should change the value of this Constant to whatever you use for your Windows UserId - when this is done, save and reopen the workbook and you should see the "Unprotect/Protect Worksheet" button in Cells I2:M3. This button will not be visible if the workbook is opened by anyone with a UserId different from your own.

Is the formula used in Column H (Visits) correct? You have used the "SUM" function, but that will not work correctly if Visits are recorded by using an "X". Should numbers (1, 2) be used in this column instead?

Now, before I look at your Daily Call Report, we need to consider what should happen at the end/beginning of the month. Do you intend to create a new worksheet for the new month? Yesterday's "Visit Cells" will no longer be visible if Today is the first day of the new month. Do you need to save a copy of the worksheet for the old month in a new "Archives" workbook and clear the "Visit Cells" so that they are available for the new month? Something else?

Have a think about what you require and let me know.

Regards,

Greg M

Hello Greg !
Thanks a lot !
I will revert to you with all my comments
Great help indeed !!!
Noushad

Archiving exactly as you mentioned
PDF with time stamp to go to the e mail addresses mentioned( changeable)

Hi Noushad,

Here's the latest version of the workbook - we're definitely making some progress

My comments on the attached might be a bit too detailed to post here, so I've sent you a Private Message with them. I'll happily post them here if anyone else is interested!

Regards,

Greg M

Hi Noushad,

Thanks for the comments in your Private Message.

The attached workbook seems to be working ok. The creation of the .PDF file and the associated email have been added. We just need to decide whetherr the .PDF file should be deleted or retained and archived.

I've had some thoughts about the archiving of the monthly worksheet and I'll let you know as soon as I have something worked out.

Regards,

Greg M

Excellent solution !
Big thank you Greg !
I would like to add some valuable points to your reputation, please advise
Noushad

Hi Noushad,

You can add to my "reputation" by looking at my posts in the Forum. In the bottom left-hand corner there is an "Add Reputation" button. Just click on that and you can add points and comments as you wish.

I've added some more details to the version of the workbook which contains your confidential data, and I'll email it to you.

Just in case any other Forum users are interested, I hope to post the final version with "sample" data here when everything has been completed.

Regards,

Greg M

Hi Greg
I' m new in this forum. My Vba Knowlwdge is beginer level but i can recognize that the solution of "026 - Customer Visit Report - 7.xlsm"‎
was an Excellent job. I was wondering if you have moved on to any solution of the monthly archiving (that is the one that interests me). Please i'd like to send me the way the file works just like you done Noushad.
Thanks alot