Cmd Button to populate all drop down list on a worksheet as "Yes"

Hi,

If you have simply validation maybe will be fine only this line i.e.:

 'let say your 30 cell have address as below
  Range("A1:A30").value = "Yes"

This this:

Sub CommandButton1_Click ()
Dim cntl
For Each cntl In Sheets("Sheet1").Controls
 If TypeOf cntl is MSForms.ComboBox Then
    cntl.Value = "Yes"
End If
End Sub

I use a variation of this to clear all controls on a userform, haven't tested it with just a worksheet though

Sorry, just tested and the above code won't work on a sheet. Makes sense though since there's no form

let me know if this works for you...

this code will look at the used range in your worksheet, it will then go through each cell to see if it contains the data validation (see notes within the code)
and will change it to yes

Sub settoyes()
Dim shtrow1
Dim shtrowlast
Dim shtcol1
Dim shtcollast
Dim Lrow As Variant
Dim Lcol As Variant

With ActiveSheet
shtrow1 = .UsedRange.Cells(1).Row  '<--returns the first used row
shtrowlast = .UsedRange.Rows(.UsedRange.Rows.Count).Row '<--returns the last used row

shtcol1 = .UsedRange.Cells(, 1).Column '<--returns the first used column
shtcollast = .UsedRange.Cells(.UsedRange.Columns.Count).Column '<--returns the last used column


For Lcol = shtcol1 To shtcollast Step 1 '<--tells excel to go through all used columns
For Lrow = shtrow1 To shtrowlast Step 1 '<--tells excel to go through all used rows


    With .Cells(Lrow, Lcol) '<--tells excel to go through all variations of above columns and rows

        If .Validation.Formula1 = "=Sheet3!$A$1:$A$3" Then .Value = "yes" '<--you have to change the location of the data validation formula, this is the range of the list that contains your yes, no N/A, make sure the "yes" matches the one on your list ie if you have "YES" then make sure it's exactly the same (case sensitive) 
    End With
Next Lrow
Next Lcol
End With

End Sub

Hi again

What about this (without manually enter the cell location)

 For Each cl In Cells.SpecialCells(xlCellTypeAllValidation)
    cl.Value = "Yes"
 Next cl