Change event due to Cut & Paste, Drag & Drop or Edit

[cjakeman]

Hi,

I'm working on a small Excel application to help schedule classes for a
college department. It uses 3 worksheets to show different views of the
same timetable data organised by Room, by Course and by Tutor. These
have to be kept in sync and I use VBA to copy any change on one sheet
onto the other 2.

I'm using the Worksheet_Change event to respond to a user's edits.
After scanning the Usenet archive, I can't find any simple way to tell
the difference between an edit, a "cut and paste" and a "drag and
drop". For example, with a "cut and paste", I need to keep track of the
cell which has been cut, so that after a paste operation, I can also
remove the cut data from the other 2 sheets.

With the Worksheet_SelectionChange event as well, I'm using a state
machine to tell the difference between an edit, a "cut and paste" and a
"drag and drop". It seems to work but it's not pretty.

Does anyone know of a simpler, cleaner way?

Thanks, Chris

 

[Tom Ogilvy]

You need to maintain a master list using a table/ database/list construct

Then you can write a macro to rebuild the views from scratch when it is
necessary to update them or use a pivottable to orgnanize the data. A pivot
table is generally for summarizing numerical data - but it does have a count
function which can be used with categories such as then ones you describe.
It is certainly a powerfull tool if you can make it fit your requirements.

Debra Dalgleish
http://www.contextures.com/tiptech.html
has information on pivot tables.

Doing it the way you propose is a non-starter if you ask me.

If you want to do something like that, then I would suggest restricting the
user to entering data through a userform where you have full control.

 

[cjakeman]

Thanks Debra and Tom for your feedback. A pivot table would certainly
deliver multiple views of the same data but I'm going for ease of use.

The ultimate ease of use, I believe, is being able to drag cells around
on one sheet and have the changes updated to suit on the others.

Now that I know there's no easy way to do this, I'll take your
"non-starter" comment as a challenge. I've done some tests and they do
indeed lead to clumsy code.
If I can make it work, I'll report back.

Bye for now, Chris

 

[Tom Ogilvy]

I think I would just rebuild the other sheets on every change.

 

[cjakeman]

Hi Tom,

Sound advice, I'm sure.
Simple and quick to code and likely bug-free. Probably slow too - my
scheme updates a single row, but rebuilding the other sheets would
update 120 rows (12 hours/day x 5 days/week and a week for 2 each of
semesters).

I think my approach is worth pursuing. It's interesting and no-one is
paying for my time. (This exercise is just a break from marking
papers.) I've made it work correctly so the next challenge is to make
it generic and package it into a module that anyone can use without
change.

Thanks again for your feedback.

Bye for now, Chris