Hi

is not working. I cut and pasted your code and changed the cell reference to
match mine, the cells i delete the data out of become locked as soon as i
delete data. when i unprotect the sheet to enter data all previous used cells
become locked as soon as i enter data into one.

"Kevin Smith" wrote:

This code allows you to set the range that you want to check

Sub Reset()

Dim c As Range
ActiveSheet.Unprotect Password:="thepassword"
Range("A1:A200").Select
For Each c In Selection
If c.Value = "" And _
c.Locked = True Then
c.Locked = False
End If
Next
Range("A1").Select
ActiveSheet.Protect Password:="thepassword"

End Sub

once you have deleted the data from the cells this will check you range and
if a cell is locked and also blank it will unprotect the cell so that you can
enter data again.
--
Kevin Smith :o)


"David Ryan" wrote:

Hi Kevin i feel like i am being difficult

not all data gets deleted at the same time, nor are the cells in sequence ie
may need to delete a2, a5:a7 & a35 then a bit later another group etc.

"Kevin Smith" wrote:

if you wanted to do it through VB you could use this in a standard Module

Sub Reset()

ActiveSheet.Unprotect Password:="thepassword"
Range("A1:A20").Select
With Selection
.Locked = False
.ClearContents
End With
Range("A1").Select
ActiveSheet.Protect Password:="thepassword"

End Sub
--
Kevin Smith :o)


"Kevin Smith" wrote:

The problem here is that the cells are being locked and they are not being
unlocked once the data has been deleted.

You need to select the range, right Click in the selection, go to format
Cells, under the Protection Tab you need to make sure that the tick box of
Locked is un-ticked.

This will allow you to enter text again into these cells.
--
Kevin Smith :o)


"David Ryan" wrote:

Hi Kevin
Manually deleting the data. Thanks

"Kevin Smith" wrote:

Hello David.

How are you deleting the data from the cells? do you do it through code or
manually?

--
Kevin Smith :o)


"David Ryan" wrote:

Hi folks
I need to have a section of a spreadsheet become locked after data is
entered. I found a great macro someone wrote on the net that does the trick.
The code is;

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error GoTo justenditall

Application.EnableEvents = False
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
If Target.Value < "" Then
ActiveSheet.Unprotect Password:="thepassword"
Target.Locked = True
End If
End If

ActiveSheet.Protect Password:="thepassword"

justenditall:
Application.EnableEvents = True
End Sub

As I said this works well. However I need to reset the cells at times so
that they behave as if originally blank.
What happens is once the cell/s have been used when I delete the data then
insert new data the cells that have already been used and "emptied" all
become locked straight away. However the Cells that have never had data in
them still behave OK ie remain usable till used for the first time.

I hope this makes sense and will appreciate any help.

Note the macro above is not mine but obtained by net search. Not sure who
wrote it but all cudos to them.

Regards David