Prev: how do i copy/paste in excel without pasting hidden lines
Next: custom command
From: johnsail on 18 Feb 2010 06:19
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
From: JLatham on 18 Feb 2010 07:54
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
 | 
Pages: 1
Prev: how do i copy/paste in excel without pasting hidden lines
Next: custom command