Trapping Events with a Class Module

[Kristina Conceicao]

Some of our users frequently need to combine worksheets from several
sources into a single workbook. Some of the worksheets come from files
that originated on the mac; some from files that originated on a PC.
All of the files contain lots of dates. Because different date systems
were used to create the files, some dates are wrong in the final
workbook. I'd like to warn the users when they're copying a worksheet
from a 1900 date system workbook into a 1904 date system workbook, and
vice versa.

After consulting help, I took a shot at creating a class module. I
think I got it right, because I was able to "trap" the NewWorkbook
event. However, I can't figure out how to trap a move or copy of a
worksheet. Is there any way to do this? I need the code to work in
Excel 98 (via Classic), Excel 2004 and Excel XP.

For now, I'm resorting to writing my own move/copy code that will
perform the necessary checks, and will ask the users to use it in place
of the default menu item.

Thanks for any and all help,

Kristina

 

[JE McGimpsey]

For now, I'm resorting to writing my own move/copy code that will
perform the necessary checks, and will ask the users to use it in place
of the default menu item.

The only thing I can think of is to trap the Workbook Object's
SheetActivate event. The SheetActivate event will fire if a worksheet is
moved from one workbook to your target workbook. If you keep a list of
existing sheets, you can detect a new sheet and scrub it. You'll
probably need to run the SheetDeactivate event, too, to update your list
if a worksheet is deleted.

An alternative, though not as nice, would be to supply a menu or toolbar
command to toggle the dates in the active sheet. That requires manual
intervention by the user, of course.

OTOH, I like your move/copy approach. You can disable the built-in
worksheet copy command (remember to disable it on both the main and the
contextual menus) and insert your own.

 

[Kristina Conceicao]

The only thing I can think of is to trap the Workbook Object's
SheetActivate event. The SheetActivate event will fire if a worksheet is
moved from one workbook to your target workbook. If you keep a list of
existing sheets, you can detect a new sheet and scrub it. You'll
probably need to run the SheetDeactivate event, too, to update your list
if a worksheet is deleted.

An alternative, though not as nice, would be to supply a menu or toolbar
command to toggle the dates in the active sheet. That requires manual
intervention by the user, of course.

OTOH, I like your move/copy approach. You can disable the built-in
worksheet copy command (remember to disable it on both the main and the
contextual menus) and insert your own.

Thank you for your suggestions. I think that the move/copy approach
will be the one that best fits my users needs, although I will also
supply them with a button to toggle the dates, as you suggested.

I've run into a new problem now, though. I figured it would be easiest
to use the built in dialog for the move/copy of a worksheet. However, I
need to store the destination workbook in a variable so I can see if
it's date system is the same as the original workbook. (After the copy
is complete, I want to warn the user if the systems didn't match.) I've
been through help and can't figure out how to get to that information.
Maybe you can't, and I need to use a custom userform?