Excel to have User Name and Password?

[Shaka215]

Hello fellow programmers!

I was woundering if any one knew some code to allow me to do the
following...

I have 10 employees with 10 unique user names and 10 unique
passwords...

I have all 10 employees listed in Column A and all the passwords listed
in Column B, so I am wondering is it possible to create a UserForm to
allow a person to enter the user name and password to gain access to
workbook if it matches correctly to their name and password off the
values on the spreadsheet? Any help is greatly appreciated!!!

 

[Guest]

Yes

You need to have an auto_open macro that opens a form for the user to enter
their name and password. You then check the name and password and if the name
and password agree then you can allow access.

You need to set all the sheets visibility to very hidden and you do that in
VB project exlorer except for one sheet that is a splash type screen. You
then password protect the vba project and then you save the work book hiding
all the sheets except 1.

You also have an auto_save and auto_close macro that saves the workbook
hiding the worksheets (for the save one you need to unhide the sheets
aft5erwards).

 

[Shaka215]

I appreciate your feedback but this isn't what I was hoping to do. I
have two diffrent columns with a user name and a password for a total
of 10 rows of information... A1:B10

I need to know how to program a VB User form to prompt for a user name
and password using the information from A1:B10 and grant access to the
workbook through the information stored inside of the workbook. The
plan is to have the page with the user names and passwords be read by
the VB User form and then if the password or user name doesn't match it
would display a message saying "Account doesn't exsist or invalid
entry...access is denied." If they got it the information correct it
would unhide a hidden spreadsheet... The idea is simple but I'm sure
the code isn't...I really need someone's help with this one!!! ANY HELP
IS MUCH APPRECIATED!!!

 

[NickHK]

Well, you still need to follow principles laid out by Martin, but instead of
showing the worksheet, do you actions from the userform.
If unsuccessful login, show your MsgBox and Exit Sub.

If your worksheets are named for the person who uses it, you can a global
variable to work with, irrespective of the person involved, assuming each
sheet has the same structure.
Dim WorkingSheet as worksheet

if username/password=OK then
set WorkingSheet =Worksheets(Username)
else
set WorkingSheet =nothing
msgbox "failed..etc"
exit sub
end if

NickHK

 

[Don Guillett]

How about this idea?

Sub askforpassword()
On Error GoTo nogood
ans = InputBox("enter password")
If Range("passwords").Find(ans).Row > 0 Then
'MsgBox "OK"
Sheets("sheet7").Unprotect
Exit Sub
End If
nogood: MsgBox "Access denied"
End Sub

 

[Shaka215]

Don,

Thank you so much your code worked just the way I wanted it to!! Your
the best!

-Todd

 

[Akash]

i am not able to know that hws r u running this code.

when ever i try to run this macro its asking me the password.

where & what password to type.

Where to store the password.

pls do tell me that....

Awaiting for ur mail

Akash

 

[Shaka215]

Akash,

Work with the code to figure it out...its not difficult to figure
out since he provided me the proper sequence and santax of the code...A
true coder / Excel Developer doesn't always need the exact method or
procedure to alter code to work it the neccessary method. Thanks to his
code I am now able to have diffrent passwords to run for diffrent
features of a workbook. I didn't use his code the way he displayed but
merely altered it to do what I wanted it to do...