Is there a way to make the cursor move ...

Senji

New Member
Joined
Mar 22, 2002
Messages
32
Example:

User loads up spreadsheet, starts at A1, hits enter, moves to next cell data is expected at, B11, types and hits enter and cursor moves to C15

is that possible?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
You can (sort of) do this via the following-

1 Select all the cells you want the user to be able to enter data into
2 Click on Format-Cells-Protection and uncheck the locked box for those cells
3 Click on Tools-Protection-Protect sheet (enter a password if you want)

You can then press Tab (not enter) to move to those cells that are unprotected.
 
Upvote 0
Actually, if you do the above, then right-click on the sheet tab, select View Code and paste in the below, that should do the trick: -

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False
SendKeys "{TAB}", True
Application.EnableEvents = True

End Sub
 
Upvote 0
Hey Mudface ? why SelectionChange ? I think you were shooting at Worksheet_Change() alone, that is, when the user presses ENTER, he's taken to the next unlocked cell ?
 
Upvote 0
Hi, Juan. I used the Selection_Change event rather than the Change event as, if the user doesn't modify a cell and just presses Enter, they'll just go to the next cell down, rather than the next usable cell.
 
Upvote 0
Hi Senji,

Mudface has posted a nice option, and an event macro would also work.

A third option...

Select your three cells A1, B11, C15 by holding the Ctrl key when you click on each cell separately.

With the three selected, in the Name box, name the range. "DataEntry" for example.

In a workbook_open event, you can use something like:
Range("DataEntry").Select

You can always reselect the range from the from the Name box as well.

You can now <tab> or <enter> between the three cells.

Bye,
Jay
 
Upvote 0
Nice one, Jay, but I don't think this will work if the user presses the cursor keys to move between cells.
 
Upvote 0
There's also a property of the worksheet that allows the user to ONLY select UNLOCKED cells. It's called EnableSelection, and by defaults, its value is NoRestrictions if i remember correctly ?
 
Upvote 0
* A quick trip to VB help later :) *. Yep, spot on Juan. The following would seem to work OK: -

Private Sub Workbook_Open()

Dim ws As Worksheet
For Each ws In Worksheets
ws.EnableSelection = xlUnlockedCells
Next ws

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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