“Status Quo Ante” of Workbook_SheetChange ?

Topic

New Reply

Is there a _sensible_ way to determine what was the value of the Target cell before its (manual) change triggered the Workbook_SheetChange Event of Excel 97 ? The only working (sort of) way I came up till now is to use the Application.Undo Method to retrieve the “status quo ante”. However the VBA/Help states the Undo must be at the beginning of the code (why ? If I have to use this I want to test the exception, not the rule) and I have not yet circumvented all secondary problems. I am toying with the idea to use the Workbook_SheetSelectionChange Event as monitor to obtain the pre-Change value. Did anyone of you ever use this ? Or do you have other and hopefully better ideas short of making a form which I do not want (approx. 400 cells times 13 sheets….) ? Any input is greatly appreciated.

PS: Rethinking it, as actually there are approx. 50 rows with 8 cells on each of the 13 pages, perhaps the only solution is to let the user access the rows with a “on the fly” 8 field input mask. But this is the last straw for me, so I would prefer something simpler/less kludgy.

Reply | Quote

Replies

Don’t you just need to save the address and value of the selected cell in the Workbook_SheetSelectionChange event?

Then when you get to the SheetChange event you’ll have the value there. You can use the address to check that Excel hasn’t forgotten to fire the SheetSelectionChange event (what you do if it has is another problem).

I’m not sure why you have to save all values from all sheets every time the selection changes. If you do for some reason I’ve missed though, there are some very quick ways of reading values from contiguous cells into arrays
eg arrTest=Range(“A1:B8”) where arrTest is a variant, and 50 by 8 by 13 isn’t really that big.

HTH
Jon

Reply | Quote

Jon, thanks. As I mentioned above the SheetSelectionChange is a possibility, but it seems to have some drawbacks:
– If you switch the Sheet and input right away into the cell which comes up as selected, it will not fire.
– Handling multi cell copy/paste is mind-boogling
– Now you mention that Excel sometimes forgets to fire it, or did you mean the above ?

I do not want to save all values, I am trying to setup a kind of ‘pass value forward’ or ‘carbon copy scheme’ from one sheet to the next of instead of using external links. As the users are…users, I want/need to waterproof this. I am stuck at how to know if the cell reported as changed was empty before or not.

As usually avoiding the problem is more effective than solving it heroically: Do you know how to setup relative links without using INDIRECT which, besides a bit slow, are not “Data|Sort” resistant ? I want to interconnect worksheets copied regularly from a single template. Right now it looks like I have to write links by macro, the people in charge of the update can not distiguish between a absolute and a relative cell address, can you believe this ?

Reply | Quote

Users are … users, but what would we do without them, bless their little cotton socks (after all, we all need someone to feel superior to).

I’m not sure what sort of relative links you are trying to create. If you mean along the lines of “I want the cell on sheet x that is in column b and every 12th row”, no I don’t know any way other than indirect. If you can give an example of the structure that you are trying to put together, I might be able to come up with something more helpful.

Jon

Reply | Quote