Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Auto lock after date

Maybe this is so easy that no one writes about it, but I am trying to make a time sheet that students fill out themselves for a school program. I have the date next to a blank cell that they will update with the time they worked for that day, but I want it to to lock after the day is over. Than the next day they write a new amount of hours in another blank cell next to the date and it closes at the end of that day. At the end of the week I check the hours and sign off on them, so I don't want anyone updating the hours afterward. Any idea? I need to keep the entire semester on one page. I have little sections of weekdays in columns. First week is E8-E12, next week is G9-G12. There are twenty weeks. How would I do it for this? Or an entire worksheet? If you need anymore information please ask. Thanks a lot for the help!


Post your answer or comment

comments powered by Disqus
Hello Everyone.

This is my first post here. I have been using Excel for a few months now, and am learning more and more everyday. I am becoming pretty familiar with most simple functions however i am completely green when in comes to Macros.

The Goal:

Columns A - G, J & L need to mandatory, meaning they must have data in them.

After data has been entered into column L i need there to be an automatically generated date and time stamp entered into column M. Column M needs to be hidden so it is not visible to the user.

After the time stamp is entered the entire row needs to become locked so the user can not edit it.

Then the row needs to auto sort itself based on the numerical account number in column A.

Here is the code i currently have. Auto sort works, but the auto lock is not working.


	VB:
	
 
Private Sub Worksheet_Change(ByVal Target As Range) 
     
    If Target.Count > 1 Then Exit Sub 
    If Target.Column  12 Then Exit Sub 
     
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table2").Sort 
        .Header = xlYes 
        .MatchCase = False 
        .Orientation = xlTopToBottom 
        .SortMethod = xlPinYin 
        .Apply 
    End With 
     
     
    If Target.Cells.Count > 1 Then Exit Sub 
    If Not Intersect(Target, Range("A4:L253")) Is Nothing Then 'set your range here
        ActiveSheet.Unprotect Password:="PASSWORD" 
        Target.Locked = True 
        ActiveSheet.Protect Password:="PASSWORD" 
    End If 
     
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any feed back would be great.

Cross Posted Here.
http://www.excelforum.com/excel-prog...ime-stamp.html

Hi,
with the help search engine and a miniscule copy/paste job I was able to assemble the following code which is locking cells in a range after data has been entered:


	VB:
	
 Range) 
     
    If Not Intersect(Target, Range("B3:AI28,D29:D33,AM3:AM4")) Is Nothing Then 
         
        If Target.Locked = False Then 
            ActiveSheet.Unprotect "PWD" 
            Target.Locked = True 
            ActiveSheet.Protect "PWD" 
            ThisWorkbook.Save 
        End If 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now, rows B3:B28 contain a date of employees arrival at work.

What I would like to accomplish next is that even when some cells in range are blank, user without password won't be able to fill them after Date+1 time had passed (they will become locked).

Moreover, in my particular circumstances, another way to accomplish it might be by using the file name, as for every day in a month I create a table with corresponding number. Right now it structured by way of folders and subfolders and the worksheet name contains only day number of the month (e.g. ..2012417.xlsm), but it can be very well be changed (for example into ..2012-04-17.xlsm).

Solution by means of file name might even be preferable, as it would prevent to change retroactively data on days when no employee attended (meaning no date in B3:B28 rows).

Is it as easy as a piece of cake for seasoned Ozgrids? :-)

If it is the case, you have my many thanks in advance!

Firstly, I know 0 about programming or VBA, but I found this code which auto locks cells after you enter data in them.


	VB:
	
 Excel.Range) 
     ' Place this code in the worksheet code module. ' The subroutine unprotects the sheet and IF an entry is made ' in an
empty ("") cell, the cell is locked and then the ' sheet's protection is turned back on. Any further ' attempts to edit the
cell generate the password msgbox. ' You can set the range to one cell ("A1") or an area ("A1:Z300"). ' 1. Use Format - Cells
- Protection to unlock the cells ' in the range where one time entries are to be allowed. ' 2. Protect the worksheet with the
same password as ' you use twice in the following subroutine (thepassword).
    On Error Goto justenditall 
    Application.EnableEvents = False If Not Intersect(Target, Range("A1:A20")) Is Nothing Then If Target.Value  "" Then
ActiveSheet.Unprotect Password:="thepassword" Target.Locked = True End If End If 
    ActiveSheet.Protect Password:="thepassword" 
    justenditall: Application.EnableEvents = True End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Now I want to make this only work when the file is saved. For example, when an employee opens up the file he or she is able to enter any new data, but not edit old data, and once saved, they will not be able to edit the data they just entered.
I hope this makes sense.
Thanks!

