PDA

View Full Version : Items in a Validation list



Aussiebear
09-22-2006, 04:01 PM
Is there any limits to the number of items you can hold in a valiadation list?

For example if you were to have say 1000 names in a client list, would this be impractical to use as a validation list or could it be broken down in some method so that if you were to start a name, a validation list then only contains those names who start with the letters you are chasing?

Or on the other hand, something similar to that which Microsoft uses with an autocomplete function based however not on the activesheet you are using but from a master list elsewhere in the workbook?

Ted

johnske
09-22-2006, 05:11 PM
Is there any limits to the number of items you can hold in a valiadation list?

For example if you were to have say 1000 names in a client list, would this be impractical to use as a validation list or could it be broken down in some method so that if you were to start a name, a validation list then only contains those names who start with the letters you are chasing?

Or on the other hand, something similar to that which Microsoft uses with an autocomplete function based however not on the activesheet you are using but from a master list elsewhere in the workbook?

TedNo limitations that I'm aware of, try this example with 10,000 entries
Option Explicit
Sub NumberRowsAndCreateList()
Application.ScreenUpdating = False
[A:A].ClearContents
With [A1:A10000]
.Formula = "=ROW(B1)"
.Value = .Value
End With
With [D1:D10].Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=$A:$A"
.IgnoreBlank = True
.InCellDropdown = True
.ErrorTitle = "ERROR"
.InputMessage = "Select from list"
.ErrorMessage = "Please select from dropdown list only"
.ShowInput = True
.ShowError = True
End With
ActiveWorkbook.Save
[D1].Select
Application.ScreenUpdating = True
End Sub
You can use a named range to put the list on another sheet and you could use a Worksheet_Change event to complete other details pertaining to the entry made from the list if you wish :)

Aussiebear
09-22-2006, 05:20 PM
Hmmm... any chance you could tell me just what this is doing ( Section by section) please?

johnske
09-22-2006, 05:28 PM
[A:A].ClearContents
With [A1:A10000]
.Formula = "=ROW(B1)"
.Value = .Value
End Withclears any previous values in column A then puts consecutive numbers in the first 10,000 cells in column A (this is merely an example for your data list)


With [D1:D10].Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=$A:$A"
.IgnoreBlank = True
.InCellDropdown = True
.ErrorTitle = "ERROR"
.InputMessage = "Select from list"
.ErrorMessage = "Please select from dropdown list only"
.ShowInput = True
.ShowError = True
End Withinserts a data validation list in the 1st 10 cells in column D (again, as an example)

Aussiebear
09-22-2006, 06:31 PM
Thank you