Login

[Robin]

Hello guys

I need help creating a macro that would act as a login form for the workbook.
the macro would validate the id and password from another workbook.
Can anyone please help me with this?

Thanks and Regards
Robin

 

[Joel]

sub CheckLogin

PassWordFName = "C:\MyFolder\Password.xls"

Account = inputBox("Enter Account : ")
PassWd = inputbox("Enter Password : ")

'Open Password File
set PassWdBk = workbooks.open(filename:=PassWordFName)
with PassWdBk.Sheets("Sheet1")
set CheckAccnt = .Columns("A").Find _
(what:=Account,lookin:=xlvalues,lookat:=xlwhole)
if CheckAccnt is nothing then
msgbox("Did not find account")
else
OldPassword = CheckAccnt.offset(0,1).value 'get password from col B
if PassWd = OldPassword then
msgbox("Password Matched")
else
msgbox("Password did not match")
end if
end if
end with

PassWdBk.close savechanges:=False
end sub

 

[Robin]

Hello again Joel
is it possiable to modify the code so that the macro automatically runs when
the workbook is opened and displays the sheets only when the correct account
and password are entered?

also, how can i hide a spreadsheet in a way that when every someone tries to
unhide it, he/she is required to input a password?(i believe there is an
inbuilt function which allows us to do this) or lock a workbook so that a
password is required to access it?

Thanks and Regards
Robin

 

[Joel]

Something like this? You would need to protect the VBA code so people cannot
find the password and you would need to protect the workbook when it is
closed.

Sub Workbook_open()
PassWordFName = "C:\MyFolder\Password.xls"

Account = InputBox("Enter Account : ")
PassWd = InputBox("Enter Password : ")

'Open Password File
Set PassWdBk = Workbooks.Open(Filename:=PassWordFName)
With PassWdBk.Sheets("Sheet1")
Set CheckAccnt = .Columns("A").Find _
(what:=Account, LookIn:=xlValues, lookat:=xlWhole)
If CheckAccnt Is Nothing Then
MsgBox ("Did not find account")
Else
OldPassword = CheckAccnt.Offset(0, 1).Value 'get password from col B
If PassWd = OldPassword Then
For Each sht In ThisWorkbook.Sheets
sht.Unprotect Password:="ABC"
Next sht
Else
MsgBox ("Password did not match")
End If
End If
End With

PassWdBk.Close savechanges:=False

End Sub

 

[Robin]

yup its something like that, but there r a few problems. after i have
finished running the macro, the sheet becomes unprotected and it stays
unprotected even after i close the workbook and restart it. so is there any
way i can change the sheet to protected when i close the workbook?

also could u please tell me how i can protect the VBA code?
and is there anyway i can hide a sheet so that it requires me to input a
password before i can unhide it?

Thanks and Regards
Robin

 

[Joel]

To protect the code, In the VBA Projects window (left side of VBA window)
right click the Module Folder (or sheet where code is located) . select
Properties - Protection - Lock for Viewing.

You also need a workbook save envent to protect the workbook when closing.

Private Sub WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, Cancel As Boolean)

For Each sht In ThisWorkbook.Sheets
sht.protect Password:="ABC"
Next sht
End Sub