Is it possible in Excel to set a worksheet up to auto-lock (protect) on a certain date? I have a shared workbook that has 12 sheets on it, one for each month. Is it possible to set it up so that you can only write on the worksheey for the current month, with the previous month locking on the last day of that month. There are actually many workbooks we are working with, so it is time consuming to go through at the beginning of every month to protect each sheet. Thanks!

Firstly, I know 0 about programming or VBA, but I found this code which auto locks cells after you enter data in them.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

' Place this code in the worksheet code module. ' The subroutine unprotects the sheet and IF an entry is made ' in an empty ("") cell, the cell is locked and then the ' sheet's protection is turned back on. Any further ' attempts to edit the cell generate the password msgbox. ' You can set the range to one cell ("A1") or an area ("A1:Z300"). ' 1. Use Format - Cells - Protection to unlock the cells ' in the range where one time entries are to be allowed. ' 2. Protect the worksheet with the same password as ' you use twice in the following subroutine (thepassword).

On Error GoTo justenditall

Application.EnableEvents = False If Not Intersect(Target, Range("A1:A20")) Is Nothing Then If Target.Value <> "" Then ActiveSheet.Unprotect Password:="thepassword" Target.Locked = True End If End If

ActiveSheet.Protect Password:="thepassword"

justenditall: Application.EnableEvents = True End Sub

Now I want to make this only work when the file is saved, so that the users can amend mistakes. Once they are sure its al ready, they press save and it becomes locked. I want to do this so they cannot edit information from the last save.
I hope this makes sense.

Thanks!

Hi.

Please help.

I have two worksheets.

In worksheet 2: the sheet (and the workbook) is protected with a password and some cells are locked but some cells are unlocked for data input (e.g: B27 to C38).

The user enters some word (e.g. “max”) in cell F10 of sheet 1 and then is supposed to enter names at least in cells B27 and C27 of sheet 2.

After this the user “saves” the file.

This is what I would like the VBA to do before save event:

1.If the value in F10 of sheet 1 is “max” AND if the value of cells B27 and C27 is not equal to null then “lock” the cell B27 to C38, and save file
2.If F10 of sheet 1 is “max” AND if the value of B27 and C27 equals “null” then
display a message to alert the user to enter values in B27 and C27 and not allow the user to save file.

Following is my attempt: could you please suggest a complete code.

	VB:
	
) 
    If Sheet1.Range("$F$10").Value > "max" Then 
        If Sheet2.Range("$B$27", "$C$27").Value  “” Then 
            "auto lock B27 to C38 – I need some code here" 
            cancel = False 
        Else 
            MsgBox UserName() & ":" & Chr(13) & "Please enter values in sheet 1 before saving the file." 
             
            cancel = True 
        End If 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
EDIT: Added Code Tags - Jack

Already confused>> Probably just me!! I need 2 alter the appearances of the
odd cell, whilst maintaining the auto format day/date settings, but not font
colour & borders etc....
I do a monthly "thingo" (schedule), in which, at present, I use auto format
2 complete days & dates - I have rows for Sat & Sun which r not 2 b seen and
2 date, have been using white txt colour so, when printed, u just see a gap -
desired effect!
Have tried custom format (worked well) but as soon as I used auto fill again
it overrode custom (set 4 white txt). Also have tried grouping & hiding
(works well, but doesn't leave desired gap between weeks - tried bold outline
on bottom border, but NQR still! ~~ Long story short:
Does anyone know how to force cells to "go with the flow" of auto
formatting, but not 2 show contents 2 achieve what I am after??--
cheers & thx ___ J-Pop :-)

Col. D is Data.Can I lock cell once Data is entered.If I need to change Data I would Unprotect Sheet. Eg.D4 data entered ,then cell auto locks

We have several Excel spreadsheets that were created and protected in
Excel 97 and 2000. Many of the wks are forms used by personnel for
submitting expense reports, planning travel, etc. Recently we migrated
all of our users to Office XP (Excel 2002).

Now when a user opens one of the spreadsheets created and protected in
Excel 97/2000 in Excel 2002, enters data and then saves and closes it,
when they re-open it all of the previously selected cells are
un-selectable by the user.

If the user just opens one of the wks and does not make any changes and
then saves it, when they reopen it the cells are not user select locked.
The cells are only user select locking after data is entered and the
file is saved.

Once the cells are user select locked, if the user takes the file back
to a system still running office 2000 and opens it, the user can select
and edit the cells contents.

Also if we open the cell select locked file in 2002 and save it as a
Excel 95 file and then reopen it in 2002, the users is then once again
able to enter data.

We fixed some of the wks by un-protecting them and then resetting the
protections. However, this is not an option for all of the files as we
do not control them.

