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 :-)

"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 :-)

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 :-)

Clif McIrvin pretended :
"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.)

Yes, Clif. The name of the menu that pops up when you right a sheet tab
is "Ply". So.., Commandbars("Ply").Enabled = False disables that menu.




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.

Well, this was a customization done to meet a client's specifications
as a mechanism to be able to manage large numbers of sheets within
several workbooks. Not really a common requirement in most apps. It was
just a userform with a multi-column ListBox and a set of buttons. I
could probably have used a grid control or ListView, given the number
of columns, but I didn't want to have to distribute any ActiveX
components.<bg

--
Garry

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

"GS" wrote in message
...
Clif McIrvin pretended :
"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.)

Yes, Clif. The name of the menu that pops up when you right a sheet
tab is "Ply". So.., Commandbars("Ply").Enabled = False disables that
menu.




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.

Well, this was a customization done to meet a client's specifications
as a mechanism to be able to manage large numbers of sheets within
several workbooks. Not really a common requirement in most apps. It
was just a userform with a multi-column ListBox and a set of buttons.
I could probably have used a grid control or ListView, given the
number of columns, but I didn't want to have to distribute any ActiveX
components.<bg


jic this discussion is of interest to anyone else .. or some future
search ...

What I did in my case was loop through every sheet of every workbook in
the current folder (mix design worksheets) and build a summary
worksheet, then put that summary data into an auto-filter table. I used
the double-click event to activate the sheet behind the selected row in
the table.

This would (imo) be a better fit for Access, but it does what it needs
to.

--
Clif McIrvin

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

Clif McIrvin expressed precisely :
jic this discussion is of interest to anyone else .. or some future search
...

What I did in my case was loop through every sheet of every workbook in the
current folder (mix design worksheets) and build a summary worksheet, then
put that summary data into an auto-filter table. I used the double-click
event to activate the sheet behind the selected row in the table.

This would (imo) be a better fit for Access, but it does what it needs to.

Sounds similar to what I use an 'Index' sheet for, except it has
hyperlinks to the target sheets. If the sheet is in another wkb Excel
opens/activates that. No VBA used here!

I did fail to mention, though, that my sheet manager utility previously
mentioned also will activate (via the list's doubleclick event) any
selected sheet in the list, opening its wkb if need be. This is all VBA
here!<g What makes this work is the utility has the ability for the
user to show all app sheets and displays where (which file) they're
stored. This requires using stiff wkb/wks protection so moving/copying
sheets is tightly controlled, but not much of a problem since it
affords keeping a log of which sheets are stored in which wkbs AND
where those wkbs are stored. (If it's been moved elsewhere, users are
prompted to locate it) -Some clients are really demanding, huh!

--
Garry

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