1

I have sheet1 with data,

Each field should refer the other sheet fields(Sheet2) for validation

in Sheet2, Data Validation list,

Data validation

Sub validation()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

'Set ws1 = Sheets("Sheet1")
Set ws1 = ActiveWorkbook.Worksheets(1)
Set ws2 = Sheets("Data Validation")
'Set ws3 = Sheets("V011 Record")


Dim wkst As Excel.Worksheet

ThisWorkbook.Names.Add Name:="listdata", RefersTo:= _
                       "=Data Validation!$A$1:$A$4"

For Each wkst In ThisWorkbook.Sheets

    If wkst.Name <> "Data Validation" Then
        
        With wkst.Range("A1").Validation

            .Delete
            

            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, 
Operator:= _
                 xlBetween, Formula1:="=listdata"

        End With
    End If
Next

End Sub

PLEASE NOTE: Sheet1 Name: Dynamic name Sheet2 Name: "Data Validation"

Can anyone help me pls

Community
  • 1
  • 1
  • Can you explain what is not working? – BigBen May 11 '18 at 01:54
  • @BigBen in the line .Apptype, it is throwing error as application or with object .... –  May 11 '18 at 02:00
  • You can find your answer [here](https://stackoverflow.com/questions/22956604/how-to-add-data-validation-to-a-cell-using-vba). Almost an exact duplicate of this question, even down to your use of `xlBetween`, which you don't need for a list :-) – BigBen May 11 '18 at 02:23
  • @BigBen the reference you shared is that they worked around single sheet, in my case it is two sheets to play –  May 11 '18 at 02:29
  • 1
    Nice try! Your problem isn't with 2 sheets, it's with your `Formula1` reference. You need to reference the `Data Validation` sheet, as well as the `listdata` `Address`. Or just use `Formula1:="='Data Validation'!$A$1:$A$4"` – BigBen May 11 '18 at 02:33
  • @BigBen Perfect!!! But in this, wt about the other field validations... B1:B7, C1:C5 ...so on...I hope we can use increment method to check...Can you please help me on this –  May 11 '18 at 02:41
  • You already have a `With... End With` section for Column A - you can use that same structure for each remaining Column. – BigBen May 11 '18 at 02:48
  • In my example, in the sheet2 Data Validation, we have shared $A$1:$A$4" in the code...what about if there is many list to validations??, How could I change the lastrow to get automatically –  May 11 '18 at 02:51
  • Google "vba get last row" - thousands of people have had this problem before you :-). You need to generalize your code - i.e. you need to loop through each column, finding the last row each time, and then adding validation to your other sheet similarly to how you are doing currently. – BigBen May 11 '18 at 03:01
  • Thanks @BigBen One last question, I would like to use if condition to check whether the value(in the "A1") in sheet1 should be in the range (A1:A4). Can you help me pls –  May 11 '18 at 03:04
  • How to change this Formula1:="='Data Validation'!$A$1:$A$4" into incremental way? –  May 11 '18 at 03:06
  • Check out the question I mentioned earlier. Use a `Range` to refer to the list in each column. – BigBen May 11 '18 at 03:16
  • Possible duplicate of [How to add data validation to a cell using VBA](https://stackoverflow.com/questions/22956604/how-to-add-data-validation-to-a-cell-using-vba) – ashleedawg Jul 31 '18 at 11:31

0 Answers0