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

willow1985

Active Member
Joined
Jul 24, 2019
Messages
438
Office Version
2019
Platform
Windows
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

Code:
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Mentor82

Board Regular
Joined
Dec 30, 2018
Messages
203
Hi,
You are talking about this line, is that correct?
Code:
r.NumberFormat = "[$-409]dd-mmm-yy;@"
How do you want to have this formatted, mm/dd/yyyy? If so change this line to this
Code:
r.NumberFormat = "dd/mm/yyyy"
 

willow1985

Active Member
Joined
Jul 24, 2019
Messages
438
Office Version
2019
Platform
Windows
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

Board Regular
Joined
Dec 30, 2018
Messages
203
Hi,
Try this

Code:
[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]

 
Last edited:

willow1985

Active Member
Joined
Jul 24, 2019
Messages
438
Office Version
2019
Platform
Windows
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:

Code:
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

Board Regular
Joined
Dec 30, 2018
Messages
203
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

Active Member
Joined
Jul 24, 2019
Messages
438
Office Version
2019
Platform
Windows
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:

Code:
    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.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,903
Messages
5,411,132
Members
403,343
Latest member
Vinay M

This Week's Hot Topics

Top