We understand that Excel 2002 provides the developer with much more
granularity in the protection options than previous versions. However,
one would think that as cell user select locking was not an option in
the earlier versions, Excel 2002 would not be enable this by default
when working with a protected XLS file created in a previous version.

Does any by have an explanation and hopefully a solution for this does
not require us to rework all of the files?

Thanks

--
TWilson
------------------------------------------------------------------------
TWilson's Profile: http://www.excelforum.com/member.php...o&userid=25939
View this thread: http://www.excelforum.com/showthread...hreadid=393164

How can I get excel to apply values in a cell using an IF function before or
after dates displayed in another cell? It will allow me to use a
datevalue(...)= but not datevalue(...)> or datevalue(...)

I have an Excel sheet that I obtained from a 3rd party. I can unlock it, but once I go to another sheet, the sheet I unlocked locks itself again automatically. How do I disable this auto-locking feature of a sheet once I navigate to another sheet?

I have a multipage spreadsheet that has certain cells unlocked so users can
enter data. The data that will be entered could be copied and pasted into
the cells, however every time I use the Office Clipboard to copy and paste,
the cell locks after pasting. I can paste using the shortcuts and the cell
does not lock. I want to keep the sheet password protected so the cell needs
to stay unlocked.

Any ideas on what may be causing the problem?

Dear Admin/Mod/Experts,

Auto Hilighting Day & Date For Sunday

i tried but no use............pls guide me.

Regards
SUn

How can I get excel to apply values in a cell using an IF function before or
after dates displayed in another cell? It will allow me to use a
datevalue(...)= but not datevalue(...)> or datevalue(...)<.

Hello,

I would like to have my dropdown boxes automatically lock after the initial selection. I tried going the "protect sheet" route of:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column > 2 Then Exit Sub 'only works in Columns A & B
With ActiveSheet
.Unprotect
If Target.Value > "" Then Target.Locked = True
.Protect
End With
End Sub which is a solution I got from an online source, however it does not work in the way I want it to. The cell becomes locked from typing in any new values, but you can still select a new item from the dropdown list.

I have attached a sample workbook. I appreciate your help with this, thanks.

Cam

Dear All,

Someone have help me write a macro to prompt me on over due payment.
But instead of clicking on the "CHECK DUE DATE" button, can the
macro auto check due date whenever I open the workbook?

Here I attached the workbook for easy understanding.

Hope and need your help urgently.

Thank you very much =)

I have a file that links in several different datasources. What I am doing is updating all my sources, running all my calculations then producing "results" sheets. I have several seperate reports being generated from the same data. I am copying the worksheet into a new workbook then saving, emailing, then going onto the next. Problem is when the users open that file their calculation is set to manual because I am manually calculating everything to speed up processing time. If i enable auto calculation after copying the worksheet it calculates my entire workbook, then saves, and goes onto the next. Each time it auto calculates it takes 15 minutes. With manual calc its 2 minutes. How can i make it so that my main file always has auto calc off but make it so that when users open the seperate file auto calc is enabled?

i need the date to change automatically on a daily basis - how do i do this -
i know that you can auto populate the date in WORD so i figured you could do
it in EXCEL

We have several Excel spreadsheets that were created and protected in Excel 97 and 2000. Many of the wks are forms used by personnel for submitting expense reports, planning travel, etc. Recently we migrated all of our users to Office XP (Excel 2002).

Now when a user opens one of the spreadsheets created and protected in Excel 97/2000 in Excel 2002, enters data and then saves and closes it, when they re-open it all of the previously selected cells are un-selectable by the user.

If the user just opens one of the wks and does not make any changes and then saves it, when they reopen it the cells are not user select locked. The cells are only user select locking after data is entered and the file is saved.

Once the cells are user select locked, if the user takes the file back to a system still running office 2000 and opens it, the user can select and edit the cells contents.

Also if we open the cell select locked file in 2002 and save it as a Excel 95 file and then reopen it in 2002, the users is then once again able to enter data.

We fixed some of the wks by un-protecting them and then resetting the protections. However, this is not an option for all of the files as we do not control them.

We understand that Excel 2002 provides the developer with much more granularity in the protection options than previous versions. However, one would think that as cell user select locking was not an option in the earlier versions, Excel 2002 would not be enable this by default when working with a protected XLS file created in a previous version.

Does any by have an explanation and hopefully a solution for this does not require us to rework all of the files?

Thanks

Hello!

I have been reading through this forum for quite some time now and decided it was easiest to get my answers by describing my own specific spreadsheet. I am a very basic user and want to accomplish some rather complex things.

My company designs and manufactures components for the biopharmaceutical industry. Essentially, it is just a fancy job shop. I am in the process of creating a master production matrix that will keep track of all aspects of each job, including static details such as job #, part #, customer info, etc. I also want it to generate a couple fields automatically based on the data that is input into other related columns. Here are the spots that are giving me the biggest problems:

