Determine Previous column

[ErickTreetops]

I have a quite complex spreadsheet where users fill in one column each day.
Although the users can move up and down the column as much as they want I need to know when they try to move to a different column.

Question:
How can i detect when a users leaves a column either by keystroke or mouse button and either cancel the move or set focus to a cell back on the original column.

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

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

Change 3 to the column number they must stay in.

 

[AlphaFrog]

You could also protect your worksheet and unlock only the column of cells that you want them to fill in. Then Excel would prevent them from selecting outside that column of cells.

Protect Worksheet

 

[ErickTreetops]

I won't know which column they have just come from. There are 14 columns in all and the rule applies to all. what i was hoping for was property on the target object that was something like target.previousColumn

The selection_change event has information only on the current cell/column that you moved to and not on the cell/column that you just left

 

[ErickTreetops]

You could also protect your worksheet and unlock only the column of cells that you want them to fill in. Then Excel would prevent them from selecting outside that column of cells.

Protect Worksheet

There is no way to know when they have completed filling in a column other than that they will click/move to another column. So i won't know when to unlock the next column and lock down that one. Adding a finished button etc is not an option. Look and functionality of spreadsheet are fixed by owner.