VB Coding
 

I have a spread sheet with all cells locked except B1. Spreadsheet is
protected allowing access to unlocked cells.

I would like to be able to step through the spreadsheet by "unlocking" the
next cell when data is entered into B1 and for the cursor to move to that
next cell.

I would like this sequence to be repeated until all required cells have been
completed.

Try as I may I cannot get the program to even unlock the first cell in the
sequence so as to allow the cursor to be moved to that cell.
I am using a Change event that first unprotects the sheet, purports to
unlock the next cell (on the next row - 7 columns further along.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="MyPass"
'If Target.Row = 1 Then
If Not Intersect(Target, Range("B:B")) Is Nothing Then GoTo last
Target.Offset(1, 7).Locked = False

GoTo last

last:
Application.EnableEvents = True
Application.ScreenUpdating = True
ActiveSheet.Protect Password:="MyPass"
End Sub

What am I doing wrong?

regards
John

VB Coding
 

I believe if you will change
If Not Intersect(Target, Range("B:B")) Is Nothing Then GoTo last
Target.Offset(1, 7).Locked = False

to
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Target.Offset(1, 7).Locked = False
End If

plus then you can do away with the "GoTo last" statement right after that.


"johnsail" wrote:

I have a spread sheet with all cells locked except B1. Spreadsheet is
protected allowing access to unlocked cells.

I would like to be able to step through the spreadsheet by "unlocking" the
next cell when data is entered into B1 and for the cursor to move to that
next cell.

I would like this sequence to be repeated until all required cells have been
completed.

Try as I may I cannot get the program to even unlock the first cell in the
sequence so as to allow the cursor to be moved to that cell.
I am using a Change event that first unprotects the sheet, purports to
unlock the next cell (on the next row - 7 columns further along.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="MyPass"
'If Target.Row = 1 Then
If Not Intersect(Target, Range("B:B")) Is Nothing Then GoTo last
Target.Offset(1, 7).Locked = False

GoTo last

last:
Application.EnableEvents = True
Application.ScreenUpdating = True
ActiveSheet.Protect Password:="MyPass"
End Sub

What am I doing wrong?

regards
John