Data Validation Alternatives? (Excel 2002)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote