Excel VBA Validation List Error

Question

I went throught various threads and got several tips on Validation. But still my code is throwing me an error. It could be a silly error but i couldn;t spot it.

        Set val1range = projInfo.Range(projInfo.Cells(2, 23), projInfo.Cells(m, 23))     

        With Cells(rowN, 3).Validation
                .Delete
                .Add Type:=xlValidateList, Formula1:="=" & val1range

                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
        End With

I am getting a 1004 error on the line .Add Application-defined or object-defined error.

Could youplease let me know if i am giving the correct formula?

I think it could be due to the fact that you .Delete the row and then reference it straight after. Try calling the Delete Method outside the With Block — hammus
– hammus, Commented Nov 5, 2013 at 17:10

Jean-François Corbett · Accepted Answer · 2013-11-05 08:13:41Z

2

Not sure if this is the only error, since the code you posted doesn't define projInfo, m, or rowN, but your Add method is attempting concatenate a string, "=", and a range object, val1Range. The default property of Range objects is Value, so in effect you're concatenating "=" with the value or content of the cell instead of its address (which is presumably what you want).

To fix, change this line:

.Add Type:=xlValidateList, Formula1:="=" & val1range

to this:

.Add Type:=xlValidateList, Formula1:="=" & val1range.Address

edited Nov 5, 2013 at 8:13

Jean-François Corbett

38.7k30 gold badges144 silver badges192 bronze badges

answered Nov 4, 2013 at 22:36

mr.Reband

2,4312 gold badges20 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siva Over a year ago

the projinfo, m rowN are all defined in previous lines and they all hold vaild values. Just i didn't show them here.. and i am sorry about that. I gave the fix you suggested. Still i am getting the same error.

Collectives™ on Stack Overflow

Excel VBA Validation List Error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related