Number format property

[rjplante]

I have the code listed below for adding a colon to the time entered into a cell when you hit enter or arrow away form the cell after entering a time. I have recently encountered an error and I don't know what to do to fix it. I don't know if it is a preference I selected or just buggy macro code. Here is the error message:

"Run-time error '1004':

Unable to set the NumberFormat property of the Range class."

What do I do to fix this issue? Code highlights the text (listed below in red) when I launch the debugger.

Thanks for the help.

<code>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("D17:W23"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
If IsNumeric(c) And c <> "" Then
If Len(c) > 4 Then
c = Format(c, "00\:00\:00")
c.NumberFormat = "[h]:mm:ss"
Else
c = Format(c, "00\:00")
c.NumberFormat = "[h]:mm"
End If
End If
Next
Application.EnableEvents = True

ActiveWorkbook.Save

End Sub
<code></code></code>

 

[Jonmo1]

I can't seem to reproduce the error.

What is the value of c at the time of the error?

 

[rjplante]

I enter the value 720 or 1130 and get the error. After I hit the debugger and reset the macro and try to enter 1130 int a cell, I get the following result, "27120:00". Don't know what to do. Is there another way adjust the macro to fix the error? My worksheet is protected and I have added the line to unprotect and protect it again. Still doesn't work.

<code>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("D17:W23"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False

ActiveWorkbook.Unprotect

For Each c In d
If IsNumeric(c) And c <> "" Then
If Len(c) > 4 Then
c = Format(c, "00\:00\:00")
c.NumberFormat = "[h]:mm:ss"
Else
c = Format(c, "00\:00")
c.NumberFormat = "[h]:mm"
End If
End If
Next

ActiveWorkbook.Protect

Application.EnableEvents = True

ActiveWorkbook.Save

End Sub
<code></code></code>

 

[GTO]

This may be helpful:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=75

 

[rjplante]

Thanks!! That code runs without a problem. I appreciate the help.

Robert