PDA

View Full Version : Solved: Add data validation from selection



snidera
09-15-2006, 12:49 PM
I have a Part Number colume that i want to use as a selectable list, but it will change based on the model number.


For j =1 to ubound(models())
If Cells(i, 6) = models(j) Then
With Cells(i, 7).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:='ModelName(j)'
.InCellDropdown = True
.IgnoreBlank = True
End With
End If
Next j


-I have dynamic named ranges for each list of part numbers and the names are in another array of the same length- ModelName() (String format ModelName_Model#)
-Currently both Model() and ModelName() are sized 1 to 4 if that helps simulate
-The user will enter the partnumbers with a name and model# for the heading the array models() is created from that).
-The named range for each part number is ModelName_model#

Is there any way to insert the named range into Formula1 from my code above? I'm currently using If/ElseIf statements to do this, but if a user inputs more/different models, i have to modify the code

Bob Phillips
09-15-2006, 01:09 PM
For j = 1 To UBound(models)
If Cells(i, 6) = models(j) Then
With Cells(i, 7).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=" & models(j)
.InCellDropdown = True
End With
End If
Next j

snidera
09-15-2006, 01:27 PM
I feel dumb - I put quotes on both sides and couldnt figure out why it didnt work.

Thank you