Data disappears from Sheet but not when Debugging

[pjwhitfield]

Many a time Ive raised a query here and following further review have found the problem to be a bit of rogue code etc running that I hadnt realised, this time however its different.

I have a large Excel/Access application that pulls data, displays it in a form and allows the user to work on it, due to system and network constraints this has been developed in such a way as to cut down wherever possible on the transactions across the ADO so it pulls lots of data in at the start, copies selected items to internal sheets for working on and then writes back to the Db when required. The problem I have is in the early stages, where a user selects a case to work on, below is a basic description of the steps taken when a user first opens the app, requests a list of cases and selects one.

On Opening the App, a list of all cases possible for that user is pulled via ADO and dropped into a specific Sheet (CaseList)

If the user opens the Case Selection Form and selects relevant filtering criteria, the code will cycle through CaseList sheet and populate the ListBox with those cases that match the criteria.
User then selects an item and hits the Open button.

The first thing done then is to grab the relevant row from the CaseList sheet, copy it and Paste it into Row 2 of the "Case" sheet, before opening the frmCase Form. (Ive even tried removing this portion of the code and reverting back to pulling the relevant record from ADO and dropping it into the sheet, which still correctly populates but ulitmately ends with the data not being there).

The Initialize event of the form then does numerous actions to populate the fields (using the data in "Case" sheet), do some calculations etc before displaying the form and ending the code.

All of this works perfectly, the final result is the form is displayed with the data from the case populated (Therefore I know that the Case sheet definitely contained the data when the form was Initialized).


However, at some stage it seems to delete Row 2 from the Case sheet BUT only if the code is ran outside of Debug, if I debug at any stage of the code process (even the very last End Sub line) then the data is still there. Let it run through as normal though and the Case sheet contains no data in Row2 (the headings in Row1 are still there though).

Ive even tried duplicating the row copy and paste at the very end of the process which (when followed in Debug) is actioned correctly however again, in "normal" running it then disappears.

I am at a loss, normally debug would highlight where the problem is but this appears to be something outside of the code which isnt ran in debug mode?

Things that I know happen
Data is correctly copied from CaseList to Case sheet (resulting in a single row of data in row 2)
Data is then correctly populated from Case sheet into the form fields (therefore data is definitely still there when the Form Initialize runs).


I know people will want the workbook uploading but it will require a number of changes to make it operate elsewhere and like I say, Im 99.9% confident its not code at fault (or at least not code that can be seen to run in debug mode) so was wondering if anyone had seen anything like this before ie it works in debug but not in live.

[bulina2k]

Wild guessing here but have you tried a Sheets("Case").UsedRange.Select right before last End sub? - maybe the data is somewhere else?!?
Or have you tried running the code from another computer ? Maybe another excel version ?

[brokenbiscuits]

Experience has taught me it's ALWAYS the code, no matter how much you think it isn't.

[pjwhitfield]

bulina2k - When debugging I can see the data exactly where it should be (and its still there at the end).

brokenbiscuits - I would also agree, like I said at the top of the post, Ive usually been in these positions and then found the code at fault but that always been cases where the normal and debug runs give the same results, in this case it acts totally different when debugged, which ive never seen before.

[bulina2k]

I understand but I was referring to normal execution. After normal execution what area remains selected?

[pjwhitfield]

Apologies, the expected area ie A1:AB2

[bulina2k]

So, there is data in row 2 ?
Can you investigate where ?

[pjwhitfield]

thats the thing, theres nothing in that range, Ive ran through each cell and theres no visible data there (there may be spaces or tabs etc but no actual characters).

Just to clarify, row1 has the headings in which are always there,row 2 is the dynamic row as such, ie thats the only row that should change.

[brokenbiscuits]

All I can suggest is that you put a break point on the last line of your code, run it as normal and see what happens. Then if it's not looking as it should, keep going back and back a line to find exactly where it's going wrong in normal use.

[pjwhitfield]

Thats the weird thing though, Ive done that and it works fine, if I break point anywhere and then step through the remaining code the data is left exactly as it should be.

So when I break point on the very last End Sub, it stops I press F8 and the code ends and all is well. Remove the break point, run it and the data disappears.

[bulina2k]

.....
However, at some stage it seems to delete Row 2 from the Case sheet BUT only if the code is ran outside of Debug, if I debug at any stage of the code process (even the very last End Sub line) then the data is still there. Let it run through as normal though and the Case sheet contains no data in Row2 (the headings in Row1 are still there though)....

he already did that dindn't he?
and a code mistake ? a code that erase data but leaves spaces and tabs? i don't think so...

pjwhitfield , can you upload the file, or one file containing the Case sheet as is after the code is executed?

[pjwhitfield]

Like I say the file isnt really possible for uploading but I have attached a workbook containing the 2 sheets copies of before and after (not sure if they'll be any help though)

[bulina2k]

That's not it.. the after case sheet contains only one used row.
One possibility would be to save the original case file (after running the code) under a different name and delete every other sheets and then upload it.

[pjwhitfield]

Fair point, copying and pasting the sheets into a new workbook was never going to work! (thats how much this has addled my brain!)

[brokenbiscuits]

What happens if you add a break point, then press F5 to run through the remainder of the macro?

[pjwhitfield]

Exactly the same as using F8, the code runs, does exactly what it should do and the data in Case and CaseList are intact.

[bulina2k]

the code is protected by password

[pjwhitfield]

PotOfTea


thats the one

[bulina2k]

What happens if you add a break point, then press F5 to run through the remainder of the macro?

What happens if you add a break point at the very first line of the macro and then press F5 to run all the code?

[brokenbiscuits]

There's a lot of forms there! Where is the code that you're referring to kept?

[pjwhitfield]

What happens if you add a break point at the very first line of the macro and then press F5 to run all the code?

Exactly the same, it does as it should.

There's a lot of forms there! Where is the code that you're referring to kept?

The code thats a problem occurs after pressing the "Select Check" button (btnOpmCheck) on frmNewUploads. Near the end of that event it then opens another form frmFLMCheck2 which is the one that gets populated from the items in the Case sheet, which it does fine, its sometime after that where it removes the records.

[pjwhitfield]

UPDATE: If I add a Message Box as the very last line before the final End Sub then it works, take that MsgBox out and the data is deleted (use any other code such as setting a temporary variable etc, and it doesnt work). Its as if, theres some form of timing issue that if it gets to the end too quickly it deletes the data, now I know how ridiculous that sounds but its the only way I can explain it, debugging has an element of delay in it so its not happening then and by putting this message box in it causes the code to stop and catch up?

[brokenbiscuits]

Hmm... what about if you throw in something like this at the end, does it work then?

Application.Wait (Now + TimeValue("0:00:01"))

?

[pjwhitfield]

Yep, the adding of a Wait worked as well however Ive found the culprit.

Turns out that the Menu form from which you select this action contains the deletion code, this had been put in the Activate event and for some reason (that I still havent worked out) the form was being activated at the end of the code (even though theres no mention of the form in there).

It still doesnt explain why the form gets activated in live running but not debugging