Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skip Column when using Tab or arrow
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skip Column when using Tab or arrow
Lee - many,many thanks!! It works a treat. Eggs-xactle what i needed.
again, thank you! (and it doesn't look a bit like the macro i recorded) Lee wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skip Column when using Tab or arrow
I have found the following line of code quite helpful in a situation like yours
where you are using a worksheet as a kind of "data entry form" with entry cells in various locations as opposed to in a single column: ActiveSheet.EnableSelection = xlUnlockedCells It requires all cells to be locked except the "Entry cells" AND the sheet must be protected. HTH "Hugh" not.com" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skip Column when using Tab or arrow
Well, howdy-do. that works even more better! extreeeeeemly elegant.
Thank You! gocush wrote: I have found the following line of code quite helpful in a situation like yours where you are using a worksheet as a kind of "data entry form" with entry cells in various locations as opposed to in a single column: ActiveSheet.EnableSelection = xlUnlockedCells It requires all cells to be locked except the "Entry cells" AND the sheet must be protected. HTH "Hugh" not.com" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to skip cells in column for charting | Charts and Charting in Excel | |||
I need a formula to calculate 2-3 columns but skip a column if it has a zero | New Users to Excel | |||
Is it possible to set the enter and arrow keys to skip locked cel | Excel Discussion (Misc queries) | |||
I sort a column, how can I have all the data skip every other row | Excel Worksheet Functions | |||
Average Column, but Skip Null and 0? | Excel Worksheet Functions |