Solved:

[rangudu_2008]

Hi vbax mates,
I too have the same requirement as the user who started this thread...

My workbook contains 4 sheets and lots of formulas in the time cature sheet. A vast majority of the cells in this sheet are locked and the entire workbook is protected with password.

Time capture is done by clicking the buttons after choosing the appropriate cell and placing the cursor in it. (Ref. attached screenshot)
When time sheets of different users are compared at the end of each fortnight/month, the managers should have the ability to unlock these cells.

I've tried out the code module proposed below, which removes the password protection and enables it back; but the sheet protection is at stake.
I tried out a different option by using Validations from the Data menu, but i'm not done with it fully yet.
It was an idea which i got while browsing thru this forum, the link of which is as below:
http://www.vbaexpress.com/forum/showthread.php?t=21083&highlight=locking+a+cell+in+Excel+after+a+value+is+entered

Can someone help me implement it using Validations, since the sheets and the workbook are protected with passwords?

Ranga

[Bob Phillips]

It is not clear what you are trying to do, or what the problem is. How will Data Validation solve the problem?

[rangudu_2008]

By coding in the Worksheet_Change event, the sheet password is removed first, the cell is locked and then the sheet is protected again...
My time capture sheet contains a lot of formulas and such cells will have to be locked which is done by protecting the sheet with a password.

I tried that, but my sheet protection is affected... Its not a bad idea, but i wanted to lock those cells where time is captured after the buttons are clicked.

I tried using data validation, the same idea as in the attached workbook (slightly modified) but the validation needs to work after the time is captured in the appropriate cells... This validation needs to be extended to all the cells which will store the In-Out time everyday...

These timestamps captured are used in the same worksheet for further computation & they are collated and reported. While collating data, these validations need to be turned off (cells unlocked) and should be enabled back when required.

Ranga

[rangudu_2008]

Hi xld,
How can data validation be invoked/revoked thru VBA coding? In my time capture sheet, i need to impose restictions when the buttons are clicked.. But while collation is done, i need to remove restrictions...

Ranga

[rangudu_2008]

Aaron, Bob & Jimmy,
Any ideas on how this validation part can be implemented?

Ranga

[rangudu_2008]

Dear Ken Puls,
Do u have any ideas on implementing this validation?

Ranga

[Simon Lloyd]

rangudu_2008 please do not hijack someone elses thread, start one of your own, also please be patient, people who help here also work and give help here for free in their own free time, when they have a moment to answer your question they will!

[Simon Lloyd]

Posts moved to new thread!

[rangudu_2008]

Is there any other way by some type of validation or via VBA coding to lock data into the cell without unprotecting the sheet after time is captured by clicking the buttons?

I'm racking my brains to get an idea for implementing this...

Ranga

[rangudu_2008]

The time capture sheet contains only 2 specific rows to store the start and end times.

Can this cell range (that stores start & end times) be made uneditable (which i'm trying to do) by detecting the ASCII keystrokes while the end user tries to edit the cell to change the time (done via VBA code)?

Ranga

[Aussiebear]

Why aren't you locking some of the cells, yet allowing the user access to others?

[rangudu_2008]

Those cells which store the time are the only ones which need data from the user and to prevent users modifying it, i need to lock them out once the time is captured in the appropriate cell... Users should only be able to view the contents of this sheet... No other things can be done.. The format of those cells are HH:MM:SS AM/PM

R anga

[rangudu_2008]

Since worksheet contains a lot of formulas which users should not modify, i'm protecting the worksheet with a password. The code i tried out removes this pwd & is set back once that cell is locked.. The end user won't know the password, so my code won't work; also my other idea of validation will work if it can be implemented thru VBA code.

Ranga