xl2010
Win XP Pro SP3

A while back I created a macro to create and massage a new worksheet,
then show the built-in Move/Copy worksheet dialog so I could specify
where to move the new worksheet.

I've used the macro several times since. Now it's crashing "after" using
the built-in dialog xlDialogWorkbookMove. I say after, because after xl
recovers, the sheet was moved correctly in the recovered workbooks.

I can invoke the dialog from the UI, and all is well.
It doesn't matter if I use xlDialogWorkbookMove or xlDialogWorkbookCopy,
the same symptoms occur.

If I cancel, no error.
If I copy, no error.
If I move, the move is completed, but xl Crashes.

When the error first occurred, I got an error pop-up but I didn't write
down the error number. After adding On Error Resume Next, I no longer
get the error pop-up, but I never reach the next statement, either
(using VBE debugger to single-step.)

Now, I removed the On Error Resume Next to capture the error message for
this post, but I'm no longer getting the error pop-up ?!

I did a shutdown / restart ... no joy.

I created this bare bones procedure, and it also falls over as described
above.


Option Explicit

Sub x()
Dim vv
Dim v
On Error Resume Next
v = Application.Dialogs(xlDialogWorkbookMove).Show
Set vv = Err
On Error GoTo 0
End Sub


My original code included only the single statement

Application.Dialogs(xlDialogWorkbookMove).Show

at the end of the procedure. I tried adding the [ v = ... ] and the
error trapping just to see if it made any difference.

Any suggestions?

Memory and/or other hardware trouble beginning in my long-in-the-tooth
desktop?

Interference from some recent automatic windows update?

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Update:
Today, I did more testing and managed to catch the error dialog box:

Automation error
The object invoked has disconnected from it's clients.

followed by "the application has encountered an error and needs to
close."

1. Move within the same workbook executed without error.
2. Move to (new book) produced "object disconnected error" and crash.
3. Move to other open book produced crash without error dialog.

At this point I modified the macro to use the Copy dialog followed by
code that deletes the extra worksheet so I have a useable work-around.

"Clif McIrvin" wrote in message
...
xl2010
Win XP Pro SP3

A while back I created a macro to create and massage a new worksheet,
then show the built-in Move/Copy worksheet dialog so I could specify
where to move the new worksheet.

I've used the macro several times since. Now it's crashing "after"
using the built-in dialog xlDialogWorkbookMove. I say after, because
after xl recovers, the sheet was moved correctly in the recovered
workbooks.

I can invoke the dialog from the UI, and all is well.
It doesn't matter if I use xlDialogWorkbookMove or
xlDialogWorkbookCopy, the same symptoms occur.

If I cancel, no error.
If I copy, no error.
If I move, the move is completed, but xl Crashes.

When the error first occurred, I got an error pop-up but I didn't
write down the error number. After adding On Error Resume Next, I no
longer get the error pop-up, but I never reach the next statement,
either (using VBE debugger to single-step.)

Now, I removed the On Error Resume Next to capture the error message
for this post, but I'm no longer getting the error pop-up ?!

I did a shutdown / restart ... no joy.

I created this bare bones procedure, and it also falls over as
described above.


Option Explicit

Sub x()
Dim vv
Dim v
On Error Resume Next
v = Application.Dialogs(xlDialogWorkbookMove).Show
Set vv = Err
On Error GoTo 0
End Sub


My original code included only the single statement

Application.Dialogs(xlDialogWorkbookMove).Show

at the end of the procedure. I tried adding the [ v = ... ] and the
error trapping just to see if it made any difference.

Any suggestions?

Memory and/or other hardware trouble beginning in my
long-in-the-tooth desktop?

Interference from some recent automatic windows update?

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)





--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin explained on 1/22/2011 :
Update:
Today, I did more testing and managed to catch the error dialog box:

Automation error
The object invoked has disconnected from it's clients.

followed by "the application has encountered an error and needs to close."

1. Move within the same workbook executed without error.
2. Move to (new book) produced "object disconnected error" and crash.
3. Move to other open book produced crash without error dialog.

At this point I modified the macro to use the Copy dialog followed by code
that deletes the extra worksheet so I have a useable work-around.

"Clif McIrvin" wrote in message
...
xl2010
Win XP Pro SP3

A while back I created a macro to create and massage a new worksheet, then
show the built-in Move/Copy worksheet dialog so I could specify where to
move the new worksheet.

I've used the macro several times since. Now it's crashing "after" using
the built-in dialog xlDialogWorkbookMove. I say after, because after xl
recovers, the sheet was moved correctly in the recovered workbooks.

I can invoke the dialog from the UI, and all is well.
It doesn't matter if I use xlDialogWorkbookMove or xlDialogWorkbookCopy,
the same symptoms occur.

