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