PDA

View Full Version : deny changes after entering data



cosmos82
12-04-2010, 03:50 PM
Hey there,

I need a code that prevents cells from changing.

I work in a big company and i must secure that data can not be change once it was entered. My collegues fill into the sheet (Collum A to N) some date. After they did that and close the file all the filled cells should be looked.

I know the code Workbook.BeforeClose. But I don't know how to protect the cell in which the data was filled in.

Any ideas? I Would appreciate your help.

Sean.DiSanti
12-04-2010, 04:26 PM
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets(1).UsedRange.Locked = True
Worksheets(1).Protect ("password")
End Sub
i would go with before save rather than before close, that way you're not wasting time if they're closing without saving. change password to be whatever you want the pass to be

macropod
12-04-2010, 06:46 PM
Hi cosmos82,

Welcome to VBA Express.

Try adding the following code to the relevant worksheet's code module:
Option Explicit
Dim MyVal
Private Sub Worksheet_Change(ByVal Target As Range)
If MyVal = Empty Then Exit Sub
If Intersect(Target, ActiveSheet.Range("A1:E1000")) Is Nothing Then Exit Sub
With Application
.EnableEvents = False
MsgBox "You can't alter this cell.", vbExclamation + vbOKOnly
.Undo
.EnableEvents = True
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, ActiveSheet.Range("A:E")) Is Nothing Then Exit Sub
MyVal = Target.Formula
End Sub