Excel Data Protection

pamupamalal

New Member
Joined
Jul 10, 2009
Messages
5
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
 
Upvote 0
Perhaps this will solve your problems... So long as the sheet is protected. Placed in the ThisWorkbook Object

Code:
'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
 
Upvote 0
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
 
Upvote 0
See if this Helps

Code:
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
 
Upvote 0
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

Code:
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
 
Upvote 0
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

Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top