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

 

[mark hansen]

This is the code I put on the sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column > 13 Then
Cells(Target.Row + 1, 6).Select
End If
Application.EnableEvents = True

End Sub

To see how it works, I started in a1 and entered characters in each cell going across and I expected it to automatically take me back 6 columns and down one row. I expected this to happen on column "M". what happened is I just kept on going across.

I guess I'm misunderstanding something.

 

[Anthony47]

Hummm...
Possible situations:
1) the code has not been put on the sheet you are working:
-rightclick on the tab with the name of the sheet, select "Show code"; this will open the vba editor, please check if the code appears in the program area.
-if not, just paste the code in right frame of vba editor
-retest

2) "Events" are disabled: this could be done by other macros
-with the vba editor open, check in the "Project" area (the frame at the left) that below the sheet names of your workbook a "Modules" icon is present.
-if NOT, just add one: Menu ->Insert ->Module
-open Module1 and add the following code:

Sub TestEvents()
Application.EnableEvents = True
End Sub

Run this macro once: while in the vba editor, set the cursor on line 1 of the macro, press 4 times F8 (the instructions will highlight at each F8).

At this point retest your position control macro.

If it is Ok now, save your file, close, and reopen; check that position control still work; if NOT, it means that other macros are using "events" and they set they off.

Let us know.

Bye,

 

[Zack Barresse]

Try this..

Press Alt + F11
Press Ctrl + G
Type: enableevents = true
Press Enter
Type: screenupdating = true
Press Enter
Press Alt + Q

If nothing still, right click the sheet tab in question and select View Code. Put your cursor on the first line of code (where it says SelectionChange and press F9. Then press Alt + Q, then select M1. It should bring up the VBE where you can step through your code with the F8 key, as Anthony points out.

 

[mark hansen]

Thanks for the help... I brought up a clean sheet and it worked! I don't remember using any enable event code, but I guess I did.

The sheet I ended up putting it on had tables off to the right for the data validation drop down boxes, and since I need users to edit those, Eric I remembered your idea of putting a checbox to turn on and off the spell check code. I did that so you can edit the tables off to the right when you check the box... but turn it off and you are locked into the form without protection and disable locked cells to get the automatic return.

Thanks again for the troubleshooting help.