Data Validation with VBA

Dear community members,

I have a big Excel document where I need to put the input message "Equivalence agreed. Model health attestations in Annex VII, Section 1(a) to be used. The EU may lay down its import certificates for live animals and animal products from New Zealand with a 'Yes-1' status in T" through Data Validation to every cell that contains "Yes(1)" in row C.

My code unfortunately just does this task for the first cell it encounters that contains "Yes(1)" and ignores the rest.

Sub Inputmessage()


' I need to activate all the cells and look for a special text in them


' Then I need to create a loop

Dim lrow As Long, rng As Range, cell As Range
lrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row

Set rng = Range("C10:C500" & lrow)

For Each cell In rng
If InStr(1, cell.Value, "Yes(1)", vbTextCompare) > 0 Then
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .Inputmessage = "Equivalence agreed. Model health attestations in Annex VII, Section 1(a) to be used. The EU may lay down its import certificates for live animals and animal products from New Zealand with a 'Yes-1' status in T"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
        End With
 End If
 
   
   Next cell
 

 
   
End Sub


Could you please help?
Thank you :)
Cathy ConzemiusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try
Sub Inputmessage()


' I need to activate all the cells and look for a special text in them


' Then I need to create a loop

Dim lrow As Long, rng As Range, cell As Range
lrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row

Set rng = Range("C10:C500" & lrow)

For Each cell In rng
If InStr(1, cell.Value, "Yes(1)", vbTextCompare) > 0 Then
    With cell.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .Inputmessage = "Equivalence agreed. Model health attestations in Annex VII, Section 1(a) to be used. The EU may lay down its import certificates for live animals and animal products from New Zealand with a 'Yes-1' status in T"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
        End With
 End If
 
    
   Next cell
  

 
    
End Sub

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fabrice LambertFabrice LambertCommented:
@Cathy:

Basically your code loop trough all the cells in C10:C500 range, but use the Selection objects who's value is never changed by your code.
Ehence why only one cell is checked.
Plus, since the Selection object is not tested, you have no guarantees it is a range, neither it is a single cell or within the C10:C500 range.

Side notes:
Never ever use objects such as ActiveWorkbook, ActiveWorksheet, ActiveCells, Selection, the global Sheets, Cells or Range collection, as these object value is user dependant and by nature chaotic.
As a devlopper, you don't want to use chaotic objects.
0
Cathy ConzemiusAuthor Commented:
This solved it perfectly :) Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.