Results 1 to 7 of 7

Thread: Date validation error - Unable to set the NumberFormat property of the Range class
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2019
    Posts
    177
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

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

    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"

  3. #3
    Board Regular
    Join Date
    Jul 2019
    Posts
    177
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    I would like the following format:

    07-Aug-19

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

  4. #4
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

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

    Hi,
    Try this

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

    Last edited by Mentor82; Aug 19th, 2019 at 01:57 PM.
    Regards,
    Sebastian

    "When you've eliminated the impossible, whatever remains, however improbable, must be the truth."

  5. #5
    Board Regular
    Join Date
    Jul 2019
    Posts
    177
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  6. #6
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

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

    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.
    Regards,
    Sebastian

    "When you've eliminated the impossible, whatever remains, however improbable, must be the truth."

  7. #7
    Board Regular
    Join Date
    Jul 2019
    Posts
    177
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •