Data validation using VBA

Question

I am getting 'application defined or object defined object error' for the following code

10        Sheets("Main").Select
20        currYearDate = DateValue("April 1, " & Year(Now))
30        nextYearDate = DateValue("March 31, " & (Year(Now) + 1))
40        Range("H9").Activate
50        With ActiveCell.Validation
60            .Add Type:=xlValidateDate, AlertStyle:=2, Operator:=1, Formula1:=currYearDate, Formula2:=nextYearDate
70            .ErrorMessage = "You should enter date between " & Format(DateValue("April 1," & Year(Now)), "dd-mmm-yyyy") & " and " & Format(DateValue("March 31," & Year(Now) + 1), "dd-mmm-yyyy")
80            .ErrorTitle = "Warning Message"
90        End With

can anybody help me out?

Santosh · Accepted Answer · 2013-04-19 05:54:34Z

2

Try below code :

 Sheets("Main").Select
    currYearDate = DateValue("April 1, " & Year(Now))
    nextYearDate = DateValue("March 31, " & (Year(Now) + 1))
    With Range("H9").Validation
        .Delete
        .Add Type:=xlValidateDate, AlertStyle:=2, Operator:=1, Formula1:=currYearDate, Formula2:=nextYearDate
        .ErrorMessage = "You should enter date between " & Format(DateValue("April 1," & Year(Now)), "dd-mmm-yyyy") & " and " & Format(DateValue("March 31," & Year(Now) + 1), "dd-mmm-yyyy")
        .ErrorTitle = "Warning Message"
    End With

answered Apr 19, 2013 at 5:54

Santosh

12.4k4 gold badges43 silver badges74 bronze badges

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

Collectives™ on Stack Overflow

Data validation using VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related