Solved

locking excel cell after user enters data

Posted on 2011-09-03
11
204 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:Tavasan65
  • 4
  • 4
  • 3
11 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36478963
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

0
 

Author Comment

by:Tavasan65
ID: 36479283
Will that lock data in columns N thru AA once data is entered?
0
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 500 total points
ID: 36479755
Hi,

Try this one.

Dim blnUnlockedAllCells As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Not blnUnlockedAllCells Then
        Me.Cells.Locked = False
        On Error Resume Next
        Me.Range("n:aa").SpecialCells(2).Locked = True
        On Error GoTo 0
        blnUnlockedAllCells = True
        Me.Protect , , , , 1
    End If
    If Not Application.Intersect(Target, Me.Columns("n:aa")) Is Nothing Then
        Target.Locked = True
    End If
   
End Sub

Kris
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36479764
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?
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36479778
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
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Tavasan65
ID: 36489195
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
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 36489694
Hi,

Have you try the code I posted above ?
0
 

Author Comment

by:Tavasan65
ID: 36491111
Hi,

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

Expert Comment

by:krishnakrkc
ID: 36495847

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

Kris
0
 

Author Comment

by:Tavasan65
ID: 36512130
I'm sorry.  Can you post steps so I can implement the code and see if it works?

0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 36512452
Hi,

PFA.

Kris
Sample-Layout.xlsm
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now