Solved

creating a password userform

Posted on 2011-09-30
37
2,567 Views
Last Modified: 2013-11-25
I am a novice at VBA and creating user forms.  I actually have about 150 users each within different functional groups.  I am creating a paid time off tracking tool that will allow a user once they click on the name (NAME1 on the PTO menu)  a user form pops up asking for a password to be entered.  If entered correctly it will take them directly to their individual calendar.  I want to set the password to default allowing them to establish their own password, but in the event that they forget it the form is set up to allow them to reset and change it. Also,  instead of having two different forms I would like this to all be incoporated into one userform. . In addition these users are running off both 2003 and 2007.  We are in the process of upgrading but it is a slow process that can take a couple years.  I hope your able to help.  Sample templates are attached.
**user form1**
Private Sub Cancel_Click()
Sheets("Protected").Visible = xlVeryHidden
MsgBox "Password Not Entered.  Workbook will close!"
Unload Me
ThisWorkbook.Close
End Sub

Private Sub EnterPassword_Click()
Dim password As String
password = PasswordInput.Text


If password = WorksheetFunction.VLookup(UserList.Value, Range("Users_List"), 2, 0) Then
Sheets("Protected").Visible = xlVeryHidden
Sheets("Name1").Visible = xlVeryHidden
MsgBox "Log In Successful"
Unload Me
Else
Sheets("Protected").Visible = xlVeryHidden
Sheets("Sharmal").Visible = xlVeryHidden
MsgBox "Incorrect Password. Workbook Will Now Close."
Unload Me
ThisWorkbook.Close
End If



End Sub

Private Sub UpdatePassword_Click()
Dim password As String
password = PasswordInput.Text
If password = WorksheetFunction.VLookup(UserList.Value, Range("Users_List"), 2, 0) Then
User = UserList.Value
Sheets("Name1").Range("A1").Value = User
Unload Me
UserForm2.Show
Else
MsgBox "Passwords do not match, please try again"
End If



End Sub



Private Sub UserForm_Terminate()
Dim password As String
password = PasswordInput.Text

If PasswordInput.Text = "" Or UserList.Value = "" Then

MsgBox "must use this form, workbook will close"
ThisWorkbook.Close
ElseIf password = WorksheetFunction.VLookup(UserList.Value, Range("Users_List"), 2, 0) Then



End If
End Sub

**user form2

Private Sub CommandButton1_Click()

Dim password As String

password = TextBox1.Text
secondpassword = TextBox2.Text

If password = secondpassword Then

With Worksheets("Protected").Range("Users")
    Set c = .Find(Sheets("Sharmal").Range("A1").Value, LookIn:=xlValues)
    If Not c Is Nothing Then
        Sheets("Protected").Range(c.Address).Offset(0, 1).Value = password
    Sheets("Protected").Visible = xlVeryHidden
    Sheets("Sharmal").Visible = xlVeryHidden
MsgBox "Password Changed Successfully"

Unload Me
Sheets("Name1").Range("A1").Value = ""
    End If
End With

Else
MsgBox "Incorrect Password. Please Try Again."
End If




End Sub



Private Sub TextBox2_Change()

End Sub

Private Sub UserForm_Terminate()
Dim User As String

User = Sheets("Name1").Range("A1").Value

If TextBox1.Value = WorksheetFunction.VLookup(User, Range("Users_List"), 2, 0) Then

Else
MsgBox "must use this form, workbook will close"
ThisWorkbook.Close
End If
End Sub

Open in new window

Book2.xlsx
UserForm1.frm
UserForm2.frm
0
Comment
Question by:Melbut
  • 18
  • 14
  • 2
37 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 36975189
Hi
I am trying to attach the 2 userform to the excel sheet but I get following errors
Line 8: Property OleObjectBlob in UserForm1 had an invalid file reference.
Line 8: Property OleObjectBlob in UserForm2 had an invalid file reference.

What is the object that you have on both forms and what is the refrence that is selected ? I will need to see these 2 forms to be able to help you further.
gowflow
0
 

Author Comment

