Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

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

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 or between the three cells.

    Bye,
    Jay


  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nice one, Jay, but I don't think this will work if the user presses the cursor keys to move between cells.

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    * 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

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Download my file
    resultsheet from my website

    http://www.pexcel.com/download.htm

    file nos is 9

    i hope this is what you are asking.

    ni****h desai

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •