Link to home
Start Free TrialLog in
Avatar of Tavasan65
Tavasan65

asked on

locking excel cell after user enters data

Is it possible to lock specific cells from being changed after data is entered into it?

If it is possible how can it be done?
Avatar of andrewssd3
andrewssd3
Flag of United Kingdom of Great Britain and Northern Ireland image

It is possible.  As I understand your requirement, try entering the following code in the Sheet1 (or whatever) code section in the VBAproject. This makes the first entry final and any other entry gets ignored.  Other solutions would involve locking the whole worksheet, which is also possible.

Private Sub Worksheet_Change(ByVal Target As Range)
    Static blnChanged As Boolean
    Static vFirst As Variant
    If Target.Address = Application.Range("b3").Address Then
        If Not blnChanged Then
            vFirst = Target.Value
            blnChanged = True
        Else
            Target.Value = vFirst
        End If
    End If
End Sub

Open in new window

Avatar of Tavasan65
Tavasan65

ASKER

Will that lock data in columns N thru AA once data is entered?
ASKER CERTIFIED SOLUTION
Avatar of krishnakrkc
krishnakrkc
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can certainly do this - my code was just an example.  Can you tell me exactly what you want? For example, this code would allow you to change the cell B3 once after the workbook is opened, then not again.  You might want the cell to be locked whenever it contains a non-empty value even straight after opening.  If you want to lock a different range of cells, it may be necessary to use sheet protection.  

Perhaps you could post an example workbook with some more details of what you want to achieve?
My last comment was made before I saw krishnakrkc's comment, so just to make it clear I was talking about my code not his.  I think it would still be useful to have a clear statement of your requirement.

Stuart
In cells A thru I are the details of call contacts.
In cells L thru AA are the details of contacts and calls.

Here is the situation.  Initially, I may be talking with one person as identified in columns B67 and their contact details follows in columns C67 thru I67.  Later the contact has changed.  Contact details such as phone number, email, or the actual names themselves may change and are linked to columns R, S, and T.

The prior linked information in columns R, S, and T should not change and be locked from any contact details changed in columns B67 thru I67 and below row 67.

I hope this makes sense.

Thanks in advance.
Sample-Layout.xlsm
Hi,

Have you try the code I posted above ?
Hi,

I am not versed with coding.  How do I implement your code to check it out?

Right click on the tab name and paste the code before the first line of existing codes.

Kris
I'm sorry.  Can you post steps so I can implement the code and see if it works?