by:Melbut
ID: 36997382
Thank you for responding.  I am a novice when it comes to VBA and the code that is attached was obtained by internet searches.
Images-of-forms.docx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36999615
Hi
I saw the snapshot and that's fine I am a bit confused at this stage:
1) You code refers to sheets that do not exist in the workbook you posted. How to ge these worksheet
"Protected", "Sharmal",
2) Also the code refer to a Range Range("Users_List") I cannot find in the worksheet as well where is it ?
3) There is in userform1 (the snapshot) a combobox called username how is it filled and frem where ?

Finally can you advise what you need to acheive maybe there is a better route as what you have posted have lots of missing items that are required for what you need to acheive.

rgds/gowflow
0
 

Author Comment

by:Melbut
ID: 37003666
I am very flexible.  I more or less would like to have a password user form that allows a user upon clicking on their individual command button  pops up a password user form asking for a password to be entered.  If entered correctly it will take them directly to their individual calendar sheet.  I want to set the password to a default "password", so that in the event that forget they can reset their own password and reenter a new one.  In addition these users are running off both 2003 and 2007, so the code needs to work for both.

1.)  The users has a command button identified with their name (i.e NAME1)
2)   The user clicks on their command button, a user form pops up ask for their          password.  The user enters the default password which is "password"
3) the user is prompted to change the password to their choice overwriting the default password
4) After the user changes their password they should be able to go directly to their individual PTO calendar
5) If the user forgets his password he should be able click on a button within the user form (ie, forgot password or reset password to default) and giving them the ability to change it again.

Password sheet:
Username  Password
Name 1       Password (default)
Name 2       test1
Name 3       test 2

I hope this clears it up better.
0
 

Author Comment

by:Melbut
ID: 37003692
So if the code I submitted is not workable, I am completely ok with other suggestions.  I just need for the user to have the ability to enter a password that directs them to their individual PTO calendar and also the ability to reset and change their password again if they forgot what they initially changed it to.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37003773
So I can click on any button, select reset password and enter the default and have access to any and all data.  And since I reset the password, the actual person can't get in without resetting the password.

The idea behind passwords is security.  Can you relate the windows user name to the Excel user name?
If so you can use that name and don't need the form with all the buttons or a password or you can select the button and just ask for the password.

Reset should require some method of authentication such as emailing the reset password to them or a security question.

mlmcc
0
 
LVL 29

Expert Comment

by:gowflow
ID: 37005994
I read your comments and to be honest here are my comments/suggestions

1) I am totally in agreement with mlcc comments that Excel is not the place to handle security.
2) What I can propose is the following :
a) You provide a list of users you have so we save them onto a hidden sheet in the workbook.
b) We create sheets for every user that is in the database and create a routine that if a new user is added it will create a default calendar/sheet for him.
c) We make a routine so we pickup the user name from the window logon and this would direct the user directly to his/her calendar. For sure we will hide all other calendars and only show his calendar
d) if the user is new it will ask him do you want to create a calendar ? and if yes it will create a standard one for him.

The advantages is that you are not boged with passwords and change passwords whcih can be easily bypassed if one is a bit knowlegable, and also the access is ssimple and straight to the point as at the end all you need (correct me if I am wrong)  is to have the user connected to his/calendar when he openes the excel file.

The down side of your version is that if a user wants to take a look at an other user's calendar he would select the other user's name and ask to change password or put the default password and then set a new password and hv access to the other user calendar which is something maybe you do not favor.

If your agreable pls provide a list of the user names exactly as they will log in to windows when they use the file.
Like if they login from a domain I should have the domain name and if from their pc I should also know then we can make soem tests to make sure the excel recognizes the user when he logs in not simply create a default calendar ....

An other question, will all the users always log in to the same file ? like 1 file will hold all calendar for all users ?

gowflow
0
 

Author Comment

by:Melbut
ID: 37008216
I do not have all the users who will be using this PTO yet.  Here's what I have so far as a start and maybe you can work from the attachment
PTO-tracker.xls
0
 

Author Comment

by:Melbut
ID: 37008232
This sheet also once save automatically sends an email via outlook
0
 
LVL 29

Expert Comment

