• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Opening file by username & password

A

Amit Jain

New Member
Is is possible to create a macro in excel so that as soon as the file is opened it asks for username & password with submit & cancel button. There will be two users with usernames "Suraj" & "Praveen" and password "shell" & "petronas" respectively. When the user clicks submit with valid credentials they will be able to access the excel sheet & their user name along with date & time of login will be stored on "K" row.
Thanks for reading & would be great if someone helps it through.
 
Yes Amit, this can be done using the workbooks.open module

You have to add a userform to this module to track the user ID & password and then if valid credentials then pass the details to column K.

Let me know if you require any help in building the macro code.
 
Ok, so firstly you will need to add a userform, to do that below is the procedure:

1. Goto the visual editor(alt+F11) - insert - userform
2. If you are not able to see the toolbox then goto View-Toolbox
3. From the toolbox add a combo box & a textBox alongwith two buttons.
4. Caption the buttons as desired (Submit, Cancel is what I have)
5. Now double click on thisworkbook from the rightpane add the below code to it

Code: 
Private Sub Workbook_Open()
UserForm1.Show
End Sub

6. Now rightclick on the userform1 module from the right pane and select view code and paste the below in it

Code: 
Private Sub UserForm_Initialize()
With UserForm1
  .ComboBox1.AddItem ("Suraj")
  .ComboBox1.AddItem ("Praveen")
End With
End Sub
Private Sub CommandButton1_Click() 'submit button
If UserForm1.ComboBox1.Value = "Suraj" Then
  If UCase(UserForm1.TextBox1.Value) = "SHELL" Then
  Unload Me
  Worksheets(1).Range("K1").Value = "NAME: Suraj  TIME: " & Format(Now(), "DD-MMM-YYYY HH:MM:SS")
  Else
  Unload Me
  ActiveWorkbook.Close False 'delete this row if you dont want to close the workbook
  End If
ElseIf UserForm1.ComboBox1.Value = "Praveen" Then
  If UCase(UserForm1.TextBox1.Value) = "PETRONAS" Then
  Unload Me
  Worksheets(1).Range("K1").Value = "NAME: Praveen  TIME: " & Format(Now(), "DD-MMM-YYYY HH:MM:SS")
  Else
  Unload Me
  ActiveWorkbook.Close False 'delete this row if you dont want to close the workbook
  End If
Else
End If
End Sub
Private Sub CommandButton2_Click() 'cancel button
Unload Me
ActiveWorkbook.Close False 'delete this row if you dont want to close the workbook
End Sub

7. Now save the file and try using the code.

This is quite a basic approach, there is lots more that can be done...:)
 
Do note that this method is not as secure as using the built in feature of XL requiring a password to open a file. Someone would be able to bypass the Workbook_Open event simply by disabling macros.
 
@Luke M
Hi, buddy!
Many many years ago Groucho Marx said "military intelligence is a contradiction in terms". And that was because Excel hadn't arrived yet, otherwise he'd have surely changed to "Excel security is a contradiction in terms".
Regards!

Hi, Abhijeet!
Maybe you want to give a look at this. In one of the sublinks there's a method to avoid (a bit, not bullet proof) the trick of disabling macros that Luke M mentioned.
http://chandoo.org/forum/threads/wh...t-method-to-secure-workbook.11962/#post-70156
Regards!
 
code is working but no new record is being recorded.
the new record is being replacing the old record.
want to save all the logins
 
You must log in or register to reply here.
Back
Top