autolock a cell after entering value into it

  • Thread starter Thread starter Deepak Jeyakaran
  • Start date Start date
D

Deepak Jeyakaran

I am preparing an attendance tracker for a huge team. I
want to create a excel sheet where-in when data is entered
for a particular day and close the worksheet, the
worksheet should not allow any person to edit the previous
days data execept for the current day and the future.

Request you to provide me a step by step method to create
this function.

Regards

Deepak Jeyakaran
 
Deepak

AFAIK this cannot be done as you say. I would copy the old
data to a new workbook that the members can't see. You
could use a macro for this.

Regards
Peter
 
Deepak, ths will protect the cell after input, put in worksheet code and
unlock all cells in the range first.
Ypu will also need to protect your VBA project so no one can see the
password in it,
To protect the VBA project, from your workbook right-click the workbook's
icon and pick View Code. This icon is to the left of the "File" menu this
will open the VBA editor, in the left hand window right click on your
workbook name and select VBA project properties, protection, check lock
project for viewing and set a password. Press Alt and Q to close this window
and go back to your workbook and save and close the file

Private Sub Worksheet_Change(ByVal Target As Range)
'Automatically Protecting After Input
'unlock all cells in the range first

Dim MyRange As Range

'change range as needed
Set MyRange = Intersect(Range("A1:D100"), Target)
If Not MyRange Is Nothing Then
'Change passwords to your passwords
Sheets("Sheet1").Unprotect password:="123"
MyRange.Locked = True
Sheets("Sheet1").Protect password:="123"
End If
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
Paul B said:
Deepak, ths will protect the cell after input, put in worksheet code and
unlock all cells in the range first.
Ypu will also need to protect your VBA project so no one can see the
password in it, ....
Private Sub Worksheet_Change(ByVal Target As Range)
'Automatically Protecting After Input
'unlock all cells in the range first
Click to expand...
....

This is a pood design. Humans have been known from time to time to make
mistakes, like typos. Using a change macro to protect cells upon entry is
very likely to cause frequent problems.

Next, if users are clever enough to look in VBA modules to find passwords,
they're likely smart enough to search newsgroups to find out how to disable
passwords. EXCEL DOES NOT PROVIDE TRUE SECURITY! Relying on it to do so is
foolish. Excel provides only lightweight idiot-proofing.

Anyway, the ideal method of 'freezing' attendance entries would be to have
the BeforeClose event handler e-mail the current workday's attendance sheet
to a central source. This assumes the machine on which this workbook resides
has an e-mail connection and that the file would be opened and closed each
workday. If not, more details about how the file would be used and how the
machine that would run it was configured would be useful.
 
Harlan,
Can you provide more information about hwo you would do this via email? I
have something siilar and this sounded like a good idea until you mentioned
mistakes.

What would you suggest.

Cheers,
Dean
 
dean collins said:
Can you provide more information about hwo you would do this via email? I
have something siilar and this sounded like a good idea until you mentioned
mistakes.
Click to expand...

There are threads in the Google Groups archives that deal with e-mailing
individual worksheets. Just add logic to select the current day's worksheet,
and put the whole thing in the workbook's BeforeClose event handler.

Not necessarily a good example, but see

http://groups.google.com/[email protected]
 
Back
Top