Use VB to lock excel cells 1

[techie131]

Is there a way to lock the excel file using VB so people can input data but not be able to change it afterwards. I was told that there is no way to do it using regular excel options but could be done using VB.

 

[AnotherHiggins]

Just to make sure I understand:

You want to allow a user to put data into an empty cell. But once the user presses Enter (or Tab, etc.), you do not want them to be able to go back and change the cell that they just entered?

Question: What if someone makes a typo? You aren't going to allow them to fix their own mistakes?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

This is very basic. I'd suggest using a back door to turn Events on and off in order to "fix" things.

Firther, it assumes that a multiple cell selection or paste is not included. That can be modified if necessary, but the logic becomes more complex.

NAME the range that you want to "protect", MyRange

Paste this code in the SHEET CODE WINDOW - right-click the sheet tab and select View Code...

Dim PrevVal
Sub EventsON()
    Application.EnableEvents = True
End Sub
Sub EventsOFF()
    Application.EnableEvents = False
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then
        PrevVal = ""
        Exit Sub
    Else
        If Not Intersect(Target, [MyRange]) Is Nothing Then
            If PrevVal <> "" Then
                Target.Value = PrevVal
            End If
        End If
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then
        PrevVal = ""
        Exit Sub
    Else
        If Not Intersect(Target, [MyRange]) Is Nothing Then
            PrevVal = Target.Value
        End If
    End If
End Sub

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[techie131]

Skipvought that is what I was looking for but is there a way to set the lock function so it activates when the user moves to a different row. So they can go back and change data as long as they are in the same row but it will lock once they move on to a different row.

 

[SkipVought]

Try playing around with a PrevRow value, like the PrevVal. Check out Target.Row and test it.

Think thru the logic for what you are trying to do and how that would all work together.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[techie131]

Still having trouble setting it so if target.row changes than it activates the protection else you can still make changes to the data on that row. Been awhile since I had to do VB any other advise you can give me would be great.

 

[SkipVought]

What have you tried?

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[techie131]

Haven't done VB in a while but this is what I have tried so far. I first set an input box so that they can activate the EVENTSOFF just by hitting CTRL O and putting in a password. I than set prevrow = target.row when Sub EVENTSON() starts. Than I set it so when a new row is selected it sees if target.row = prevrow and allows changes. At this point I got stuck because my prevrow = target.row just sets off an error when I activate Sub EVENTSON().

Dim PrevVal
Dim PrevRow
Sub EventsON()
Application.EnableEvents = True
prevrow=target.row

End Sub
Sub EventsOFF()
If InputBox("Please enter the password") = "test" Then

Application.EnableEvents = False

End If

End Sub

Private Sub Worksheet_Change(ByVal target As Range)

prevrow = ""



If target.Count > 1 Then
PrevVal = ""
Exit Sub
Else
If Not Intersect(target, [a:Z]) Is Nothing Then
If target.Row = prevrow Then
PrevVal = ""

Else


If PrevVal <> "" Then
target.Value = PrevVal


End If
End If
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal target As Range)


If target.Count > 1 Then
PrevVal = ""
Exit Sub
Else
If Not Intersect(target, [a:Z]) Is Nothing Then
If target.Row = prevrow Then
PrevVal = ""
Else

PrevVal = target.Value
End If
End If

End If
End Sub

 

[SkipVought]

"I first set an input box so that they can activate the EVENTSOFF ..."

WHY? How do you expect to capture changes or prevent changes without EVENTS???

Please detail your LOGIC for how this is supposed to work. Please be painfully clear, concise and complete.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[techie131]

I set the input box because the supervisor of these people requested an easy way of turning off the protection. Your first reply would probably work for what they are asking but is it difficult to set the protection so it starts once someone changes a row. Basically when someone enters data in cell A1 they can make select and add data to B1 and still go back to A1 and change the data but if they go to any other row than all the data in the previously selected row because locked and cannot be changed. I'm not sure if this is even possible what my users were asking.

 

[SkipVought]

Do you understand how the initial code that I posted works? You need to take the time to study it and use it and thoroughly understand it. It would be a terrible idea if you installed this code on your users' workbooks and then could not help thme in the event of problems.

Given your understanding of the code and what's happening AND the tips I posted following regarding, "Try playing around with a PrevRow value, like the PrevVal. Check out Target.Row and test it," you ought to be able to figure this out. At least, make a stab at modifying it yourself, test it and then post back if you need help.

It can work! It DOES work in my test. But YOU need to figure it out! You have EVERYTHING that you need in order to make it work.

I don't particularly believe that its a GOOD practice to lock down cells like this. I think that the "back door" EventsOFF/ON will be a constant FIX process. But that's only me.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}