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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.
 

seljo

New Member
Joined
Sep 29, 2006
Messages
12
I am wanting to include range B:B and range E:E and yes I do have it password protected.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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
 

UPN

Board Regular
Joined
May 14, 2006
Messages
138

ADVERTISEMENT

protect

What if i wanted to protect from let say B1 to h100
 

UPN

Board Regular
Joined
May 14, 2006
Messages
138
protect

also with no password what would code be
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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
 

Forum statistics

Threads
1,137,332
Messages
5,680,875
Members
419,937
Latest member
Talic

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
Top