Help with Code

[Jim G]

I have the following code used in data entry. I use it in several insatnces
and have no problems. However, in this configuration, it will, after 4 rows,
prompt for a response to the overwriting data test even though there is no
data in the target cells. I can skip a few lines and start re-entering and
after 4 lines it asks the over writie prompt again. In other workbooks
(different purpose/design) it will go forever if there is no data to over
write. The only thing I've added is "ActiveCell.Offset(0, 4).Value = "Y"" to
create a default, and changed the starting rows etc to fit the new form
demension. Anyone with any ideas?

Private Sub Worksheet_Change(ByVal target As Range)
If target.Cells.Count > 1 Then Exit Sub
If target.Column <> 6 Then Exit Sub 'last data entry cell
If target.Row < 19 Then Exit Sub 'starting row

If target.Offset(0, 1).Value <> "" Then
If MsgBox("You are overwrititng existing data. Are you sure?", vbYesNo) =
vbNo Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
End If


Application.EnableEvents = False
Cells(target.Row + 1, 1).Select
'MsgBox "Range" & target.Address & "was changed"

Range("g19:Q19").Copy target.Offset(0, 1).Resize(1, 8) 'formula row to copy
ActiveCell.Offset(0, 4).Value = "Y"

Application.EnableEvents = True

End Sub

 

[p45cal]

I can't see why it should be doing that, so it's time to debug:

after the line:
If target.Offset(0, 1).Value <> "" Then
add the following:
Stop
MsgBox "Target offset (0,1) contains what's between the pairs of xs
xx" & target.Offset(0, 1).Value & "xx"
Try the code, when it stops, use F8 to step through the lines one b
one. You say there's nothing in that cell, so you might expect to se
'xxxx', however I suspect you'll see the likes of 'xx xx', that is
space, in the cell.
Pressing F5 allows the macro to proceed as normal again.
Over to you

 

[Patrick Molloy]

I can't replicate the problem. With your code, on a blank sheet, i can enter
values in F23, F24,-- the code takes me to A iof the next row, placign Y in
D of that row.
etc then repeat while there's data there. I get no warnings. I only get a
warning if I enter something in G23, G24 while there's data in F23, 24 etc.

 

[Jim G]

It doesn't do on other workbooks or on a blank sheet with the code added.
However, on this particualar work book it does it every 4th row. If I skip a
row the count starts again. In otehr words, the third row of data entry
brings up the error message, so it counts the blank row.

I added the debug message. the result was the result of the formula that
was copied in from G19. If I delete the formula (=F19/1.1) or change it to
D19/1.1, it will work as expected. Change back to F19 and it stops again.

Would there be something else going on with F19 and how could I find it?

For now, I've taken out the eeror check on the working template. However, I
would like to get to the bottom of it. If anyone wants it, it can email the
file.

 

[Jim G]

That's waht it's supposed to do and I get the same result in a blank sheet.
However, see the response to p45cal.

 

[p45cal]

It doesn't do on other workbooks or on a blank sheet with the cod
added.
However, on this particualar work book it does it every 4th row. If
skip a
row the count starts again. In otehr words, the third row of dat
entry
brings up the error message, so it counts the blank row.

I added the debug message. the result was the result of the formul
that
was copied in from G19. If I delete the formula (=F19/1.1) or chang
it to
D19/1.1, it will work as expected. Change back to F19 and it stop
again.

Would there be something else going on with F19 and how could I fin
it?

For now, I've taken out the eeror check on the working template.
However, I
would like to get to the bottom of it. If anyone wants it, it ca
email the
file.

Run down column G where you think there's nothing, and I think you wil
find formulae not deleted from previous runs. I suspect you're no
seeing anything because the result of the formula is zero and somewher
you've set to display zeroes as blank.
I'll try to send you a private message with my email address fo
sending the file to me if needed

 

[Jim G]

This gets more bizare!

I looked for spaces or formulas from previous data deletions (I had deleted
all rows to row 65M...). There were none.

I created a new sheet with all formulas etc re-typed. I put in each formula
one column at a time in row 19. I tested it for several rows on each and it
worked perfectly. I also made sure there were no links to other workbooks.
I then entered this formula in column P >>
=IF(ISERROR(MATCH(D19,$I$16:$N$16,0)),D19,"").

This checks for an account number in the list and enters the account number
from Col D if it doesn't match the defaults. If I test this for four rows it
throws up the prompt message as before. The strange thing is, I have
commented out the error trap as below. So how does it find the code to run
the message? The only difference is that if the response to overwriting data
is "NO", the code fails and brings up the "End" or "Debug" option. This also
has the effect of turning off macros and Excel needs to be restarted. I made
sure no other workbooks were open or macros (other than Personal Macro Book)
were in action/available.

So would you like to see the workbook in its entirety?
--
Jim


Private Sub Worksheet_Change(ByVal target As Range)
If target.Cells.Count > 1 Then Exit Sub
If target.Column <> 6 Then Exit Sub 'last data entry cell
If target.Row < 19 Then Exit Sub 'starting row

'If target.Offset(0, 1).Value <> "" Then
'If MsgBox("You are overwrititng existing data. Are you sure?", vbYesNo) =
vbNo Then
'Application.EnableEvents = False
'Application.Undo
'Application.EnableEvents = True
'Exit Sub
'End If
'End If


Application.EnableEvents = False
Cells(target.Row + 1, 1).Select
'MsgBox "Range" & target.Address & "was changed"

Range("g19:Q19").Copy target.Offset(0, 1).Resize(1, 8) 'formula row to copy

With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Size = 11
End With

ActiveCell.Offset(0, 4).Value = "Y"

Application.EnableEvents = True

End Sub

 

[p45cal]

This gets more bizare!

[snipped]

So would you like to see the workbook in its entirety?

Yes please, I'd be interested

 

[Jim G]

p45cal,
I've sent a PM to CodePage for your contact details.

I've even deleted the error code in it's entirety and it still runs. It is
likely there is some link to another file even though I've removed all
reference to links?

On another note; how do you remove a VBA project? I have some VBA projects
listed that I don't need or use and can't get rid of them.

Cheers
--
Jim

This gets more bizare!

[snipped]

So would you like to see the workbook in its entirety?

Yes please, I'd be interested.