Solved: Using VBA to Log In Members with a Username and Password stored within the Workbook.

[DaveyBoy9]

Hello All,

I have created a Userform in Visual Basic to Allow users to be able to Log In to the workbook using a username and a password which is stored on a sheet within the same workbook.

I have already created the Userform complete with Labels, Text Boxes and a Combo Box.I have also created a button on my Home Screen to Activate the Log Userform when clicked.

I am now stuck on the tricky bit. I need a code to automatically load the form on the Home Page of the Workbook when opened and not allow access unless the username and the password are entered correctly.

I was hoping to be able to look up a range so that additional users can be added to the worksheet at any time.

Thanks in Advance

Dave

[joms]

have you tried considering to use the workbook_open event?

check out this snippet:
[VBA]
Private Sub Workbook_Open()
Msgbox "Enter Credentials if You don't have one, Buy me a coffee"
'Call your form that has the user credentials, username and password
End Sub
[/VBA]

find out more on this site:
http://support.microsoft.com/kb/265113/en-us

Good luck!!!

[Rob342]

There are a few ways to do this and depends what you or other users need to see you can hide the relevant sheets until the password is correct

Post a copy of what you have and i'll fill in the missing bits if I can ok
Rob

[DaveyBoy9]

Thanks for the feedback!

Here is what i already have:

A Home Page with a button macroed to bring up my Userform, I wasnt sure whether to have this or to have it pop up automatically when the document is opened. I dont claim to be a VBA expert so ive done this for now as im not what formula I need to make it pop up.

A Tab called Users where I hope to store my Usernames and Passwords to validate the form on log in.

Having some trouble posting the document, can I take your email? or send me an email on david.thair@princes.co.uk if you would rather not post it!

Thanks for you time in advance.

Dave

[Rob342]

Dave
We don't usually give out E Mails address's Try the following below
Attach your file using the or send it me via PM, which can be found in Quick links

Rob

[DaveyBoy9]

Rob,

Keep getting error every time I try and attach. Cannot PM as I am new to the site and have to send 10 messages to Site Admins before it will allow me.

Any Other Ideas?

Dave

[Kenneth Hobs]

There are two parts that you need to address.
1. Force macros enabled.
2. Userform for password and verification and actions.

Both of these are shown in the Knowledge Base. Search for terms like Force, Password, etc.

For item (1):
http://www.vbaexpress.com/kb/getarticle.php?kb_id=578

[Rob342]

I Have sent you an e mail ok

[Rob342]

Dave
See attached excel file, it will get you started in the right direction
I have password protected the main sheet password =dave
The VBA has not got a password but you will need to password it at a later stage
Login Dave 1 or Dave2 you can select from the dropdown or by 1st letter in the combo box
Password for Dave1= dave1
Password for Dave2=dave2
Have set up a defined name called USERS, when you add more users it will automatically show in the dropdown
You will have to add a routine if you delete any of the users otherwise the defined name will get corrupted.

See Ken's comments on enabling macro's etc
Rob

[Aussiebear]

Keep getting error every time I try and attach. Cannot PM as I am new to the site and have to send 10 messages to Site Admins before it will allow me.

Really??? 10 posts should do the trick, providing they are positive additions to the content of the thread.

[DaveyBoy9]

Rob,

thanks for all the work you have done on this document for me! Just one last question tho? Is it possible to set an additional access level for members to only be able to open the document as read only?

Regards

Dave