Locking cell after data is entered

seljo

New Member
Joined
Sep 29, 2006
Messages
12
Is there any way to lock a cell after someone enters the data in it and the spreadsheet remains open until the next data point is entered.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hello seljo,
If you mean to include all cells in the sheet then you can do this.
1) Press Ctrl+A to select all cells and from the menu choose Format > Cells > Protection tab
and uncheck the 'Locked' checkbox
2) Right click the sheet's name tab and choose View code.
3) Paste this code in the sheet module. Press Alt+Q to get back to the sheet.
4) Protect your sheet. (Tools > Protection > Protect Sheet. -in this case with no password.)

You should be good to go.
Post back if you only meant to include certain cells/ranges or if you do use a password in your
sheet protection.
 
Upvote 0
I am wanting to include range B:B and range E:E and yes I do have it password protected.
 
Upvote 0
Oh, well I suppose it would've helped if I had remembered to post the code! :rolleyes:
Following the same instructions above and using this code should do what you're asking.
(You could also select just the columns B & E instead of the entire sheet to unlock if you like.)
Replace both instances of the word Password with your real one.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("B:B")) Is Nothing And _
   Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
ActiveSheet.Unprotect "Password"
Target.Locked = True
ActiveSheet.Protect "Password"
End Sub
 
Upvote 0
Hello UPN,
What if i wanted to protect from let say B1 to h100 . . .also with no password what would code be
Following the same instructions above to unlock cells first, this should do that for you.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("B1:H100")) Is Nothing Then Exit Sub
ActiveSheet.Unprotect
Target.Locked = True
ActiveSheet.Protect
End Sub
 
Upvote 0

Forum statistics

Threads
1,218,619
Messages
6,143,509
Members
450,492
Latest member
Rusbus1972

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
Back
Top