Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Data Validation Alternatives? (Excel 2002)

    Hi,
    I've been using data validation lists to control what people enter into a worksheet for a number of years and the draw back to data validation is that the "list" must be on the same worksheet. This causes a problem if I allow people to insert/delete lines, etc... inevitably part of the "list" gets screwed up, and then it causes a problem. Of course I've used the "protection" options but I'm ready to move on to another option. Is there another option available that would work... I've looked at the combo boxes, however it doesn't seem to be what I'm looking for. I want to have defined and required fields to control what is entered... I've seen special displays that have "boxes" that you can select the data you want to enter into the boxes, however I'm thinking this was some kind of a template in Access... is there something similar to this in Excel? An example I can give, is this very post... it asks me to enter something in the "Subject" box (however it is not defined with a list behind it), and it asks me to enter the "Version/Service Release"... etc. I'm thinking I want to make some kind of a form, as the data validation limits me. Any ideas would be greatly appreciated!
    Thanks!
    Lana

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

    Re: Data Validation Alternatives? (Excel 2002)

    The source list for validation doesn't need to be on the same sheet. You can use a named range that can be located on another sheet:
    - Select the source list.
    - Click in the cell address box on the left hand side of the formula bar.
    - Type a name, for example MyList.
    - Switch to the sheet where you want to use the list.
    - Select one or more cells.
    - Select Data | Validation...
    - Select List from the Allow dropdown.
    - Enter = followed by the name you defined, for example =MyList
    - If you want, activate the other tabs and specify an input message and/or error message.
    - Click OK.

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

    Re: Data Validation Alternatives? (Excel 2002)

    Other possibilities:
    - If you want the user enter data in a database-like table, where rows are "records" and columns are "fields", you can use Data | Form...
    - If you want to restrict the range of cells where users can enter data, but you don't want to restrict what they can enter, you can unlock the cells in that range (in the Protection tab of Format | Cells...), then protect the sheet (in Tools | Protect Sheet...)
    - If you want to design a data entry form yourself, you can create a userform in the Visual Basic Editor. Text boxes etc. on a userform can be bound to a cell on a worksheet.

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Data Validation Alternatives? (Excel 2002)

    Okay... I learn something new everyday... I was told years ago it had to be on the same worksheet and I always thought that it was odd! Thanks for setting me straight... this helps out tremendously... I just got your second response... you read my mind, as I was still interested in learning the form thingy in Visual Basic. I've just found it 10 minutes ago and I was playing around with it... how does the form get from the Visual Basic into Excel for the people to use??
    Thanks Hans!!
    Lana

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

    Re: Data Validation Alternatives? (Excel 2002)

    Say that you create a userform named UserForm1 - that is the default name of the first userform you create in a workbook; you can change its name in the (Name) box in the Properties pane.
    To display the form, you create a macro in a standard module, for example

    Sub ShowForm()
    UserForm1.Show
    End Sub

    You can call this macro from a command button on the worksheet, or you can assign it to a custom toolbar button and/or keyboard shortcut.

    If you want to display the form automatically when the workbook is opened, you can call the macro from the Workbook_Open event procedure in the ThisWorkbook module:

    Private Sub Workbook_Open()
    Call ShowForm
    End Sub

Posting Permissions

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