Skip Column when using Tab or arrow
Hugh,
The code uses the SelectionChange event to trap any attempt (by mouse or by
keyboard) to move to column C, and forces the selection to B or D, depending
on what cell the user came from.
If the user selects a range of cells that includes column C, the code does
nothing, and actually loses track of LastCell. Oh well.
I recommend you still protect Column C
----------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static LastCell As Range
If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
' User selected a single cell.
If Target.Column = 3 Then
If LastCell Is Nothing Then Set LastCell = Cells(1, 1)
' User tried selecting column C.
' Move them to either column B or D
If LastCell.Column 3 Then
' User came from the right
' Move them to B
Cells(Target.row, 2).Activate
Else
' Else, move them to D
Cells(Target.row, 4).Activate
End If
Else
Set LastCell = Target
End If
Else
' User selected a range of cells
' Take no action.
End If
End Sub
----------------------------------------------------------------------
HTH...Lee
"Hugh" not.com" wrote in message
...
Hope this makes sense
User enters number in B3. Formula in C3 then shows "x". User then
Tabs or uses right arrow to go to D3.
Is there a way to arrow or Tab directly from B3 to D3 so that data
intended for D3 does not get inadvertently entered in C3?
Validation &/Protection keeps out entries, but involves the hassle of
clicking out of the error message.
This is a worksheet w/large amounts of entry. It is difficult to keep
track of where you are on the sheet because you are following the
information that is on your desk, instead of on the screen.
Tried recording a macro that hides col C when a cell is selected in col
B, but cant figure out how to make it work automatically.
Any help gratefully accepted
|