VBA to create dropdown list in a cell with formula = named range that is in another cell

[ShoYnn]

Kind of a long title, but as it says I am trying to add a data validation list to cell D5 with the formula referencing a named range that is typed into cell C5. This is what I have

Range("D5").Select
With Selection.Validation
.Add Type:=xlValidatList, Formula1:=Range("C5").Value

And I keep getting Application-defined or object-defined error with that line highlighted. I have tried dimmimg rng as a string and having that be the C5 value then having the formula:=rng with the same results. How can I fix this?

 

[GWteB]

How about ...

Range("D5").Validation.Add Type:=xlValidateList, Formula1:="=indirect(c5)"

 

[ShoYnn]

How about ...

Range("D5").Validation.Add Type:=xlValidateList, Formula1:="=indirect(c5)"

Lol, so that didn't work, I even just tried typing the indirect formula in the data validation myself and it said it calculated to an error, but I did fix it! Turns out I had my sheet lock itself earlier in the code and it didn't like doing that function as it would change the value on a locked sheet, which is silly since the cell itself is not locked! But anyway, it is fixed, tested, and working. I managed to get it to work with the code in my original post after ensuring it happens while my sheet is unlocked. Thanks for responding though!

 

[GWteB]

You are welcome and thanks for the feedback.