excel macro help

[Guye]

Hi,
Maybe someone can help me.
i need to create a marco that preforms the following:
at the beginning all worksheet is locked, but the cells on column A.
1) if data is entered into cell a7 then cell b7 unlocks and if data is
entered to cell b7 cell c7 unlock and so forth for row 1.
the same must apply to all the other rows on the worksheet.
the only cells that are not locked are those on column A from row 7,
and the rows 1-7 which are locked all the time.
thanks

 

[Don Guillett]

Try this idea. Right click sheet tab>view code>copy/paste this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Locked = True Then Exit Sub
ActiveSheet.Unprotect
Target.Offset(, 1).Locked = False
ActiveSheet.Protect
End Sub

 

[Guye]

one more thing i completely forgot about,
is it possible to creat the reversible, if i delet the info from the
cell the next cells will be locked

thank
guy

 

[Guye]

hi
How can i activate the previous Sub automaticaly when the file opens.
thanks guy

 

[Mike Fogleman]

Here is something simple that will work.
The code goes in the worksheet module, not a general module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 7 Then Exit Sub

If Target.Value <> "" Then
Me.Unprotect
Target.Offset(0, 1).Locked = False
Me.Protect
End If
End Sub

Mike F

 

[Mike Fogleman]

Here is the reverse procedure, but it will only work if you clear one cell
at a time from column A only.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 7 Then Exit Sub

If Target.Value <> "" Then
Me.Unprotect
Target.Offset(0, 1).Locked = False
Me.Protect
End If


If Target.Column < 2 And Target.Value = "" Then
Application.EnableEvents = False
Me.Unprotect
With Target.EntireRow
.Clear
.Locked = True
End With
Target.Locked = False
Me.Protect
Application.EnableEvents = True
End If
End Sub


Mike F

 

[Mike Fogleman]

I am not sure what you mean. This is Worksheet Event code, which means when
any cell content is changed, the code will run.

Mike F

 

[Guye]

thank Mike,
you have been of great help to me

 

[Guye]

hi
no, its not an event code.
the file is just made to store information.

by the way, the Sub you gave me locks the cells but it also formats
all the rows characteristic such as color etc, etc, and not only
erase the information on the row

 

[Mike Fogleman]

The code that Don and I gave you is event code, if pasted where Don said to.
Apparently you did or it would not work right. To fix the cell formats from
being changed when you clear a cell in column A, change .Clear to
..ClearContents.

Mike F

 

[Guye]

thanks for the code
it works now perfectly