The following code is snipped from a worksheet_change event sub:

With Sheets("Jobs")
Stop

If target.row = Range("EnterJob").row Then .Cells(target.row, _
JobBook.JOBS_DateIn).Value = Format(Date, "mm-dd-yy")

Stop
End With

I put the stops in while debugging. The program hits the first stop, I
click continue, then it just seems to flow somewhere else. The
assignment to .Cells(target.row, JobBook.JOBS_DateIn).value is never
carried out, and the second Stop is never reached... I am utterly
baffled. It works fine in Excel 2000. This bug is showing up in Excel
2002, but I am pretty sure this section of code *was* working fine a
week ago. I tried commenting out that line, but the same thing happens
with the next line instead... Huh?

Iain

Where is the range "EnterJob" located. If on sheet Jobs, then try putting a
period in front of
Range("EnterJob")

I assume this code is in a sheet module. If so, the unqualified Range
reference refers to the sheet containing the code.

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
The following code is snipped from a worksheet_change event sub:

With Sheets("Jobs")
Stop

If target.row = Range("EnterJob").row Then .Cells(target.row, _
JobBook.JOBS_DateIn).Value = Format(Date, "mm-dd-yy")

Stop
End With

I put the stops in while debugging. The program hits the first stop, I
click continue, then it just seems to flow somewhere else. The
assignment to .Cells(target.row, JobBook.JOBS_DateIn).value is never
carried out, and the second Stop is never reached... I am utterly
baffled. It works fine in Excel 2000. This bug is showing up in Excel
2002, but I am pretty sure this section of code *was* working fine a
week ago. I tried commenting out that line, but the same thing happens
with the next line instead... Huh?

Iain

Tom Ogilvy wrote:
Where is the range "EnterJob" located. If on sheet Jobs, then try putting a
period in front of
Range("EnterJob")

I assume this code is in a sheet module. If so, the unqualified Range
reference refers to the sheet containing the code.

--
Regards,
Tom Ogilvy

Thanks for your reply. I've tried what you suggest, it doesn't work
(there is only one EnterJob range in this workbook, in the sheet
containing the code, so it shouldn't have made a difference anyway).
Any more ideas?

Iain

Possibly you are doing this in the change event and you are causing
recursive calls.

Application.EnableEvents = False
If target.row = Range("EnterJob").row Then .Cells(target.row, _
JobBook.JOBS_DateIn).Value = Format(Date, "mm-dd-yy")
Application.EnableEvents = True

--
Regards,
Tom Ogilvy

wrote in message
oups.com...

Tom Ogilvy wrote:
Where is the range "EnterJob" located. If on sheet Jobs, then try
putting a
period in front of
Range("EnterJob")

I assume this code is in a sheet module. If so, the unqualified Range
reference refers to the sheet containing the code.

--
Regards,
Tom Ogilvy

Thanks for your reply. I've tried what you suggest, it doesn't work
(there is only one EnterJob range in this workbook, in the sheet
containing the code, so it shouldn't have made a difference anyway).
Any more ideas?

Iain

Tom Ogilvy wrote:
Possibly you are doing this in the change event and you are causing
recursive calls.

Application.EnableEvents = False
If target.row = Range("EnterJob").row Then .Cells(target.row, _
JobBook.JOBS_DateIn).Value = Format(Date, "mm-dd-yy")
Application.EnableEvents = True

--
Regards,
Tom Ogilvy


Good idea, unfortunately one I already tried. It disables events, then
does it's vanishing act leaving them turned off, at which point I have
to manually re-enable them.

Iain

Tom Ogilvy wrote:
Possibly you are doing this in the change event and you are causing
recursive calls.

Application.EnableEvents = False
If target.row = Range("EnterJob").row Then .Cells(target.row, _
JobBook.JOBS_DateIn).Value = Format(Date, "mm-dd-yy")
Application.EnableEvents = True

--
Regards,
Tom Ogilvy

wrote in message
oups.com...

Tom Ogilvy wrote:
Where is the range "EnterJob" located. If on sheet Jobs, then try
putting a
period in front of
Range("EnterJob")

I assume this code is in a sheet module. If so, the unqualified Range
reference refers to the sheet containing the code.

--
Regards,
Tom Ogilvy

Thanks for your reply. I've tried what you suggest, it doesn't work
(there is only one EnterJob range in this workbook, in the sheet
containing the code, so it shouldn't have made a difference anyway).
Any more ideas?

Iain


Strangely (or more strangely), when another macro changes the cell
contents, the event works fine. It's only when the user changes it
that it doesn't work.

Iain

Hi Iain,

If you had said it works fine in XL2002 but not in XL2000 and particularly
XL97, the problem might be related to a UDF error without an error handler.
Code can just stop when code creates a sheet change and triggers
calculation, even with events disabled.

I don't think that problem occurs in XL2002, but might be worth checking as
a long shot.

Does the problem occur if Calculation is manual.

Regards,
Peter T


wrote in message
ups.com...
The following code is snipped from a worksheet_change event sub:

With Sheets("Jobs")
Stop

If target.row = Range("EnterJob").row Then .Cells(target.row, _
JobBook.JOBS_DateIn).Value = Format(Date, "mm-dd-yy")

Stop
End With

I put the stops in while debugging. The program hits the first stop, I
click continue, then it just seems to flow somewhere else. The
assignment to .Cells(target.row, JobBook.JOBS_DateIn).value is never
carried out, and the second Stop is never reached... I am utterly
baffled. It works fine in Excel 2000. This bug is showing up in Excel
2002, but I am pretty sure this section of code *was* working fine a
week ago. I tried commenting out that line, but the same thing happens
with the next line instead... Huh?

Iain