Unusual excel behaivor causes crash... Magically linked worksheets?? Why?!
 

Hello all. Sorry for double posting if that's not appropriate, but I didn't
know which group this question was better suited for.

Test System:
I'm Running Office 2003 on WinXP with all the latest updates to both and
Symantec Antivirus v9 corp. edition running in the background. However, I
have tested this project on a Win2000 machine running Office 2000 with no AV
and I got EXACTLY the same results.

Project Description:
I have a rather long script in a userform that opens a workbook, pulls data
out of it and puts it into a UDT, then passes that UDT to a another workbook
which in turn fills one of it's worksheets with the data contained in the
UDT. It's simple and standard practice.

Crash Description:
The trouble occurs after the code is done executing. At this point,
everything appears to have worked perfectly until you notice that Excel
seems to have magically linked the worksheet containing the source data to
the destination worksheet. (Keep in mind these are in different workbooks
and the only interaction they had was through a set of procedures that read
data from one then passed it to the other.) When I select a cell on the
sheet in one workbook the same cell will have been selected on the sheet in
the other workbook as well, and hilighted too. This works both ways.
Clicking either sheet results in a corresponding selection change on the
other sheet, also hilighting it. If I drag across a large range of cells,
the same range will be selected and hilighted in the other sheet, too. I
have written no event procedures with this functionality. There is no code
running at this point as I have code execution paused in the VBE. Also, none
of the other worksheets in either of the two workbooks are affected. At any
time when the two worksheets are magically "linked" if I close either
workbook containing them (from code, or manually after code execution has
stopped) Excel crashes. Passing End to the VBE to clear all object
references does not undo the magical linking or prevent the crash either.

If that isn't weird enough, it gets worse! If I step through the code in the
second procedure (where I have determined the magical-codeless linking to
occur) the procedure runs as expected, when it's done executing Excel does
NOT magically link the sheets, and I can close either without crashing
Excel.

Another oddity is the failure of the ScreenUpdating property to change when
I try to set it. It stays on. (Removing this code doesn't prevent the crash,
either.)

What can cause this behaivor?! Please help! :(

-Mike

Example of my code:

(procedure 1, in a userform that opens the source workbook)
Sub Get_Data()

Dim firstWB as Workbook
set firstWB = workbooks.open("path",,ReadOnly:=True)

Dim udt as MyUDT

udt.Foo1 = firstWB.Worksheets("Source").Range("Bar").value
udt.Foo2 = firstWB.Worksheets("Source").Range("Bar").value
.....about 150 more lines of this....

call ws.Put_Data(udt)

End Sub

(procedure 2, in the destination worksheet)
Public Sub Put_Data(udt as MyUDT) <- by reference, could this have
anything to do with the "link"?

'after the line below is executed, screenupdating remains TRUE. WHY?!
Application.ScreenUpdating = False

me.Range("Bar1").value = udt.Foo1
me.Range("Bar2").value = udt.Foo2
.....about 150 more lines of this....

Application.ScreenUpdating = True

End Sub 'after this procedure is finished executing, the worksheets
become, "linked."

Unusual excel behaivor causes crash... Magically linked worksheets
 

Hi,
Hmmm. I am not sure but look in your macro to see if
thisworkbook.range(x).value=thatworkbook.range(y). value

This may create a link between the two workbooks. But if it is a simple copy
and paste then no link should occur unless you use the pastespecial ....


-- Mark


"Mike Mertes" wrote:

Hello all. Sorry for double posting if that's not appropriate, but I didn't
know which group this question was better suited for.

Test System:
I'm Running Office 2003 on WinXP with all the latest updates to both and
Symantec Antivirus v9 corp. edition running in the background. However, I
have tested this project on a Win2000 machine running Office 2000 with no AV
and I got EXACTLY the same results.

Project Description:
I have a rather long script in a userform that opens a workbook, pulls data
out of it and puts it into a UDT, then passes that UDT to a another workbook
which in turn fills one of it's worksheets with the data contained in the
UDT. It's simple and standard practice.

Crash Description:
The trouble occurs after the code is done executing. At this point,
everything appears to have worked perfectly until you notice that Excel
seems to have magically linked the worksheet containing the source data to
the destination worksheet. (Keep in mind these are in different workbooks
and the only interaction they had was through a set of procedures that read
data from one then passed it to the other.) When I select a cell on the
sheet in one workbook the same cell will have been selected on the sheet in
the other workbook as well, and hilighted too. This works both ways.
Clicking either sheet results in a corresponding selection change on the
other sheet, also hilighting it. If I drag across a large range of cells,
the same range will be selected and hilighted in the other sheet, too. I
have written no event procedures with this functionality. There is no code
running at this point as I have code execution paused in the VBE. Also, none
of the other worksheets in either of the two workbooks are affected. At any
time when the two worksheets are magically "linked" if I close either
workbook containing them (from code, or manually after code execution has
stopped) Excel crashes. Passing End to the VBE to clear all object
references does not undo the magical linking or prevent the crash either.

If that isn't weird enough, it gets worse! If I step through the code in the
second procedure (where I have determined the magical-codeless linking to
occur) the procedure runs as expected, when it's done executing Excel does
NOT magically link the sheets, and I can close either without crashing
Excel.

Another oddity is the failure of the ScreenUpdating property to change when
I try to set it. It stays on. (Removing this code doesn't prevent the crash,
either.)

What can cause this behaivor?! Please help! :(

-Mike

Example of my code:

(procedure 1, in a userform that opens the source workbook)
Sub Get_Data()

Dim firstWB as Workbook
set firstWB = workbooks.open("path",,ReadOnly:=True)

Dim udt as MyUDT

udt.Foo1 = firstWB.Worksheets("Source").Range("Bar").value
udt.Foo2 = firstWB.Worksheets("Source").Range("Bar").value
.....about 150 more lines of this....

call ws.Put_Data(udt)

End Sub

(procedure 2, in the destination worksheet)
Public Sub Put_Data(udt as MyUDT) <- by reference, could this have
anything to do with the "link"?

'after the line below is executed, screenupdating remains TRUE. WHY?!
Application.ScreenUpdating = False

me.Range("Bar1").value = udt.Foo1
me.Range("Bar2").value = udt.Foo2
.....about 150 more lines of this....

Application.ScreenUpdating = True

End Sub 'after this procedure is finished executing, the worksheets
become, "linked."

Unusual excel behaivor causes crash... Magically linked worksheets
 

Mark, thanks for the reply. I actually did figure out how the bug occurs,
and bug it is indeed. It has to do with filling cells from code while a cell
is selected that has an XML map. The XML mapping hilights those cells with a
blue border. If I have a cell selected that is not mapped (so that no blue
borders are shown) when I fill the sheet, the bug does not occur. I have
been able to reproduce this behaivor in different projects and on different
machines. After some more testing I believe a bug report is in order.

-Mike

"exceluserforeman" wrote in
message ...
Hi,
Hmmm. I am not sure but look in your macro to see if
thisworkbook.range(x).value=thatworkbook.range(y). value

This may create a link between the two workbooks. But if it is a simple
copy
and paste then no link should occur unless you use the pastespecial ....


-- Mark