First time poster, long time lurker! Really struggling with this and can't seem to find an answer anywhere. Hoping someone here has a solution for me.
I'm trying to set Data Validation for a cell via vba. The cell needs to exist on each row of data the rest of my code generates, so I can't hardcode the cell referencing. Also, this needs to be a dynamic validation, so I'm trying to use an indirect formula to drive things. Here's the sub I'm using:
- Sub CreateDetailDD(r As Range)
- Dim cTarget As String
- Dim FormStr As String
- cTarget = Cells(r.Row, r.Column + 3).Address(0, 0)
- FormStr = "=INDIRECT(" & cTarget & ")"
- With r.Validation
- .Add Type:=3, AlertStyle:=1, Formula1:=FormStr
- .InputTitle = ""
- .ErrorTitle = ""
- .InputMessage = ""
- .ErrorMessage = ""
- .ShowInput = True
- .ShowError = True
- End With
- End Sub
When I try to run it, I get a run-time 1004 error on the line adding Formula1 etc.
Is it possible to do what I'm trying to do, and if so, how do I do it?
Thanks in advance!