"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