how to hide a sensitive workbook while users are still able to enter onto it

[mania112]

hello,

quite a difficult question to get my head round.

we have a spreadsheet called prop reg (proposal register). as business is picking up and staff numbers increase it's no longer appropriate for information regarding our finances to be exposed to everyone.

this proposal register needs to essentially be blocked from 'regular' staff view, however with the ability for them to be able to enter information on to it.

in my mind i would see that this would work with some sort of switchboard system where (when they open the spreadsheet) they would be greated with a 'ENTER NEW CLIENT' form.

however:

a) how do i create this so the data on the spreadsheet isn't visible.
b) how do i make it available for the directors to access the spreadsheet with full permissions.


i have attached an example spreadsheet to hopefully shed some light on exactly what i need. The first 5 columns would be entered by the 'staff' and the commission paid fields would be completed by someone who had full access.

many thanks

[shg]

There are lots of examples for how to do this; search the forum for force enable macros.

That said, Excel security is intended to prevent inadvertent errors, not to protect sensitive data from prying eyes. If your data falls in that category, you need to find another method.

[antonf]

...Excel security is intended to prevent inadvertent errors, not to protect sensitive data from prying eyes. If your data falls in that category, you need to find another method.

I once read somewhere on the web that to protect an open Excel file is as good as impossible....

[mania112]

ok, thanks for the reply.

i shall look in to 'force enable macros'.

although if its not really possible to secure it from users view it's probably not worth going any further with it.

i suppose a benefit of using these forms would be that it would eliminate mistakes and limit their ability to delete important data. so while it isn't secure it is at least safer; which might be the compromise.

at the end of the day, i don't think we'd want a member of staff on our team that wants to add up all the numbers anyway.... just prevents them posturing for a pay rise i guess :P

thanks again

[mania112]

i see how to force enable macros, but how do i autorun a macro when a user opens a workbook?

[royUK]

Yiou can simply make the data sheet very hidden, this will fool most Excel users. Then add a data entry form( there's a free example here: http://excel-it.com/vba_examples.htm)

[mania112]

Yiou can simply make the data sheet very hidden, this will fool most Excel users. Then add a data entry form( there's a free example here: http://excel-it.com/vba_examples.htm)

ok thanks.

i have attached the example for refernce.

i don't quite follow what's happening here. there is a hidden sheet? (other than the explanation and protectedsheet)

when data is added using the cmdbutton it is displayed on protectedsheet, so i dont understand how that is hidden.

[royUK]

You need to change the Sheet's Visible property in the VB Editor. This makes the sheet hidden from view, if you choose VeryHidden then it can only be accessed with code or from the VB Editor.

There's no point attaching my example, you need to attach yours. Also, that example does not contain the data Entry Form