Determine Previous column

ErickTreetops

New Member
Joined
Jul 22, 2011
Messages
7
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Try this: right click the sheet tab, select View Code and paste in

Rich (BB code):
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,551
Messages
6,179,472
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top