Security in Excel 2

[qmann]

I want to create an excel workbook that will be worked on by several people. The last sheet (Sheet6) is not to be viewed by anyone but one person. I want a button on Sheet1 and when it is pressed i want it to do the following.

Please enter a password:
(If right password makes sheet 3 visible and goes to that sheet)
(If wrong password textbox displays.. "sorry you do not have viewing rights&quot

the issues i also have are as follows:

what is to stop the users from disabling macros and can they not just go into the menu and unhide the worksheet?

Any help on this would be fantastic.

Thanks in advance.

Q

 

[qmann]

sorry..
(If right password makes sheet 3 visible and goes to that sheet)
should be:
(If right password makes sheet 6 visible and goes to that sheet)

 

[davidmunoz]

A few suggestions would be:
1. Protect the workbook with a password and have Worksheets from other "Users" workbook linked within this protected one with sheet 6 available.

2. A little more intense VB approach would be to create a button that runs a macro or VB script that goes to format, column, Hide/Unhide sheet 6 after an if statement written in prompts for user interaction (password) and what do display if not correct (a Message box)

3. And an alternative with a simplistic approach would be to hide sheet 6 through format, column, sheet on the menu bar and then protect the workbook with a password. You then would have to show others how to unprotect the workbook first then to unhide the sheet through the same format, column,sheet menu options.

Hope this helps!

 

[xlbo]

qmann - this'll work

mPass = inputbox("Enter password&quot
if mPass = "whatever the password is" then
sheets("Sheet6&quot.visible = true
else
msgbox "You do not have viewing rights"
end if

You will also need to have this in the workbook Before_Close event
To get there, you will need to goto the VBE and double click on "ThisWorkbook" in the project window
This will create the workbook module. Select "Workbook" from the left side dropdown and then "BeforeClose" from the right side dropdown and enter this code:

sheets("Sheet6&quot.visible = xlveryhidden

This will set sheet6 so that it cannot be made visible from the Format>Sheet>Unhide menu path.

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[qmann]

xlbo,

you hit the nail on the head.. thanks for all your input guys.

Couple of issues i'd like to try and fix.
I can't find the BeforeClose option on the workbook??? to hide the format>sheet>unhide menu path.

Also,
I have security on the excel sheets set to medium.. therefore it asks if you want to enable/disable macros.. i do not want this to show.. any other ideas.. i suppose i could make the security minimum and they probably wouldn't be able to figure it out but then again i'd rather be secure.

Also,
Is there a little bit of code i could add that would allow me to jump to sheet3 if the password is correct.

 

[qmann]

found the beforeclose option... sorry.

 

[qmann]

okay,

i tried the beforeclose and i get the error
run-time error '9'

subscript out of range.

this happens when i try to close the document.

 

[Bowers74]

Gmann,

if you set the Sheet6 to xlVeryHidden like xlbo suggested, you do not need to hide the format>sheet>unhide menu path.

If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[qmann]

good job guys.. you're fantastic,
can i automatically enable macros to start on a workstation with code or do i have to go into each persons machine and make sure their security setting is set to minimum.

also anyone figure out code that will automatically bring them to that sheet if the password is correct.

 

[qmann]

i was thinking.. even if i have an option that allows it to know whether or not macros are enabled. If they are not it will not allow them to enter the template.. that would work just as good.

 

[Bowers74]

I'm afraid you will have to go to each PC and set the security settings to Low (although I wouldn't recommend it).

To get the Macro to jump to Sheet6 enter the following after the sheets("Sheet6&quot.visible = true
line from xlbo:

Sheets("Sheet6).Select

That should do the trick!

If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[qmann]

i understand the low setting and i have antivirus so it seems pretty secure but i'm only worried about them being able to figure out how to get into the code and seeing the passwords...
Can i lock the command button or password enable it??

 

[qmann]

nope this won't work.. i need it to be able to recognize whether or not macros are enabled and if they are not you cannot gain access to the workbook. There has to be something or else security this way is useless

 

[Bowers74]

You can do even better than that. You can lock the entire code from your VBA project. Press Alt+F11 and right click on your VBA Project name, Select VBAProject properties and goto the Protection Tab. Select "Lock project for viewing" and enter & confirm your password.

This way, none of your users can view your code!

You can also lock the command button by right-clicking and selecting "Format Control" click the "Locked" checkbox, in the protections tab. Do the same for all of your cells (unlock the ones that can be edited and lock the ones that cannot be edited). Goto Tools->Protection->Protect Sheet. Check all of the checkboxes and enter a password, confirm that password and save your workbook. Close it and re-open it.

That should do the trick!



If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[qmann]

Exactly what i wanted!!! Thanks a ton. You've been a tremendous help!

 

[Bowers74]

Gee, another star would've been nice!

Thanks

If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!