Date validation error - Unable to set the NumberFormat property of the Range class

[willow1985]

Run-time error 1004:
Unable to set the NumberFormat property of the Range class

I have the below code to prompt if an invalid date is entered. It also is supposed to prompt the user which format to enter, the users only understand one format so at the end I wanted the code to format it to the date format my boss prefers.

Anyway long story short I am getting the above error on the last line of the code (the formatting portion). Is there a way to fix this?

Thank you

Option Explicit



Private Sub Worksheet_Change(ByVal Target As Range)


    Dim rng As Range
    
    Set rng = Range("B2")
    
    If Intersect(Target, rng) Is Nothing Then
        Exit Sub
    Else
        Call ValidateDate(rng)
    End If


End Sub




Private Sub ValidateDate(r As Range)


    Dim c As Range


    For Each c In r
        If c.Value <> "" And Not IsDate(c) Then
            Application.EnableEvents = False
            c.ClearContents
            MsgBox "Please enter Date Returned in the following format: MM/DD/YYYY"
            Application.EnableEvents = True
        End If
    Next c


    r.NumberFormat = "[$-409]dd-mmm-yy;@"


End Sub

 

[Mentor82]

Hi,
You are talking about this line, is that correct?

r.NumberFormat = "[$-409]dd-mmm-yy;@"

How do you want to have this formatted, mm/dd/yyyy? If so change this line to this

r.NumberFormat = "dd/mm/yyyy"

 

[willow1985]

I would like the following format:

07-Aug-19

Unfortunately both of the above codes for the r.NumberFormat give the same runtime error.

 

[Mentor82]

Hi,
Try this

[LEFT][COLOR=#333333][FONT=monospace]Private Sub ValidateDate(r As Range)


    Dim c As Range


    For Each c In r
        If c.Value <> "" And Not IsDate(c) Then
            Application.EnableEvents = False
            c.ClearContents
            MsgBox "Please enter Date Returned in the following format: MM/DD/YYYY"
            Application.EnableEvents = True
        else
           c.NumberFormat="[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][$-415]d mmm yy;@"[/FONT]
        End If
    Next c

End Sub
[/FONT][/COLOR][/LEFT]
[FONT=Verdana]
[/FONT]

 

[willow1985]

Still giving me the same error on the line: c.NumberFormat = "[$-415]d mmm yy;@"

Here is the complete code again as is now in case we are missing something:

Option Explicit



Private Sub Worksheet_Change(ByVal Target As Range)


    Dim rng As Range
    
    Set rng = Range("B2")
    
    If Intersect(Target, rng) Is Nothing Then
        Exit Sub
    Else
        Call ValidateDate(rng)
    End If


End Sub




Private Sub ValidateDate(r As Range)




    Dim c As Range




    For Each c In r
        If c.Value <> "" And Not IsDate(c) Then
            Application.EnableEvents = False
            c.ClearContents
            MsgBox "Please enter Date Returned in the following format: MM/DD/YYYY"
            Application.EnableEvents = True
        Else
           c.NumberFormat = "[$-415]d mmm yy;@"
        End If
    Next c


End Sub

 

[Mentor82]

Hi,
The code in my opionion is OK. It's works on my laptop. So my question is whether your worksheet/workbook is protected? Perhaps you might upload your file to dropbox in order to have a look into it if you don't mind.

 

[willow1985]

Due to security reasons I cannot upload the workbook but yes it is protected, however the cell the code is referencing is unlocked.
I created another macro within the workbook so that any user can update information in the workbook by having the following code in the update Macro:

    Worksheets("New Data").Unprotect Password:="PASSWORD"
Run the rest of the code
    Worksheets("New Data").Protect Password:="PASSWORD"

Do I have to do something similar to the code that verifies the date?

I really just wanted to make sure all users entered in a valid date, I thought unprotecting the cell the date code was referencing was enough. I can use data validation if required, the only downside is you have to use a date range instead of setting it to just verify a valid date and format is entered.