• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing cell from Unlocked to Locked on data entry

C

clumpa

Member
I am an Excel novice and have never written/recorded a macro!

I have a protected worksheet with unlocked cells for data entry. When data is entered I would like to automatically change the cell's format to locked.

Is this possible?
 
Hi ,


You can use the following VBA procedure. Click on the Developer button , then the Visual Basic button. If Sheet1 is the sheet on which you would like this to be done , double click on the Sheet1 text in the Project Explorer window on the left. A blank window will open. Copy the procedure given below into this.


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

Me.Unprotect

Target.Locked = True

Me.Protect

Application.EnableEvents = True

End Sub


Narayan


P.S. Remember that if any data entry error occurs , you cannot backtrack ! You will have to manually unprotect the worksheet and the cell which you would like to correct.
 
You must log in or register to reply here.
Back
Top