by:gowflow
ID: 37008489
This sheet also once save automatically sends an email via outlook
Well this is an other issue I think once we fix the login then you can address that in a related question.

Let me work on your list and see from there, but first do you agree on my suggestion overall ? any comments ? pls go ahead maybe issue I did not see particular to your buisness ... etc ...
Its better to jog ideas back and forth then proceed to seting up the solution rather then jumping to solution and realising thatt it is missing things and proceeding to fix it

Awaiting for your comments
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 37008599
Sorry I may be blind but the xls you posted does not have list of user names except the dummies Name1 Name2 and Name3 !!
Maybe you posted the wrong wrokbook it is anyway missing lots of macros
gowflow
0
 

Author Comment

by:Melbut
ID: 37011474
I do not have all the names that will be using this yet, that's why it only have the dummy names
0
 

Author Comment

by:Melbut
ID: 37011505
Attached is a list of names I have available at the moment and I do agree with you suggestions.
Here-are-the-names-that-I-have-a.docx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 37015138
Sorry maybe I did not express myself correctly I do not need the name of your users as such I need their login name or code to the pc their username when they log into windows ! and if when they login they choose a domain name or they login onto their own station
and finally are all the users sharing the same file or it will be several instances of the file ?
gowflow
0
 

Author Comment

by:Melbut
ID: 37086474
I am not able to provide the log in names or code to their pc at this time is there another workaround?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37086669
gowflow - If he is to be able to use this, he needs to be able to change and add names to the list.  Thus if you can provide a working version with instructions on how to chnge the names that would be useful.  If every time he adds/deletes a user he has to ask you to do it the solution is not very useful

mlmcc
0
 
LVL 29

Expert Comment

by:gowflow
ID: 37112196
Sorry Melbut I have been away could not attend. I will give your request a shot asap. Sorry again for the delay.
gowflow
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Melbut
ID: 37169947
I appreciate any help you can offer
0
 
LVL 29

Expert Comment

by:gowflow
ID: 37170495
Sorry again for the delay this has taken I have been swamped with urgent issue I am deeply sorry for the delay will give it a 'serious' look asap !

Do not loose hope !!! :)
gowflow
0
 

Author Comment

by:Melbut
ID: 37227840
thank you for taking the time in attempting a helping with a solution.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 37236426
Hi Melbut,

I wish to Appologies for this IMMENSE delay in providing you the solution. Shame on me but I was taken in several issues and felt I cold not give you the solution that was best for you due to not being able to link your users on the Domain.

I have arrange for a middle solution and I hope it will satissfy you and compensate for the lost time you encounter.

Here is some overview on the solution
=============================
The Attach file has the intial users you provided with their Calendars as well it has the Main Menu that is called like oyu did PTO Menu. Once the file is launched and macros are enabled you have a popup that will request password identification. The default user that will pop-up is the user logs into the window this can be overritten thru the combobox that will display all the users that exist in the database (I will explain later how to add users and modify this) Once the user select his/her own name they enter their password and if credential ok then they will be taken to their own Calendar for input all other Calendars are hidden and all code is password protected so the users cannot see domeonelse calendar or cannot log in as an other user. When the user finsihes working he close the workbook by the x on top corner right or file/exit then all sheets except Menu are hidden and they are prompt to save or not the existing file. There is an Admin (Administrator) for the workbook that have all the Creation/Deletion/Add privledges that normal users don't have. I created this user as Admin and the password is 'AmTheBoss' respecting the captial letters. The visual basic code as well is protected by the same password. So you need to be careful to keep this password for you (or to the administrator of the file and not give it to someone else.) This password though can also be changed only by the administrator.

