CODEWITHSUNDEEP
vbaexcel
james9487
james9487

Reputation: 13

Setting cell data validation as list from a variable list

Am quite new to VBA and am piecing together my developments one step at at time searching for answers online but have hit a snag.

I'm struggling to understand how the creation of the data validation list code works. The beginning "With WrkBook.Range("H3").Validation" is where I'm putting the drop down list? Seems 'yes' from one posting, but another seemed to have the "With" followed by the actual range which contains the list. Secondly, the "Formula1:=..." I believe is also the location of the list. However, I seem to be only able to include the first cell in my list as being included in the list. Thanks in advance for any help.

Dim WrkBook As Worksheet
Dim LastCellRowNumber As Integer
Dim ListRng As Range
Dim Rng As Range

Set WrkBook = Worksheets("Misc Ref")

'Find
WrkBook.Activate
Range("A100000").Select
Range(Selection, Selection.End(xlUp)).Select

LastCellRowNumber = ActiveCell.Row

ActiveSheet.Cells(LastCellRowNumber, 1).Select

    Set ListRng = WrkBook.Range(Cells(2, 1), Cells(LastCellRowNumber, 1))

    With WrkBook.Range("H3").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=WrkBook.ListRng
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Upvotes: 1

Views: 2299

Answers (1)

L42
L42

Reputation: 19727

In this line:

 Set ListRng = WrkBook.Range(Cells(2, 1), Cells(LastCellRowNumber, 1))

you already set or assign a Range Object referenced at WrkBook sheet object.
So you don't have to use WrkBook.ListRng in assigning the Formula1 argument.
Also, Formula1 argument is suppose to be the address of the source range in the form of string.

So it should be something like:

Formula1:= "=" & ListRng.Address

This will fail though if the worksheet your putting the validation list is not the same worksheet of the source list. So you might want to add:

Formula1:= "=" & ListRng.Address(, , xlA1, True)

That will give you the Sheet name as well. HTH.

Edit2: Based on comments and no need to use Split function.

Sub test()
    Dim r As Range, lrow As Long
    With Sheets("Misc Ref")
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        Set r = .Range("A2:A" & lrow)
    End With
    'Debug.Print "=" & r.Address(, , xlA1, True)
    With Sheets("Summary").Range("H10").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & r.Address(, , xlA1, True)
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Setting the Address property External argument to True includes the full path.
But if you assign it to a validation formula, it automatically disregards the Workbook name.
So there is no need to use Split Function. No need to worry the apostrophe then. HTH.

Upvotes: 2

Related Questions