Macro Button or Auto Protect Formula

[enna49]

Is there anyway I can protect only cells that have data. eg after
entering data for a number of lines I would like to protect only the cells
that have data in one step.

Therefore when the worksheet is opened again the user can continue to enter
data and in one step protect what has been done.
Thanks
AR

 

[Bob Bridges]

I don't protect data much so I'm not sure what other solutions there may be.
But I think you could do this by writing a VBA program; it would use the
Worksheet_Change event, and set protection for the new cell if its value is
non-blank. That would save the protection between sessions, too. Dunno
whether you like that idea, though, since you posted this in the Worksheet
Functions group.

 

[Gord Dibben]

Assuming you have all cells unlocked on a clean sheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="justme" 'adjust to suit
With Target
If .Value <> "" Then
.Locked = True
End If
End With
enditall:
Application.EnableEvents = True
With Me
.Protect Password:="justme"
End With
End Sub

As you enter data in a cell, it will locked and protected from editing.

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module.

Adjust password if you want then Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

 

[enna49]

Hi

Thanks for your response

This works as it protects the sheet.
As soon as I unprotect the sheet I can only enter data into one blank cell
and then all blank cells are protected. I can change the cells that have
data already input to them. What I require is the cells with data to be
protected and the blank cells to remain unprotected.
Appreciate you help
Thanks