Solved: Apply Data Validation to a Named Range

[JimS]

Is it possible to apply Data Validation to a Named Range? This would allow me to only apply the Data Validation to the cells in Column-B if there was data in the corrisponding Column-A cell.

For example, only apply a Data Vaildation Rule (choice list) to a cell in Column-B if Column-A is populated. If cell A3 is blank then no Data Validation is applied to cell B3.

Is would avoid having to copy the data valiadtion (format) down the entire B column.

Thanks for any help...

JimS

[p45cal]

You may have to tweak the xlSpecialCells to cater for the type of data you have in column B:Sub blah()
With Range("B2:B100").Offset(, -1).SpecialCells(xlCellTypeConstants, 23).Offset(, 1)
.Validation.Delete
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$E$28:$E$32"
End With
End Sub


edit post posting.; Oops. Didn't read the question:Sub blah2()
With Range("myNamedRng")
.Validation.Delete
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$E$28:$E$32"
End With
End Sub

[JimS]

p45cal,

Perfect solution - as always, Thanks...

JimS

[RatherBeRidi]

I have a related question. Can you please provide guidance how I can apply validation to a range (actually 6 of them) for a data entry form created in VB? My form has 43 fields, most of them are comboboxes and a few textboxes. Using Private Sub UserForm_Initialize(), each comboboxes is restricted to a range. A command button using Private Sub cmdAdd_Click() copies the data to the spreadsheet.

I've set the comboboxes properties listed below and only the options listed in each range will display. However, if a range is 1 through 4, and a 9 is entered, number 1 displays. I need to have a message display to notify the user of an incorrect entry. Hope this is possible! Thanks for your help.

Style = fmStyleDropDownList
MatchEntry = fmMatchEntryComplete
MatchRequired = True

Private Sub UserForm_Initialize()
Dim i As Long
Dim txt As TextBox
Dim cbo As ComboBox
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")
For i = 1 To Range("Age").Rows.Count
Me.cboAge.AddItem Range("Age")(i)
Next i
For i = 1 To Range("Rating").Rows.Count
Me.cboA1.AddItem Range("Rating")(i)
Next i
etc . . (40+ entries)
End Sub

[p45cal]

Change the MatchRequired property of the combboxes to True.

[RatherBeRidi]

I do have it set to True. Only the items included in the range can display, BUT if I press any key that is not in the range, the first item in the range populated the combobox without an error message.

[p45cal]

I replied too quickly without reading fully your original post.
Try changing the Style to DropDownCombo..