Access to a hidden spreadsheet
Hi
I have a workbook used by 12 – 15 people. All users access their own area of a single sheet.
What I would like to know is, is it possible to add a button to this sheet which is password protected which will then give management access to a ‘hidden’ sheet within the same workbook? And if so, how would that be done? Ie, from a general sheet called ‘main’, can a password protected button grant access to a hidden sheet called ‘data’
Thanks
Hi
Only way I know how to do it is Alt-F11 will get you into the VBA screen select your worksheet, bottom of the properties window you will see the word xlsheetvisible if you click once on that a small pulldown window will appear and you select xlsheetveryhidden, this will hide the sheet so that the normal hide/unhide in excel will not work. Unfortunatley VBA is beyond me at the moment hopefully a VBA expert will take this up for you.
Chris
Click my star if I helped Thanks
Thanks Chris, Im sure Ive seen this (or very similar) done before so Im hopeful.
Cheers
http://www.excelforum.com/excel-prog...ith-macro.html
Hi
Found the above for you see if that helps.
Chris
Thanks for taking the time to search this out Chris. It certainly heading in the right direction, but out auditors are specifying password protection - thats the bit Im struggling with. Im thinking you'd need to display a msgbox and take in a password which gets compared to a cell containing the correct answer. But how you do that is beyond me .
Cheers
In a standard module add this code:
Then add a button and link the button to this subroutine.
Please Login or Register to view this content.
Don't forget to add a password to the vba project or anyone can just open and change the code.
It's a bit long but it checks several possible scenarios and gives message boxes along the way to guide the end user.
Hope this helps.
abousetta
Last edited by abousetta; 11-12-2012 at 04:36 PM. Reason: Highlighted areas you might want to change
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
You can hide the spreadsheet normally and then protect the workbook with a password (workbook, not worksheet). With the workbook protected, users cannot view hidden worksheets directly. Does that suffice?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Well if the OP does that then I just wasted the last 15 minutes coming up with this example
Hi Abousetta
NO your time hasn't been wasted. but I am getting an error at " ActiveSheet.Shapes("Button 1").Select"
What I have done is to add a Commandbutton and renamed it Button 1 The code has been placed in a new module.
Am I doing something wrong?
Cheers
Just had a thought - Im access your code by putting this behind the button:
Private Sub CommandButton1_Click()
Call HideDataSheet
End Sub
Is this correct?
No, that is an activex button not a standard button (Developer >> Insert >> Form controls). I am uploading an example workbook. You many need to change the shape's name in the code depending on what it is called in your worksheet.
abousetta
Last edited by abousetta; 11-12-2012 at 07:19 PM. Reason: Removed attachment... new version below
Thanks Abousetta, thats marvelous - I'd never have gotten to that stage! - Ive only used active X buttons up til now. The button you used, can you edit that - like you can active X buttons - ie resize, move on the page change colour etc?
Cheers
Supdem
Yes, you can. Here is a discussion about the differences I found online and here is the official bit from MS. If you want to use the ActiveX button, here is an example with both types of buttons.
Another question you might want ask yourself (or your managers):
* should the password be case sensitive
* do you want to show the inputted characters or show astrix instead (e.g. *****)
* how secure does this have to be... US Department of Defense level of security or average Jo security
Hi Abousetta
Thanks for that – really appreciate you finding those references, they’ve helped clear the mist!
The password issue isn’t in the realm of National Security – none of the users are driven to – or capable of hacking, beyond the level we’ve looked at so if I can protect one sheet, then that should keep the auditors at bay – but the good news is, I’m at work and have successfully implemented your solution – I went for the Active X which I’m more familiar with.
Thanks very much for your help – once again, this website and the folk on it have really saved the day. Brilliant.
Supdem
Glad we are able to be of assistance. If you are satisfied with the answers then please mark the thread as solved.
Good luck.
abousetta
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks