Excel Data Protection

[pamupamalal]

Hi,

I needed some assisstance regarding excel data protection.

I wanted to permit addition of new data in my excel files by other users. While doing this there arises a possibility of the users editing the earlier data.
Thus I would like to know if there is any option/ possibility to disable editing earlier data but continue allowing addition of new data.

(I am aware of the feature of locking the rows and columns, but that becomes cumbersome as I always need to redefine the locked area which includes the latest entered data.)

Regards,
Varun

 

[Goldfield]

Unprotect the cells where the users can enter data

then

Enter the VBA editor using alt + f11

double click on the sheet you want to protect

in the code section paste this code (you can change the word secret to your own password)
This code will lock a cell every time data is entered into it

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Value = "" Then
Me.Unprotect Password:="secret"
Target.Locked = True
Me.Protect Password:="secret"
End If
End Sub

 

[phxsportz]

Perhaps this will solve your problems... So long as the sheet is protected. Placed in the ThisWorkbook Object

'Private Sub Workbook_open()
    Sheets("Sheet1").Activate
    Sheets("Sheet1").Unprotect
    r = Cells(Rows.Count, "A").End(xlUp).Row
    c = Cells(r, Columns.Count).End(xlToLeft).Column
    Range(Cells(1, 1), Cells(r, c)).Locked = True
    Sheets("Sheet1").Protect
End Sub

 

[pamupamalal]

Dear goldfield , the code that you provided helps but only once; after that the whole sheet gets locked.
My intention is to allow the users enter data every time they want to but they should never be able to edit the data entered earlier.

Please guide me if am making any mistakes.

Regards
Varun

Unprotect the cells where the users can enter data

then

Enter the VBA editor using alt + f11

double click on the sheet you want to protect

in the code section paste this code (you can change the word secret to your own password)
This code will lock a cell every time data is entered into it

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Value = "" Then
Me.Unprotect Password:="secret"
Target.Locked = True
Me.Protect Password:="secret"
End If
End Sub

 

[hsk]

See if this Helps

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Value <> "" Then
    MsgBox "Not allowed to Edit Data"
    'Select Next Empty Cell
    Dim lRealLastRow As Long
    Dim lRealLastColumn As Long
    On Error Resume Next
    lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
    lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
    Cells(lRealLastRow + 1, lRealLastColumn).Select
End If
End Sub

 

[pamupamalal]

hi hsk,

the code work absolutely fine.
thanks dear its a gr8 help for me

thanks to goldfiled & phxsports for their help.

Regards,
Varun

See if this Helps

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Value <> "" Then
    MsgBox "Not allowed to Edit Data"
    'Select Next Empty Cell
    Dim lRealLastRow As Long
    Dim lRealLastColumn As Long
    On Error Resume Next
    lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
    lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
    Cells(lRealLastRow + 1, lRealLastColumn).Select
End If
End Sub

 

[pamupamalal]

hi,

one more query,

how do i disable any user from entering the vba editor and removing the entered code.

Regards,
Varun

See if this Helps

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Value <> "" Then
    MsgBox "Not allowed to Edit Data"
    'Select Next Empty Cell
    Dim lRealLastRow As Long
    Dim lRealLastColumn As Long
    On Error Resume Next
    lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
    lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
    Cells(lRealLastRow + 1, lRealLastColumn).Select
End If
End Sub

 

[pamupamalal]

hi hsk,

With the code u provided even i am not able to delete the entered data. I want it to be disabled for other users but when required i should be able to delte / edit the data.

Please guide.

Regards,
Varun

hi,

one more query,

how do i disable any user from entering the vba editor and removing the entered code.

Regards,
Varun

 

[Goldfield]

You need to unprotect all the empty cells in the data entry area (using Ctrl+1 and untick locked), the code will lock the sheet but all empty cells remain unlocked until you enter data into that cell.

You call hide/lock you code by right clicking on the module containing the code, then selecting properties, and lock for viewing with a password.