- The column "Award Date" is directly related to the column "Due Date", in that we always set the due date 30 days out from the date on which we receive a PO. I want the "Due Date" auto-fill when I enter an "Award Date". There is no need to make this holiday/weekend sensitive.

- The column "Ship Date" is directly related to the columns "Terms", "Follow-up" and "Payment Due Date". I will be filling in "Ship Date" manually on the day that we send a job out. Based on the payment schedule specified in the "Terms" field, I want the "Payment Due Date" to auto-fill itself in. For example, if we ship a job on 01/01/11 and the "Terms" for said customer are Net 30, I want to see the "Payment Due Date" field auto-fill with 01/31/11.
"Follow-up" is related to these columns in that I want it to prompt our accounts receivable department to follow up with the customer 10 days before the Payment Due Date. This is to ensure that all documents are in order and that there is nothing else needed before they can cut us a check...lots of the larger customers will find any reason to avoid paying on time and I specifically DO NOT want to give them anymore excuses, hence the follow-up. To follow the same example as before, the "Follow-up" field should have auto-filled the date 01/21/11. I imagine that with a more complex formula such as this, one would need to ensure that fields were auto-filled in the correct order, so that the necessary information would be in place for the next piece of the formula to function properly. Thus, "Follow-up" must be related directly to the "Payment Due Date", which is directly related to "Ship Date" plus "Terms". I can picture the workflow in my head but alas I cannot put it down on a spreadsheet.

- There are a series of columns specifying the dates on which specific job processes should be completed. These will all be set manually by the production manager to give the machinists an expectation of when they should finish each step. They will replace the date with an X once each step has been completed, demonstrating that it has been done. This is to help us keep tabs on exactly where each job stands.
I think it would be awfully great if the due date fields that are overdue or still open once the date is reached would turn red. I think it would be even better if, say, 3 days before the due date the field would turn yellow. This would be an excellent visual cue for us to glance at quickly and see that something was close to the deadline or not completed when it should have been. So in other words, the field is manually filled with a due date for that process and if that date is reached WHEN THE FIELD STILL CONTAINS A DATE AND NOT AN X then it will turn red. Similarly, it would turn yellow 3 days before it turned red, but only if the field still contained a date and not an X. Can we do this? I don't need the fields to change back to their original color because it would be good information for me to know how often each process was late or close to it.

Believe it or not, these are the ONLY things I need help with, lol. I was able to work out hyperlinks to each job's related schematics and company information, which was awesome all by itself. Getting this other stuff in place would make production soooo much easier!! I have attached my sheet as it is with a couple rows filled in manually to help describe what you would see. I filled in the second row to illustrate the desired outcome of the formulas that I am hoping someone can help me create.

Thanks for all the help and please do not hesitate to email me directly. cjmorin@overlookindustries.com

Chris

I have a work sheet using rows A - L.

I am currently using a macro that auto sorts the info in Column A based on a numerical account number, after information has been entered into column L. I now need to to lock all the cells in columns A - L after the auto sort has happened and place a date and time stamp in Column M.

I am extremely green with Macros. Thank you.

Greetings folks,
Just wondering if there is way by which i can get a sheet to automatically lock depending on the current date. Most of my sheets have the current date in them. I need them to lock off on a certain date as I am often not there on the day to do it myself. I have had several occasions where people have gone in and changed figures after i have sent reports off. Not good.
Cheers,
TRC

I am new to this forum so hello to all.

I am trying to lock cells after today's date has passed so that no one can make changes to it after today's date has lapsed. This is for protective reasons so that people do not remove their names from reserving something after using it. Now the code should disallow locking after cell input entry when today's date hasn't passed so that changes can still be made by the user. I am trying to determine the code to do this but I have no idea as to how to do it.

Here's a scenario: I reserve something for Aprill 11, 2009. I input my name. Since it's April 9th, 2009, I am still able to make changes up and until April 11, 2009. After this date, the cell is locked and no changes can be made, except for the administrator.

Also, a code to allow the administrator to make changes within the cells if need be. That would be appreciated.

Please advise,

Thank you

Good afternoon,

I am trying to create a workbook that locks after a particular date. I have a sheet that I am providing for a particular purpose, however dont want it then being sent on to others for use at a later time without my consent, so want it to lock people out and display a message to come back to me for a new version.

I thought I had the answer on the below post from another user:

http://www.excelforum.com/excel-prog...tain-date.html

however have not been able to get this to work. Please see attached a very basic workbook with this VBA code included.

Is anyone able to please assist me in what I have missed?

Thanks


No luck finding an answer? You could always try Google.