CODEWITHSUNDEEP
vbaexcel
GohanP
GohanP

Reputation: 549

Dynamic validation list - data from other worksheet

I have one worksheet where in a specified range should be validation list. This list is token from another worksheet (column A, from row 2 to last not-empty row). But it doesn't work and I can't figured why.

My code:

Set wsResourcesProjects = Sheets("ResourcesProjects")
Set wsProjects = Sheets("Projects")


With wsProjects.rGeneralFTERange.Offset(0, 3).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=ResourcesProjects!" & wsResourcesProjects.Cells(1, 1).CurrentRegion.Offset(1, 0).Address
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowInput = True
    .ShowError = True
End With

And in range rGeneralFTERange validation list is not appear. But when I change this part:

wsResourcesProjects.Cells(1, 1).CurrentRegion.Offset(1, 0).Address

on 

"A2:A10"

then it's works. But it's not good for me, because data in column "A" are dynamic.

Upvotes: 0

Views: 113

Answers (1)

Linga
Linga

Reputation: 955

In your code Formula1:="=ResourcesProjects!" exclimation ! symbol might have created the problem, hope you have already created the named ranges :)

FYR named ranges 

Sub validation()
    'Select your range
    Range("A1").Select
    With Selection.validation
        .Delete
        'Month_Val is the namedrange name
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Month_Val"
       
    End With
End Sub

Upvotes: 1

Related Questions