1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Creating an Excel Form

Discussion in 'Business Applications' started by Redline, Oct 9, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. Redline

    Redline Thread Starter

    Joined:
    Oct 9, 2003
    Messages:
    6
    I have made tons of forms in word. I then lock them and offer then to groups to complete as part of processes.
    I now need to create a form in Excel so that I can set up links to a master file. I found the control Box and forms
    tool bars, but to us ehtem and make the form "pretty" I just can't figure out. I also want to lock the form so that
    employees can "tab" to the cells that need to be completed. Help from the Excel Assitant does not help me.

    If anyone can help, I would appreciate it.

    Thank you in advance!
     
  2. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    Hey Redline,

    Select the cells you want to have available to the others and FORMAT/CELLS/PROTECTION and uncheck locked

    Then when you finish with your sheet hit TOOLS/PROTECTION/PROTECT WORKSHEET

    Tab will then take them cell by cell to the open cells

    -C
     
  3. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    PS: What are you trying to make pretty?

    Use the format capabilities to change color - size - fonts - borders - patterns - hide cells - create a solid backgraound around your form to set it off - blah - blah - blah!



    You can make a spreadsheet EYE-POPPING!


    Just format the cells

    - C ;)
     
  4. Redline

    Redline Thread Starter

    Joined:
    Oct 9, 2003
    Messages:
    6
    Thank you for your help! When I say "Pretty" I mean that I find it hard to drag and drop a text box or Drop Down box into a cell and format it so that it looks conducive to the template. In Word, when you do this, you complete the steps by "locking" the sheet and then when folks reference it, they simply tab through the sheet. It does not appear that it works this way in Excel.
     
  5. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    If you want your textbox in a cell - make the cell a text cell.

    If you want it to be several cells - select and merge the ones you want and enter the text - and format it as you like. You can drag and drop it anywhere you like.

    Insert drop-down boxes in cells and you can drag and drop - copy and paste - them all over the place.

    Format painter will make easy work of duplicating the look of cells you want to be the same.

    and once you set up your protection on the sheet - tab will step you through the available cells one at a time

    - Castleheart ;)



    EXCEL2000
     
  6. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    A lot of good info from C-H. One thing that might be worth adding is that you can snap controls to cells by holding down ALT while you drag'n'draw them.

    On a different note, the thing that sticks out is the tabbing concept -- a non-starter for text boxes and dropdowns. It would mean quite a learning curve, but you should consider having a stab at a User Form.

    Here's a link to a fairly good primer.

    http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm

    Rgds,
    Andy
     
  7. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    Whatchew tawkin bout George!!

    If he unlocks all the pertinent cells including DropDown lists in cells and Merged cell of Text - then locks up the sheet - it will tab to each one.

    Will too !!! will too !!!!

    Now if you want it to go in an order other than descending you have to work other magic !


    Or did I missunderstand you, oh great Guru ?

    - C

    Now go back and tell me about the snap control stuff again cause you lost me there.
     
  8. Redline

    Redline Thread Starter

    Joined:
    Oct 9, 2003
    Messages:
    6
    Boys, (can I call you that?) First of all, I am a woman. My name is Joan. Second, thank you both for your assistance. It was greatly appreciated! Third of all, CH, I did as you instructed and it worked! The tabbing is all set up to my liking. Tabs work from left to right, which was fortunate for me as that is the manner in which I set up the form. It looks like a masterpiece! Thanks again for all your help and have a wonderful afternoon!

    Redline
     
  9. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Hey, Joan? Can I add? The boys here are great, yes? Yes!

    I like to do something else to my forms. Pick a nice color that will indicate that your user has not yet completed that particular part of a form. Suppose you've got a cell with no data in it yet. Click the cell, hit Format-Conditional formatting and format the pattern to be yellow or orange or something if the cell is blank. You'll figure it out. :) It's really cool and so many of my clients love that. Also, if you did some data validation boxes, I like to make my first choice in the list as "Make Selection" or "Please choose from dropdown". Then, I conditional format that if the cell ="Please choose...", it should be filled yellow.
     
  10. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> Whatchew tawkin bout George!!

    LOL! Pay attention -- I said text boxes (as in Drawing toolbar) and dropdowns (as in ActiveX controls, not data validation style).

    2ndly, try placing a text box on a sheet while holding ALT -- it'll size to fit the cell(s). Good for aesthetics.

    3rdly, I steer clear of references to gender as far as hupersonly possible.

    4th/finally, should you ever need to avoid the tab order = across then down thing. Say your input cells are B2, B4, D2, D4. Hold down CTRL, click them in this order ; B4, D2, D4, B2 (last first). Define as named range via the Name box. Unlock them & protect the sheet. Selecting the range via the Name box, then try tabbing.

    Rgds,
    Andy
     
  11. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    LOL. Who uses the control toolbox when you don't have to?
     
  12. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> LOL. Who uses the control toolbox when you don't have to?

    Err, XL5 users? Newbies?? Is it a quiz???

    Rgds,
    Andy
     
  13. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Coders! Dern coders! That's who!!
     
  14. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    Andy, thank you but I am too poor to pay attention!

    But thanks for the schooling. I try to be a good listener as much as is hupersonly possible. :D


    And to add a tidbit to Dreamboats "Make Selection" info.

    Did you know: That after setting that up you can grab that message and position it wherever you want on the sheet and it will appear there each time?

    - C
     
  15. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    No clue whatcher talkin about boy.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/170698

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice