CODEWITHSUNDEEP
vbaexcelvalidation
Jacob Rusoff
Jacob Rusoff

Reputation: 1

VBA Data validation list not working (Formula1:=)

The following code is a loop that takes the value of a cell (string), and inserts it into another sheet. it then separates the values of the string by a comma delimiter. It all works fine until I try to create a list data validation, where the Formula1:= does not seem to be working properly, however the code looks fine.

It is supposed to copy the current row of the loop all the way to the last column and create a data validation list out of it.

Please help, what am I doing wrong?

Sub dataVal()

    Dim lrow As Long
    Dim lcol As Long
    Dim i As Long
    Dim counter As Integer
    counter = 1
    lrow = Sheets("LVL & Mapping").Cells(Sheets("LVL & Mapping").Rows.count, "H").End(xlUp).Row
    lcol = Sheets("Sheet7").Cells(counter, Columns.count).End(xlToLeft).Column

    For i = 4 To lrow
      Range("I" & i).Select
      Selection.Copy
      Sheets("Sheet7").Select
      Range("A" & counter).Select
      ActiveSheet.Paste
      Application.CutCopyMode = False
      Selection.TextToColumns Destination:=Range("A" & counter), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
        TrailingMinusNumbers:=True
      Sheets("LVL & Mapping").Select
      Range("J" & i).Select
        With Selection.Validation
           .Delete
           .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Sheets("Sheet7").Range(Cells(counter, 1), Cells(counter, lcol))
           .IgnoreBlank = True
           .InCellDropdown = True
           .InputTitle = ""
           .ErrorTitle = ""
           .InputMessage = ""
           .ErrorMessage = ""
           .ShowInput = True
           .ShowError = True
        End With
      counter = counter + 1
    Next i
End Sub

Upvotes: 0

Views: 4474

Answers (2)

mooseman
mooseman

Reputation: 2017

Based on how your code should work, this sub should do what you want. I just don't understand the last column on Sheet7 (since I do not have your workbook) and why you were trying to do the data validation on each cell in column J, one at a time. DataValidation lists need to be one column of data or a delimited list, thus I went with column A for Sheet7.

Sub dataVal()

    Dim lrow As Long
    Dim i As Long

    Set shta = Sheets("LVL & Mapping")
    Set shtb = Sheets("Sheet7")
    lrow = Sheets("LVL & Mapping").Cells(Sheets("LVL & Mapping").Rows.Count, "H").End(xlUp).Row


      shta.Range("I4:I" & lrow).Copy shtb.Range("A1")

      Set objRange = shtb.Range("A1").EntireColumn

      objRange.TextToColumns Destination:=objRange, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Comma:=True, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))



      With shta.Range("J4:J" & lrow).Validation
           .Delete
           .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Sheet1!" & shtb.Range("A1:A" & lrow).address
      End With


End Sub

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Formula1:=Sheets("Sheet7").Range(Cells(counter, 1), Cells(counter, lcol))

Classic mistake: unqualified Cells member call is implicitly referring to whatever the ActiveSheet is; if that isn't Sheet7, the validation is silently throwing an internal error and you don't get your list.

Range, Cells, Columns, Rows, and Names should always be explicitly qualified with a proper Worksheet instance.

That's what you did here:

Sheets("Sheet7").Range(...)

This Range member belongs to whatever worksheet the Sheets call (which is itself implicitly working off whatever the ActiveWorkbook is) is returning.

The macro recorder generates clumsy code that mimicks user interactions. It's good for exploring the object model and learn what members to use to do what, but it's inherently frail and bug-prone.

Don't use .Select and .Activate; work off object references instead. See this post for more info.

Dim validationSource As worksheet
Set validationSource = ThisWorkbook.Worksheets("Sheet7")

...

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=" & validationSource.Range(validationSource.Cells(counter, 1), validationSource.Cells(counter, lcol)).Address
    '...

Give it the .Address of the range, not the range itself. And since this is a formula, prefix with an = sign.

Upvotes: 3

Related Questions