VBA Data validation list not working (Formula1:=)

Question

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

I **think** it is meant to be `Formula1:="=Sheet7!" & Sheets("Sheet7").Range(Sheets("Sheet7").Cells(counter, 1), Sheets("Sheet7").Cells(counter, lcol)).Address` — YowE3K, Nov 16 '17 at 19:04
@YowE3K yes. Implicit `ActiveSheet` references strike yet again. — Mathieu Guindon, Nov 16 '17 at 19:05
@Mat'sMug More than that - it's attempting to use a range, instead of a formula or an address — YowE3K, Nov 16 '17 at 19:07

Mathieu Guindon · Answer 1 · 2017-11-16T19:39:59.433

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.

I totally understand the point about the active sheets, makes perfect sence not to use them and to use object references instead. However, the above code still does not work, "438: Object doesn't support this property or method". is this because of "Formula1:="? I read that it can be a tricky thing to work with. — Jacob Rusoff, Nov 16 '17 at 19:34
@JacobRusoff edited to clarify. That wasn't meant to be copy/pasted as-is, it was meant to be read & understood... — Mathieu Guindon, Nov 16 '17 at 19:40

score 0 · Answer 2 · answered Nov 16 '17 at 19:59

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

VBA Data validation list not working (Formula1:=)

2 Answers2