Move cell focus one to the right after sheet protect.

[SewerUrchin]

Most respected forum members: ray:

Here is a sample of the code I have for locking three cells after the user populates the cells. The problem I'm having is the system either highlights the range specified in line 5 of this code after the code is executed, or if I specify it, I can set the focus to A1. But that isn't what I need. . .

What I want to have the system do is mark where the cursor is by coordinate before the WorksheetChange code executes. (in my code it is variable NowCell) Then I can set the cursor back to the active cell that fired the code and then move the focus one column to the right. In essence, making easier for the person entering data to do it by row. I can't get the syntax of the final statement right and I'm wondering if it has to do with the variable type I'm using for NowCell? I'm also betting that I can't treat a variable like an Excel element, as I have done below. Thoughts? Opinions? Anything is appreciated.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim NowCell As Variant
NowCell = ActiveCell.Address(ReferenceStyle:=xlR1C1)
ActiveSheet.Unprotect Password:="password"
Range("A1:J10").Select
Selection.Locked = False
If ActiveSheet.Range("I4") <> "" Then
ActiveSheet.Range("I4").Locked = True
End If
If ActiveSheet.Range("B4") <> "" Then
ActiveSheet.Range("B4").Locked = True
End If
If ActiveSheet.Range("I6") <> "" Then
ActiveSheet.Range("I6").Locked = True
End If
ActiveSheet.Protect Password:="password"
NowCell.Offset(0, 1).SetFocus
End Sub

Thanks!

 

[Von Pookie]

What I want to have the system do is mark where the cursor is by coordinate before the WorksheetChange code executes. (in my code it is variable NowCell)

Actually, since you are using a Worksheet_Change code, you can just use the Target variable it already provides you (the variable is set in the sub statement 'Private Sub Worksheet_Change(ByVal Target As Range)'). Say you make a change in A2 which then fires the code. To the Worksheet_Change code, you can refer to that cell by simply using the name Target within the macro.

I can't get the syntax of the final statement right and I'm wondering if it has to do with the variable type I'm using for NowCell?

It's not necessarily the variable type you set NowCell as, but your choice to use SetFocus instead of Select. SetFocus is for userforms and controls, not cells

Try this:
<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)

ActiveSheet.Unprotect Password:="password"
Range("A1:J10").Locked = <SPAN style="color:#00007F">False</SPAN>

<SPAN style="color:#00007F">If</SPAN> ActiveSheet.Range("I4") <> "" <SPAN style="color:#00007F">Then</SPAN> ActiveSheet.Range("I4").Locked = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">If</SPAN> ActiveSheet.Range("B4") <> "" <SPAN style="color:#00007F">Then</SPAN> ActiveSheet.Range("B4").Locked = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">If</SPAN> ActiveSheet.Range("I6") <> "" <SPAN style="color:#00007F">Then</SPAN> ActiveSheet.Range("I6").Locked = <SPAN style="color:#00007F">True</SPAN>

ActiveSheet.Protect Password:="password"

Target.Offset(0, 1).Select

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[SewerUrchin]

Kristy:

The code worked beautifully! Many thanks for clarifying not only what code should be used but also why it should be used! I hope one day I can be as proficient so I might return the favor to you!

Jeff