Preventing Users from Sending Workbook or Clearing Data Before Send

**stevchard** · 08-16-2011, 05:57 AM

Have an unusual problem that I have not been able to find a solution for and was hoping someone here might have an idea.

We have a spreadsheet which accesses confidential data from our HR system and then displays information regarding employees salary and calculations of potential redundancy pay offs they would be entitled to. Until recently, this was only used internally by the HR community however the business have decided to roll this out so that employees can access their own information via a workbook published on the sharepoint site for their Organisational Change Programme.

This has now been adapted to use the network credentials of the user to display only their own information and is working fine. The only issue remaining is that should an employee email this spreadsheet to a colleague, their information will have already been populated on open and is therefore visible to everyone who opens that workbook until they enable macros.

We have tried to look into disabling the send option in the Office Menu with no success and now are hoping that someone infinitely wiser has a solution which would help us to maintain privacy whether that be by clearing the data out before sending (another option we've tried but failed at) or somehow disabling the send options.

Thanks in advance for your help.

**TMS** · 08-16-2011, 10:47 AM

Generally, I think the approach to take in situations where it is essential for macros to be enabled is to have a "banner sheet" and hide all other sheets (xlSheetVeryHidden). The banner sheet can carry logos and instructions, whatever, and the key message that macros must be enabled to use the workbook. When the macros *are* enabled, they can initialise the other sheets and make them visible ... based on whatever criteria you specify, for example the User ID; at the same time, you'd hide the banner sheet. You would then use the Workbook Before Close Event to hide the visible sheets and unhide the banner sheet. You could even reset the sheets before you hide them so nothing "useful" will be forwarded.

You'd need to protect the VBA Project with a password so that it is not possible to see the workbook structure ... but that's less of an issue if you reset before closing the workbook.

Hope that helps as a suggested approach.

Regards

**stevchard** · 08-16-2011, 11:06 AM

Thanks for your reply TMShucks.

Unfortunately I had considered a banner sheet however that doesn't help us in the situation of an employee using the send function within outlook as this will bypass any code I place into BeforeSave/Close events and the Open event will not run until macros are enabled.

Thanks also for your other security tips, they are all in place, including protecting the workbook structure to stop someone using personal macros to access the data.

**TMS** · 08-16-2011, 05:29 PM

OK, as it's Excel 2003:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

To limit the scope and add to the confusion, call these modules from the Workbook Open, Activate and Deactivate events:

Please Login or Register  to view this content.

As I suggested previously, the VBA Project must be password protected to avoid circumvention.

Regards

**stevchard** · 08-17-2011, 07:21 AM

Thanks for that, we've come up with the solution of completely hiding the ribbon using the following code to hide the ribbon on open and restore it on close or save (at which point all personal data is stripped out)

Please Login or Register  to view this content.

Once again thanks for your help.

**TMS** · 08-17-2011, 07:38 AM

Ah, OK ... I could have sworn your profile said 2003, hence the proposed solution.

Nevertheless, you have your solution. Thanks for the rep anyway.

Regards