User FormButton Security ( stopping access)

[Rybrookar]

Good morning,

I have a problem figuring out a bit of code for a button on a user form.

The button works exactly as I want it to but I need to put a password on the button.

Basically I have a spreadsheet for budgeting. Part of this requires users to input payroll details for every member of staff. This spreadsheet will then be sent around to several people.

The user form button opens a worksheet to allow the input of the details. Once the details are in I don't want anyone else being able to open the worksheet.(so the button should need a password to be active).

Does anyone know how to do this ?

Any help would be appreciated.

Thanks

 

[WalkerEvans]

VBA Help says passwords work on controls as well as workbooks/worksheets thru Office 2003, but I have never been able to find any documentation as to how they are set for controls. At this point I'm tempted to say that it really can't be done.

However, all is not necessarily lost. There are some "tricks" that could yield the results it appears you want. If you don't mind others seeing the detail spreadsheet as long as they can't change anything a password on that sheet will do the trick. On the other hand, if you don't want anyone to see the details, a combination of a sheet password and a restriction as to which user(s) can legitimately access the details should work. This last can be accomplished through a check of the User Name in the environmental variables - see faq702-4949.

Hope this helps.



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[Rybrookar]

Thanks for the advice. I ran out of time on this and came up with a solution.

Basically I set the code on the button to open another form. This other form was simply a box to key a password. Hidden in the workbook I had already inserted the same word.

After keying the password a bit of code determined if the password keyed was equal to the hidden word. If not then the message "Wrong Password" popped up, If it did match then the Payroll menu popped up. It seems to work although its a bit round about.

 

[WalkerEvans]

Actually, it sounds like a very good solution and is not all that "round about". The only down-side I can visualize is security; someone could find and open the Payroll data by checking your code, bypassing the password entirely. Hopefully you won't have any snoopy users who are that determined (and knowledgable).



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long