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

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

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

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

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

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

When you clear multiple cells only the first cel.l is being reset. ttyr this
change

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error GoTo justenditall
ActiveSheet.Unprotect Password:="thepassword"

Application.EnableEvents = False
for each cell intarget
If Not Intersect(cell, Range("A1:A20")) Is Nothing Then
If cell.Value < "" Then
cell.Locked = True
End If
End If
next cell
ActiveSheet.Protect Password:="thepassword"

justenditall:
Application.EnableEvents = True
End Sub


"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

Hi Joel
Unfortunately did not work and the same problem. The previous used cells
remained locked

"Joel" wrote:

When you clear multiple cells only the first cel.l is being reset. ttyr this
change

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error GoTo justenditall
ActiveSheet.Unprotect Password:="thepassword"

Application.EnableEvents = False
for each cell intarget
If Not Intersect(cell, Range("A1:A20")) Is Nothing Then
If cell.Value < "" Then
cell.Locked = True
End If
End If
next cell
ActiveSheet.Protect Password:="thepassword"

justenditall:
Application.EnableEvents = True
End Sub


"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

Hi Joel

Great help thanks

"David Ryan" wrote:

Hi Joel
Unfortunately did not work and the same problem. The previous used cells
remained locked

"Joel" wrote:

When you clear multiple cells only the first cel.l is being reset. ttyr this
change

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error GoTo justenditall
ActiveSheet.Unprotect Password:="thepassword"

Application.EnableEvents = False
for each cell intarget
If Not Intersect(cell, Range("A1:A20")) Is Nothing Then
If cell.Value < "" Then
cell.Locked = True
End If
End If
next cell
ActiveSheet.Protect Password:="thepassword"

justenditall:
Application.EnableEvents = True
End Sub


"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

your code for resetting should switch off the change event to prevent locking

Option Explicit

Sub demo()
UnlockSelection Selection
End Sub
Sub UnlockSelection(target As Range)
Application.EnableEvents = False

ActiveSheet.Unprotect Password:="thepassword"

With target
.ClearContents
.Locked = False
End With
ActiveSheet.Protect Password:="thepassword"

End Sub


"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