Protect cells IF the cell is not empty
 

Planning to make this excel file in which users should be able to fill in new
data in empty cells but not in cells that have data in them already.

I.e. IF Cell is empty, the user is allowed to fill in data. IF Cell is not
empty, cell is protected.

This way the users can only add new data to the chart but not modify old data.

thanks in advance

Protect cells IF the cell is not empty
 

right click on the sheet tab and put in code like this:

Before doing that, unlock all your cells, then select filled cells and lock
those. Then protect the sheet with your password. Just for consideration,
if the user makes a typo when they enter data or enter data in the wrong
cell, there is no correcting it.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
Me.Unprotect Password:="ABCD"
For Each cell In Target
If Not IsEmpty(cell) Then
cell.Locked = True
End If
Next
ErrHandler:
Me.Protect Password:="ABCD:"
End Sub


--
Regards,
Tom Ogilvy


"Boka" wrote in message
...
Planning to make this excel file in which users should be able to fill in
new
data in empty cells but not in cells that have data in them already.

I.e. IF Cell is empty, the user is allowed to fill in data. IF Cell is not
empty, cell is protected.

This way the users can only add new data to the chart but not modify old
data.

thanks in advance

Protect cells IF the cell is not empty
 

Thanks for the help but unfortunately your solution only worked once. Only
one cell got locked (stayed locked) after typing something into it. The other
cells were modifyable over and over again.

It's strange since looking at your code it looks as it should work.

Any suggestions?

"Tom Ogilvy" wrote:

right click on the sheet tab and put in code like this:

Before doing that, unlock all your cells, then select filled cells and lock
those. Then protect the sheet with your password. Just for consideration,
if the user makes a typo when they enter data or enter data in the wrong
cell, there is no correcting it.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
Me.Unprotect Password:="ABCD"
For Each cell In Target
If Not IsEmpty(cell) Then
cell.Locked = True
End If
Next
ErrHandler:
Me.Protect Password:="ABCD:"
End Sub


--
Regards,
Tom Ogilvy


"Boka" wrote in message
...
Planning to make this excel file in which users should be able to fill in
new
data in empty cells but not in cells that have data in them already.

I.e. IF Cell is empty, the user is allowed to fill in data. IF Cell is not
empty, cell is protected.

This way the users can only add new data to the chart but not modify old
data.

thanks in advance

Protect cells IF the cell is not empty
 

Tom's code (with one modification) worked fine for me:

Me.Protect Password:="ABCD:"
became
Me.Protect Password:="ABCD"

And I made sure the cells that could change were unlocked to start.

(I'm guessing that Tom saves lots of stuff to his D: drive (muscle memory
strikes again!) <vbg.)



Boka wrote:

Thanks for the help but unfortunately your solution only worked once. Only
one cell got locked (stayed locked) after typing something into it. The other
cells were modifyable over and over again.

It's strange since looking at your code it looks as it should work.

Any suggestions?

"Tom Ogilvy" wrote:

right click on the sheet tab and put in code like this:

Before doing that, unlock all your cells, then select filled cells and lock
those. Then protect the sheet with your password. Just for consideration,
if the user makes a typo when they enter data or enter data in the wrong
cell, there is no correcting it.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
Me.Unprotect Password:="ABCD"
For Each cell In Target
If Not IsEmpty(cell) Then
cell.Locked = True
End If
Next
ErrHandler:
Me.Protect Password:="ABCD:"
End Sub


--
Regards,
Tom Ogilvy


"Boka" wrote in message
...
Planning to make this excel file in which users should be able to fill in
new
data in empty cells but not in cells that have data in them already.

I.e. IF Cell is empty, the user is allowed to fill in data. IF Cell is not
empty, cell is protected.

This way the users can only add new data to the chart but not modify old
data.

thanks in advance





--

Dave Peterson