Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nominating post entry cell (Excel 2000)

    Is there a method by which you can nominate the cell to which excel jumps, after entering data into the previous cell. I have created a work book for novice users to enter data into and I would like to guide them to the next data entry point and restrict data entry into irelevant cells.

    I have already protected the cells into which data entry is not allowed.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nominating post entry cell (Excel 2000)

    You can set the EnableSelection property of the worksheet to xlUnlockedCells. Users can only select unlocked cells then. However, this setting is not saved with the workbook, so you must set it each time the workbook is opened:

    Private Sub Workbook_Open()
    Sheet1.EnableSelection = xlUnlockedCells
    End Sub

    The user can get round this by disabling macros when opening the workbook.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nominating post entry cell (Excel 2000)

    I forgot to mention that the code in my previous reply belongs in the module for ThisWorkbook, not in a standard module.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Nominating post entry cell (Excel 2000)

    Another thing:
    TAB/shift-TAB will "jump" forward / backward through unprotected/unlocked cells when protection is enabled whether the enable selection property is set or not.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nominating post entry cell (Excel 2000)

    Thanks for the info. This should do the trick.

    Macros will need to be enabled anyway so as to do some of the back end data processing.

    The only remaining question I have is: will the macro auto run when the book is opened and will the enable selection ensure that it applies to all cells?

    Thanks again.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nominating post entry cell (Excel 2000)

    The Workbook_Open procedure in the ThisWorkbook module will run automatically when the workbook is opened with macros enabled. EnableSelection is a property of an entire worksheet, so it applies to all cells in that sheet; if you have multiple sheets, you will have to set this property for each sheet individually.

Posting Permissions

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