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

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
871
Office Version
  1. 365
Platform
  1. 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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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"
 
Upvote 0
I would like the following format:

07-Aug-19

Unfortunately both of the above codes for the r.NumberFormat give the same runtime error.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top