Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cells for validation list
Validation is associated with individual cells. Do you want the IF to detect
if ANY cell in the range has validation or if ALL the cells in the range have validation applied? -- Gary''s Student - gsnu200796 "Sandy" wrote: Tried to post this earlier but it didn't seem to arrive?? How do I check for validation list in a range Roughly:- If Range("C30:K30,C36:K36") has validation then *****Code****** Else *****Code****** End If Thanks Sandy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cells for validation list
Detecting if any in a range would be good
sandy "Gary''s Student" wrote in message ... Validation is associated with individual cells. Do you want the IF to detect if ANY cell in the range has validation or if ALL the cells in the range have validation applied? -- Gary''s Student - gsnu200796 "Sandy" wrote: Tried to post this earlier but it didn't seem to arrive?? How do I check for validation list in a range Roughly:- If Range("C30:K30,C36:K36") has validation then *****Code****** Else *****Code****** End If Thanks Sandy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cells for validation list
Sub sandy()
' gsnuxx x = 9999 Set r = Range("C30:K30", "C36:K36") For Each rr In r On Error Resume Next x = rr.Validation.Type If x < 9999 Then MsgBox ("validation found in: " & rr.Address) Exit Sub End If Next MsgBox ("No validation found") End Sub This routine examines the range. If no validation is found, a message is issued. If validation is found, the address of the first cell with validation is output in a message. -- Gary''s Student - gsnu200796 "Sandy" wrote: Detecting if any in a range would be good sandy "Gary''s Student" wrote in message ... Validation is associated with individual cells. Do you want the IF to detect if ANY cell in the range has validation or if ALL the cells in the range have validation applied? -- Gary''s Student - gsnu200796 "Sandy" wrote: Tried to post this earlier but it didn't seem to arrive?? How do I check for validation list in a range Roughly:- If Range("C30:K30,C36:K36") has validation then *****Code****** Else *****Code****** End If Thanks Sandy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cells for validation list
You are welcome.
Another approach is to create a range of validation cells using SpecialCells and see if it intersects with the range of interest. -- Gary''s Student - gsnu200796 "Sandy" wrote: Gary''s Student Thank you very much Sandy "Gary''s Student" wrote in message ... Sub sandy() ' gsnuxx x = 9999 Set r = Range("C30:K30", "C36:K36") For Each rr In r On Error Resume Next x = rr.Validation.Type If x < 9999 Then MsgBox ("validation found in: " & rr.Address) Exit Sub End If Next MsgBox ("No validation found") End Sub This routine examines the range. If no validation is found, a message is issued. If validation is found, the address of the first cell with validation is output in a message. -- Gary''s Student - gsnu200796 "Sandy" wrote: Detecting if any in a range would be good sandy "Gary''s Student" wrote in message ... Validation is associated with individual cells. Do you want the IF to detect if ANY cell in the range has validation or if ALL the cells in the range have validation applied? -- Gary''s Student - gsnu200796 "Sandy" wrote: Tried to post this earlier but it didn't seem to arrive?? How do I check for validation list in a range Roughly:- If Range("C30:K30,C36:K36") has validation then *****Code****** Else *****Code****** End If Thanks Sandy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reset Validation cells to first list value | Excel Discussion (Misc queries) | |||
Protect List Validation Cells | Excel Discussion (Misc queries) | |||
Remove empty cells from named list / validation list | Excel Worksheet Functions | |||
Empty Cells in validation List | Excel Worksheet Functions | |||
how to list all validation cells on a sheet | Excel Programming |