Lock cell after entry

[gsp143]

Hello,

I need to be able to lock cells after an entry is made into that cell. The ws is potected and vast majority of it is locked. After the initial set up of the ws it acts as a template and individual workers come along and fill in their appropriate info. I need to lock the cells that the workers are entering data into after they have entered text and clicked away. I also need to give the managers the ability to unlock these cells in the event that the values are entered incorrectly.

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
Me.Unprotect
.Locked = True
Me.Protect
End With

End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[jimgus]

Is there a way to lock the unlocked cells in a sheet once the user has inputed all the information, ensured it is correct, and is ready to forward. Could this be done with a button or other means. jrg

[Bob Phillips]

Sure, just use



Sub LockAll()

With Activesheet
.Unprotect
.Cells.Locked = True
.Protect
End With

End Sub

[jimgus]

How do I assign a macro to a command button. The macro from xld appears to be what I want but I want it to activate once the command button is used. This is the first time I have used command buttons in excel. Thanks for any help. This is the current code that is shown with the command button: What and how do I add so the macro will work. jrg

Private Sub CommandButton1_Click()
End Sub

[Bob Phillips]

Make the Forms toolbar visible, Tools>Customise>Toolbars and check Forms, and then drag the command button onto the worksheet. When you do, youw ill get a dialog to assign a macro.

[jimgus]

Some days I confuse myself. Got the lock cell macro to work but it requires me to take the protection off the sheet in order to lock all cells. I want an employ to push a command button once they are complete inputing information in cells that they can select and after they activate the command button the macro protects the cells that were orginally selectable. Hopefully this is not to confusing. Thanks for the help. jrg

[jproffer]

Set all the cell properties (in cells>format cells) to locked...and unlock the sheet...when the macro makes the sheet lock, all the cells will be locked.

[jimgus]

I need to have the sheet protection password protected once information is entered so that the sheet can not be changed without managerial help. The password thing is giving me the problem Thanks for the help. jrg

[jproffer]

Protect with password:

Sheets("name").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"

[jimgus]

Thanks. Works fine. jrg