How to Set it up for all users Step by Step
================================
1) Launch the file and make sure that macros are enabled
2) The form will pop-up in PTO Menu displaying the default user name logged onto that computer in the combobox. You will see your username there obviously it is not in the database (I already filled Admin, name1, Name2, Name3 .... Name15 I think you can see this in sheet Users)
3) Change the User name to Admin and type the password AmTheBoss and cpress enter then enter an other time (you see the focus move from password to OK then all sheets will Unhide revealing them for you and a new window is added with title Rest Password and a combo to choose users and a button Rest Password.
4) The logic is that all Users have a default password 'Password' so you need to tell users that when they log in the first time their password is the work Password with P capital> So when they log in the first time and choose their name they input this Password and then the system opens a new extention called Input nw Password and this is where they will input their own personal password 2 times (you can try this by selecting any user Name2 example and put 'Password' then you will see this new frame pop up. So when the users register thei passwword each time afterward they log in they will need to put their own password that they created or else they cannot access the workbook.
5) In the event the users forget their own password they need to notifiy the Admisnistrator that they forgot their own password and then the Administrator will log in to his account and will ge this new window showing Reset password he select the user then press Rest password and it will automatically rest it to the word Password. The workbook is close and save by the administrator and the users will then re-open it and put his new password as he did the first time. This is to prevent users fro mentering into other's account and tempering with it and also giving 1 person control over the group.
6) When user login is successful then if the sheet is already there in his name (the name in hte table should be EXACTLY THE SAME AS THE SHEET NAME OF HIS CALENDAR !!! this is mandatory for it to finction correctly. then he is directed to his/her own calendar for input/viewing/saving
7) You or the administrator when he/her logs in is revealed all the sheets in the workbook To access the sheets you need to put X on the frontend so you can move to the sheets click on the sheet Users and you will see all the users that are created with Admin on top and the passwords and the default and the last column should only have Y to either Admin or the user that is Admin he will have full Acess.
8) At tthis point you will need to Import all the calendars of all the users into this workbook and update manually the Usernames to match the Sheet names of the user's calendar and saaving it your in business !!!!

Please feel free to take all your time to check all situation put a wrong password, put users that don't exist in the database, select a user with correct password byt that have no sheet yet in the workbook and test all these situation and revert with comments or errors if there are any that I maybe didn not trap and I will be glad to cater for all.

Again sorry for this delay that is way over what can be accepted and hope this will compensate for the longstanding waiting.
Rgds/gowflow
UsersCalendars2011.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 37242518
Sorry for overlooking your past doc with names in your organisation. In this version I incorporated the names in your company.
Rgds/gowflow
UsersCalendars2011.xlsm
0
 

Author Comment

by:Melbut
ID: 37265431
Thank you I will try this out.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 37267236
ok and pls revert as soon as possible as things tend to be forgotten when time passes by !!
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 37296781
Any news ? Did you have a chance to try it out ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 37313134
I understand you must be swamped with work and maybe the delay was my fault at the first place, but trust you will find this solution acceptable if you give it 5 min of your time and I can show you how you can adapt it so it works for you for every year

Merry Christmas
gowflow
0
 

Author Comment

by:Melbut
ID: 37315470
gowflow,

This works great!  However, I am not able to enable the macros as the pop menu appears first. This can only be done after I enter in a password. Please see the attached screen shot


Screen-shot.docx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 37316153
Yes this is how it should be press on option then it will open a messagbox click on enable in all the links like the below snapshot and the macro will work fine.

 Run Macros
Now if you want to permanately enable macros but you have to be careful with the following as it may run any file your openeing that have macros and if some are from unknown and not safe source then you can be at risk. However if your alert and always only open files from known and trusted sources then you can do the following.

1) Click on the Office Logo on top left corner and choose Excel options like the below Image

 Excel Options1
2) Then Press on Trust Center like in the below image

 Excel Options2
3) Then Press on Trust Center Settings and press on Macros Settings and then click on Enable all Macros press OK close the workbook and start again the workbook should activate macros and links automatically.

 Excel Options3
Let me know
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 37363998
Any news ?
gowflow
0
 

Author Comment

by:Melbut
ID: 37369389
Thank you again.  So far this is working great!  

Happy New Year!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 37369850
well then close the question please.
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 37439950
Is everything running ok do you need additional help with this question ? was the answer/solution satissfactory to you ?
gowflow
0
 

Author Closing Comment

by:Melbut
ID: 37539225
I do apologize for the delay in rewarding the the points.  My account was deactivated.  the solution is satisfactory.  I appreciate the help!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 37539269
Your welcome glad I could help.
gowflow
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now