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!

Goldfield

New Member
Joined
Oct 29, 2008
Messages
48
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

Well-known Member
Joined
Jun 11, 2006
Messages
1,985
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
 

pamupamalal

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

Well-known Member
Joined
Oct 19, 2006
Messages
567

ADVERTISEMENT

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
 

pamupamalal

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

pamupamalal

New Member
Joined
Jul 10, 2009
Messages
5

ADVERTISEMENT

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
 

pamupamalal

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

New Member
Joined
Oct 29, 2008
Messages
48
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.
 

Forum statistics

Threads
1,136,326
Messages
5,675,116
Members
419,551
Latest member
thangxpm

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
Top