Ensuring Events in Excel

[0ddball]

Does anyone know of any way - by hook or by crook - to //ensure// that a cell change event is fired? Even if people turn off events, disable macros, hold down sift of print it off and use tip-ex on the cell.

I don't care what technologies you use - my resources in this organisation are neigh on limitless. I'm trying to ensure an audit trail on a book - but it's not much point having an audit trail if people can just turn the damn thing off.

I'm thinking this is going to be something to do with writing an excel add-in and using COM callbacks, so I may be in the wrong place... but somehow I don't think so. I think people who work with VBA as much as you guys (and girls) probably know an API hack or two.


Yet another unchecked rambling brought to you by:
Oddball

 

[xlbo]

So if events are somehow turned off - how are you going to check for that and turn them back on? there's nothing for the code to hook into. Even if you did it as an add-in, id someone has disabled macros then no code will run whether event driven or otherwise...

Think you might be onto a loser here to be honest - excel is not the best bet if you absolutely have to ensure the integrity of something

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[0ddball]

Our current thinking is that we can monitor Excels state through a deployed service. We are hoping that - hooking into excels message queue - we may be able to gain insight into what's going on.

You pretty much said what I thought, there, xlbo. We're going to have to get very creative here.


Yet another unchecked rambling brought to you by:
Oddball

 

[xlbo]

Would suggest that maybe a .NET app that LOOKS like excel may be the way to go - or - even within excel, you could create a form that is used as the data entry rather than the spreadsheet - that way you have complete control of the events situation as if the workbook is opened without macros enabled, the form will not show and no data will be able to be entered...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DaveInIowa]

You could add an extra worksheet whose content informs the user that they must enable macros to use the workbook. On Workbook_Open, make this sheet xlSheetVeryHidden and your production sheets xlSheetVisible. On Workbook_BeforeClose, reverse the process, so the next time a user opens without enabling macros, this special worksheet is the only one they'll see.

 

[0ddball]

DaveInIowa:

Interesting, but still has the problem that - if the user turns off events during sheet operation then the Close event will not fire and the sheets will not be hidden.

I can't restrict the options of these people because they are all expert Excel users. If someone took away your VBA editor, how would you feel? I know I'd throw my teddy in the corner.

The other problem is that sometimes turning events off is a sensible precaution to prevent hammering the event macros - we have the macros in question provide the audit system a digest of their changes on completion.

I'm quite muzzy from a cold I just had, so I haven't solved this problem yet - but I will, and when I do I'll tell you all how Or I won't - and then I'll tell you all it was the medication speaking


Yet another unchecked rambling brought to you by:
Oddball

 

[PHV]

If secure auditing is a real concern then no one msoffice component suits, period.

 

[DaveInIowa]

So we're talking about your users somehow executing Application.EnableEvents = False ?
If there's another way to disable events I'd be curious to know.

Back to your problem, how about doing your auditing post-save? Save a copy of the workbook on the Workbook_Open event and then compare the 2 copies after the workbook has been closed.