Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Making a spreadsheet into a form (2k7)

    Hiyas. I'm looking to make a spreadsheet into a controlled form where the user can just tab from one input cell to the next while skipping cells that are essentially labels that shouldn't be edited. I imagine there's some VBA involved but I don't know where to start... can anyone point me in the right direction?

    I tried searching for what I want to do but I didn't turn up much. I can't imagine this topic hasn't been covered though so I probably just don't know what to search for.

    Thanks!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Making a spreadsheet into a form (2k7)

    The description below is for Excel 2003 and below. You'll have to find out yourself where Microsoft has hidden these commands in the Ribbon. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    1) Select the cells that the user should be able to edit.
    Select Format | Cells...
    Activate the Security tab.
    Clear the 'Locked' check box.
    Click OK.

    2) Select Tools | Protection | Protect Sheet...
    Make sure that only the check box for 'Select unlocked cells' is ticked.
    Specify a password if desired (if so, you'll be asked to confirm it)
    Click OK.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Making a spreadsheet into a form (2k7)

    Yes, you could get very fancy, but have you looked at locking all cells except those you want user input to, and then protecting the spreadsheet so that only UNlocked cells can be selected? With a little creativity you can have an entry sheet, and all calculations on a separate hidden sheet. The hidden sheet can be made "undiscoverable" by using the one-time VBA command Application.ActiveSheet.Visible = xlSheetVeryHidden.

    (Sheet protection in Excel prior to 2007 was not very robust, but it will defeat most ordinary users. Don't know if this was improved in 2007.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a spreadsheet into a form (2k7)

    well that was much easier than I thought it would be... thanks!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a spreadsheet into a form (2k7)

    less important than what i asked previously, which is working nicely - is there a way to control where the tab button takes you? I'd like to make tabbing more intuitive...
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Making a spreadsheet into a form (2k7)

    In a protected sheet, the Tab key takes you to the next unprotected cell, looking first across then down. It's a bit tricky to change that.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a spreadsheet into a form (2k7)

    I was thinking that you might be able to read in the keypress of tab and do a select case for cells whose tab direction I want controlled. The majority of cells are OK, there are only about 20-30 that I want to direct to a specific cell because they go down, not across. I would also put a similar case construct for shift+tab to go backwards... what do you think?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Making a spreadsheet into a form (2k7)

    You'd need VBA code for this, and it would disable undo.

    If you really need this, I'd either adapt the layout of the worksheet so that Tab takes the user to the appropriate cell automatically, or create a userform in the Visual Basic Editor.

  9. #9
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a spreadsheet into a form (2k7)

    yeah, that is way too much work. I'll just hope they figure out that enter goes down, and tab goes across! for everything else, locking is good enough.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Making a spreadsheet into a form (2k7)

    I usually just move the entry cells around to the order I think user should tab them. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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