Dropdowns using VBA

[gauti]

I am trying to insert dropdowns in sheet1 based on data in sheet2.
This works part of the code works:
Sheets("Sheet1").Activate

Range("B7").Select
x = Sheets("Sheet2").Range("D500").End(xlUp).Row
With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=DROPDOWNS!$D$2:$D$" & x
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
End With

However,this part of the code doesnt and it is exactly the same except for the column selection: I keep getting the Application defined or object defined error

Range("B20").Select
x = Sheets("Sheet2").Range("A500").End(xlUp).Row
With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Sheet2!$A$2:$A$" & x
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
End With

I checked and i realized this could be because the column A is populated with hyperlinks. Could this be a reason? If yes, any workaround? i dont need the url in the dropdown,just the text that i used to display the hyperlink.

 

[gtwo]

Hey there. Well, I've tested your code, even with hyperlinks, and it worked ok. Your problem might be in your data, sheet names, something like that.
Check the image to see if your are doing something else wrong. There are three screen shots:
1 - Your data on column A, in Sheet2
2 - Your code(I didn't change anything)
3 - The result in cell B20, what worked ok even with hyperlink

Let me know if you have found the issue, or if ou need further assistance.

 

[gauti]

Hi,

Thanks for the response, i just tried again and i got the same issue...double checked the sheet names and everything...i just manually selected the data and used data validation tab to populate the dropdown but it would have been good to do it using code.

 

[gtwo]

If you just want an "automatic updating list", in your case, you can use a formula instead of a vba code.

Here is the formula to put in data validation list:

=OFFSET(Sheet2!$A$2;0;0;COUNTA(Sheet2!$A:$A)-1;1)

Using that, if you add some data to column A on sheet2, it will be automatically added to your drop down list.

Check the uploaded image to see if you can understand it, and if that fits your needs.