Macro Help

[David Ryan]

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

 

[Kevin Smith]

Hello David.

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

 

[Joel]

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]

Hi Kevin
Manually deleting the data. Thanks

Hello David.

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

 

[Kevin Smith]

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]

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

 

[David Ryan]

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

 

[David Ryan]

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]

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.

 

[David Ryan]

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]

Lets do a little walk through from the begining.

with a blank worksheet, you want to enter data in cells A1 to A20. the code
that you had to start off with protects a cell after you have entered data
into it. for this to work you must first select the range A1:A20 and format
the cells so that the locked option is un-ticked. by doing this the worksheet
will be protected but these cells will remain active.

you code...

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

will protect the cell once data has been entered. if you unprotect the
worksheet and delete all of the data out or the range A1:A20 these cells will
still remain locked.

To unlock these cells so that you can enter data into them again you need to
go to the formatting options for the cells and ensure that the "locked"
option is unticked again. To do it through code then you can just paste this
into a standard Module

Sub Reset()
Dim c As Range
ActiveSheet.Unprotect Password:="thepassword"
Range("A1:E20").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

then run this code from Excel by selecting from the Menu Bar

Tools--Macro--Macros
select reset and then click on Run.

this code just looks for cells that are blank and locked within the range
that you specify. once it finds a cell that meets this criteria it uses the
"c.Locked = False" to upprotect the selected cell.

fingers crossed this should work for you.

 

[Patrick Molloy]

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]

Hi Kevin

yes feel like a goose working very well. Thanks heaps for your help.

 

[David Ryan]

Hi Joel

Great help thanks

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