I need a VBA script that automatically locks cells

[HAL]

I have a excel sheet that multiple people use that is used for imputting batch numbers and the like the issue is people keep imputting data into previous cells and messing up data


I need only cells with text in to be locked and the cells cells to be unlocked so people can still add data to the excel sheet


Im also very new to excel so if you can give a hint on how to get it running properly just incase I make a mistake that would be greatly appreated


also im using excel 2003

 

[fred]

i'm using MS 2007 and here is how I do it the simple way. But 2003 set up is similar.


select the cells that you allow others to input data.

right click on the selected cell.

go to "Format Cells..."

go to "Protection" tab

uncheck the "Locked" box

Hit "OK" key


go to "Review" tab on the menu bar

Click "protect sheet" and set up a password. you will need to re-enter the password to set it up.

Save your password somewhere safe.


Note: make sure cells holding previous data HAVE the Locked box CHECKED!

 

[HAL]

I should have also put that I need it so once text is inputted the cell locks itself {after saving or some other action} So previous boxes cant be overwritten

 

[HAL]

Im still needing a macro because using the protect sheet feature doesnt do all i need


I need cells to automatically lock once data is imput but still allow people to imput text on the cells that have no text in yet

 

[Luke M]

Right-click on sheet tab, view code, paste this in:


Private Sub Worksheet_Change(ByVal Target As Range)


If WorksheetFunction.IsText(Target.Value) Then

ActiveSheet.Unprotect "MyPassword" 'Delete if no password needed

Target.Locked = True

ActiveSheet.Protect "MyPassword"

End If


End Sub


Make sure your users are warned that there's no "undo" if they type text into a cell and then want to change it later.

 

[HAL]

Private Sub Worksheet_Change(ByVal Target As Range)


If WorksheetFunction.IsText(Target.Value) Then

ActiveSheet.Unprotect "MyPassword" 'Delete if no password needed

Target.Locked = True

ActiveSheet.Protect "MyPassword"

End If


End Sub


tryed that code but it still locks all cells

tryed 6 scripts so far and each one says it shouldnt be locking all the cells but it does Could I be doing something wrong and not making some changes I should be?

 

[Luke M]

Before implementing code, you need to format the cells properly. Format - Protection, uncheck the "locked" option. Now these cells won't become locked when you protect the worksheet. The code will then switch this "locked" option once text has been inputted into the cell.