If I cancel, no error.
If I copy, no error.
If I move, the move is completed, but xl Crashes.

When the error first occurred, I got an error pop-up but I didn't write
down the error number. After adding On Error Resume Next, I no longer get
the error pop-up, but I never reach the next statement, either (using VBE
debugger to single-step.)

Now, I removed the On Error Resume Next to capture the error message for
this post, but I'm no longer getting the error pop-up ?!

I did a shutdown / restart ... no joy.

I created this bare bones procedure, and it also falls over as described
above.


Option Explicit

Sub x()
Dim vv
Dim v
On Error Resume Next
v = Application.Dialogs(xlDialogWorkbookMove).Show
Set vv = Err
On Error GoTo 0
End Sub


My original code included only the single statement

Application.Dialogs(xlDialogWorkbookMove).Show

at the end of the procedure. I tried adding the [ v = ... ] and the error
trapping just to see if it made any difference.

Any suggestions?

Memory and/or other hardware trouble beginning in my long-in-the-tooth
desktop?

Interference from some recent automatic windows update?

-- Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

I'm curious as to why you'd invoke the dialogs to move/copy when using
the Move/Copy methods accept optional Before/After parameters to
specify destination (within same wkb or other open wkb). If these
parameters are omitted then a new wkb is created with just the
moved/copied sheet[s].

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...


I'm curious as to why you'd invoke the dialogs to move/copy when using
the Move/Copy methods accept optional Before/After parameters to
specify destination (within same wkb or other open wkb). If these
parameters are omitted then a new wkb is created with just the
moved/copied sheet[s].



The macro is for my use, not for distribution. There are several
possible destination workbooks, and it seemed easier to use the built-in
dialog than to devise code to determine the destination workbook.

I guess I could use the file picker dialog .. then I wouldn't have to
remember to open the destination workbook before launching the macro
<g.


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin expressed precisely :
"GS" wrote in message
...


I'm curious as to why you'd invoke the dialogs to move/copy when using the
Move/Copy methods accept optional Before/After parameters to specify
destination (within same wkb or other open wkb). If these parameters are
omitted then a new wkb is created with just the moved/copied sheet[s].



The macro is for my use, not for distribution. There are several possible
destination workbooks, and it seemed easier to use the built-in dialog than
to devise code to determine the destination workbook.

Are you running with the sheet tab menu ("Ply") disabled or the no
sheet tabs option set?


I guess I could use the file picker dialog .. then I wouldn't have to
remember to open the destination workbook before launching the macro <g.

Hmm.., sounds like a sheet utilities feature I made for a client
quotation app where users could move/copy selected sheets to other wkbs
that were open, OR click a browse button to open the target wkb if it
wasn't already open. This utility listed all sheets in the active
workbook and displayed info about each (wksName, customer, date, days
aging, date expires, visible...), could hide/unhide sheets (sets a flag
next to hidden ones), rename, delete, email (as attachment), move,
copy, and activate (..where if hidden this was toggled to visible).

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
Clif McIrvin expressed precisely :
"GS" wrote in message
...


I'm curious as to why you'd invoke the dialogs to move/copy when
using the Move/Copy methods accept optional Before/After parameters
to specify destination (within same wkb or other open wkb). If these
parameters are omitted then a new wkb is created with just the
moved/copied sheet[s].



The macro is for my use, not for distribution. There are several
possible destination workbooks, and it seemed easier to use the
built-in dialog than to devise code to determine the destination
workbook.

Are you running with the sheet tab menu ("Ply") disabled or the no
sheet tabs option set?

Standard options, afaik.

idk what you mean by "Ply" ... the tab context menu is available, if
that's what you are referring to. A quick check of options behind the
File tab (xl2010) turned up the no sheet tabs option; but it was clear
(of course - I do have sheet tabs.)



I guess I could use the file picker dialog .. then I wouldn't have to
remember to open the destination workbook before launching the macro
<g.

Hmm.., sounds like a sheet utilities feature I made for a client
quotation app where users could move/copy selected sheets to other
wkbs that were open, OR click a browse button to open the target wkb
if it wasn't already open. This utility listed all sheets in the
active workbook and displayed info about each (wksName, customer,
date, days aging, date expires, visible...), could hide/unhide sheets
(sets a flag next to hidden ones), rename, delete, email (as
attachment), move, copy, and activate (..where if hidden this was
toggled to visible).

kinda sorta. I've done something a bit similar but nowhere near as
extensive.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

"GS" wrote in message
...


I'm curious as to why you'd invoke the dialogs to move/copy when using
the Move/Copy methods accept optional Before/After parameters to
specify destination (within same wkb or other open wkb). If these
parameters are omitted then a new wkb is created with just the
moved/copied sheet[s].


I didn't realize that .Move would create a new wkb - learned something
new here (again!) <g

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)