Debug Issue

[DataGuy]

Hi,

Project: Compensation Tool for the companies review/promotion.

I have built a table so that it when a change is made it records the change
made on the individual line item (first code) and I also have put a time
stamp if any change is made to the entire spreadsheet (second code). I sent
the comp tool out for my co-workers to test it. When they enter #s for new
salaries, they get a error to debug and it does not record the date and time.
I do not get this error. Can someone let me know what is wrong with my
code? I believe the error is in this line (.NumberFormat = "mm/dd/yyyy
[$-409]h:mm:ss AM/PM;@"). Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long

If Target.Cells.Count > 1 Then
Exit Sub 'one cell at a time
End If

If Application.Intersect(Target, Me.Range("AD12:AN76")) Is Nothing Then
'do nothing
Else
myRow = Target.Row
With Me.Cells(myRow, "AO")
.NumberFormat = "mm/dd/yyyy [$-409]h:mm:ss AM/PM;@"
Application.EnableEvents = False
.Value = Now
Application.EnableEvents = True
End With
End If

Application.EnableEvents = False
Range("C7").Value = Now
Application.EnableEvents = True
End Sub

 

[Dave Peterson]

It worked ok for me.

Maybe you should drop the localization stuff and try:

..NumberFormat = "mm/dd/yyyy h:mm:ss AM/PM;@"

Hi,

Project: Compensation Tool for the companies review/promotion.

I have built a table so that it when a change is made it records the change
made on the individual line item (first code) and I also have put a time
stamp if any change is made to the entire spreadsheet (second code). I sent
the comp tool out for my co-workers to test it. When they enter #s for new
salaries, they get a error to debug and it does not record the date and time.
I do not get this error. Can someone let me know what is wrong with my
code? I believe the error is in this line (.NumberFormat = "mm/dd/yyyy
[$-409]h:mm:ss AM/PM;@"). Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long

If Target.Cells.Count > 1 Then
Exit Sub 'one cell at a time
End If

If Application.Intersect(Target, Me.Range("AD12:AN76")) Is Nothing Then
'do nothing
Else
myRow = Target.Row
With Me.Cells(myRow, "AO")
.NumberFormat = "mm/dd/yyyy [$-409]h:mm:ss AM/PM;@"
Application.EnableEvents = False
.Value = Now
Application.EnableEvents = True
End With
End If

Application.EnableEvents = False
Range("C7").Value = Now
Application.EnableEvents = True
End Sub

 

[DataGuy]

Thanks for the suggestion Dave. I am not quite sure what exactly to do.
Could you perhaps explain.

It worked ok for me.

Maybe you should drop the localization stuff and try:

..NumberFormat = "mm/dd/yyyy h:mm:ss AM/PM;@"

Hi,

Project: Compensation Tool for the companies review/promotion.

I have built a table so that it when a change is made it records the change
made on the individual line item (first code) and I also have put a time
stamp if any change is made to the entire spreadsheet (second code). I sent
the comp tool out for my co-workers to test it. When they enter #s for new
salaries, they get a error to debug and it does not record the date and time.
I do not get this error. Can someone let me know what is wrong with my
code? I believe the error is in this line (.NumberFormat = "mm/dd/yyyy
[$-409]h:mm:ss AM/PM;@"). Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long

If Target.Cells.Count > 1 Then
Exit Sub 'one cell at a time
End If

If Application.Intersect(Target, Me.Range("AD12:AN76")) Is Nothing Then
'do nothing
Else
myRow = Target.Row
With Me.Cells(myRow, "AO")
.NumberFormat = "mm/dd/yyyy [$-409]h:mm:ss AM/PM;@"
Application.EnableEvents = False
.Value = Now
Application.EnableEvents = True
End With
End If

Application.EnableEvents = False
Range("C7").Value = Now
Application.EnableEvents = True
End Sub

 

[Dave Peterson]

Make this line:
..NumberFormat = "mm/dd/yyyy [$-409]h:mm:ss AM/PM;@"
look like:
..NumberFormat = "mm/dd/yyyy h:mm:ss AM/PM;@"

Thanks for the suggestion Dave. I am not quite sure what exactly to do.
Could you perhaps explain.

It worked ok for me.

Maybe you should drop the localization stuff and try:

..NumberFormat = "mm/dd/yyyy h:mm:ss AM/PM;@"

Hi,

Project: Compensation Tool for the companies review/promotion.

I have built a table so that it when a change is made it records the change
made on the individual line item (first code) and I also have put a time
stamp if any change is made to the entire spreadsheet (second code). I sent
the comp tool out for my co-workers to test it. When they enter #s for new
salaries, they get a error to debug and it does not record the date and time.
I do not get this error. Can someone let me know what is wrong with my
code? I believe the error is in this line (.NumberFormat = "mm/dd/yyyy
[$-409]h:mm:ss AM/PM;@"). Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long

If Target.Cells.Count > 1 Then
Exit Sub 'one cell at a time
End If

If Application.Intersect(Target, Me.Range("AD12:AN76")) Is Nothing Then
'do nothing
Else
myRow = Target.Row
With Me.Cells(myRow, "AO")
.NumberFormat = "mm/dd/yyyy [$-409]h:mm:ss AM/PM;@"
Application.EnableEvents = False
.Value = Now
Application.EnableEvents = True
End With
End If

Application.EnableEvents = False
Range("C7").Value = Now
Application.EnableEvents = True
End Sub