I have a spreadsheet that tracks performance. Some info is put in by many
different users. Other info is put in by our Admin team. All the cells that
our Admin team updates is locked and the sheet is always protected.
Is there a macro that will lock the cells that the users make changes to
once they have entered and saved the inforation - I am hoping to prevent
changes/deletions once the information has been entered.

I am a novice at vba.
Thankx in advance

Hi Clark,

Try something like:

'=============
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim SH As Worksheet
Dim rng As Range
Const PWORD As String = "ABC" '<<=== CHANGE

Set SH = Me.Sheets("Sheet1") '<<=== CHANGE

With SH
On Error Resume Next
Set rng = SH.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Not rng Is Nothing Then
.Unprotect Password:=PWORD
.Cells.Locked = False
rng.Cells.Locked = True
.Protect Password:=PWORD
End If
End With
End Sub
'<<=============

This is workbook event code and should be pasted into the workbook's
ThisWorkbook module *not* a standard module or a sheet module:

Right-click the Excel icon on the worksheet
(or the icon to the left of the File menu if your workbook is maximised)
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman


"clarknv" wrote in message
...
I have a spreadsheet that tracks performance. Some info is put in by many
different users. Other info is put in by our Admin team. All the cells
that
our Admin team updates is locked and the sheet is always protected.
Is there a macro that will lock the cells that the users make changes to
once they have entered and saved the inforation - I am hoping to prevent
changes/deletions once the information has been entered.

I am a novice at vba.
Thankx in advance

Hi Clark,

Replace my suggested code with the following version:

'=============
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim SH As Worksheet
Dim rng As Range
Const PWORD As String = "ABC" '<<=== CHANGE

Set SH = Me.Sheets("Sheet1") '<<=== CHANGE

With SH
.Unprotect Password:=PWORD
On Error Resume Next
Set rng = SH.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Not rng Is Nothing Then
.Cells.Locked = False
rng.Cells.Locked = True
.Protect Password:=PWORD
End If
End With
End Sub
'<<=============



---
Regards,
Norman