This works fine but....

[clumein2]

This works correctly on 'unprotected' sheets. When the cursor hits column 14, it jumps back to col 6. But... as soon as I protect the sheet, the cursor jumps back to col 3. So I figured it was in the 'Allow all users of this worksheet to' checkboxs.Just to quickly see if it was, I allowed all... no good. I then applied protect workbook, but the same.
What do you think?

Public sColumn As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
sColumn = ActiveCell.Column
If sColumn = 14 Then
ActiveCell.Offset(1, -7).Range("A1").Select
End If
End Sub

Thanks

Cliff

 

[Zack Barresse]

Hi Cliff,

Or you could put the password into your code. Also, you already have your variable via the Target object passed ByVal in the change event. You can just use it's column value in your code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 14 Then
        Application.EnableEvents = False
        ActiveCell.Offset(1, -7).Select
        Application.EnableEvents = True
    End If
End Sub

Note the Application.EnableEvents lines. That is because this is a SelectionChange event and you're selecting another cell in your action. This means that another cell will be selected, hence the procedure will fire again. Turning the events off will disable any change events (ie SelectionChange, Change, etc), perform what you want to perform, then turn them back on (they don't reset in recent versions).

HTH

 

[Anthony47]

Hi Cliff,
in addition to what say firefytr, you should also check if when protecting the sheet the option "allow the selection of locked cells" is set to on.

Bye,

 

[Zack Barresse]

Good point Anthony47! Also note that this is only available in Excel 2002 and on.

 

[Anthony47]

Thank you firefytr; before asserting if it is a "good point" or only "a possible point" I shall wait and see if it helps Cliff !

Bye,

 

[clumein2]

Hi Cliff,
Or you could put the password into your code. Also, you already have your variable via the Target object passed ByVal in the change event. You can just use it's column value in your code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 14 Then
        Application.EnableEvents = False
        ActiveCell.Offset(1, -7).Select
        Application.EnableEvents = True
    End If
End Sub

Note the Application.EnableEvents lines. That is because this is a SelectionChange event and you're selecting another cell in your action. This means that another cell will be selected, hence the procedure will fire again. Turning the events off will disable any change events (ie SelectionChange, Change, etc), perform what you want to perform, then turn them back on (they don't reset in recent versions).
HTH

Well... that's fascinating LOL... I'm a little embarassed because............ I don't know WHAT the heck you're talking about.... I'm sorry if I gave the impression that I did that code.....

If you really wanna help this poor clueless soul, you're gonna have to put it in simpler terms then that.

regards
Cliff

 

[Anthony47]

I wrote: "you should also check if when protecting the sheet the option "allow the selection of locked cells" is set to on"

Do it this way:
1)unprotect the sheet
2)now protect it back:
-Menu ->Tools ->Protection ->Protect Sheet
-at this point the "sheet protection" window will pop-up; you can set a password on the protection (set it only if you really need it), and below you have a list of boxes with possible actions when the sheet is protected; check at least "Allow selection of blocked cells" and "Allow selection of unblocked cells".
-complete the protection with OK

Now test your macro.

Let us know.

 

[clumein2]

Thanks Anthony,

Yes, I should have said that I 'allowed the selection or locked cells', but the cursor still goes back to the wrong cell.

Cliff

 

[Anthony47]

Try using the code suggested by firefytr:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 14 Then
Application.EnableEvents = False
ActiveCell.Offset(1, -7).Range("A1").Select
End If
Application.EnableEvents = True
End Sub

1) writeclick on the sheet name tab
2) the vba edito will open and should show the current macro
3) delete everything and paste the new code.
4) move to excel and test the macro.

Let us know.

 

[Anthony47]

Pardon:
1) Rightclick on the sheet name tab
etc

Bye,