login username

[Ranjit kurian]

I have created a txt file which contain employees NT names

I need a macro, so that when ever the person try to open my excel file, it
should check the NT name of his/her in txt file,and if the NT name exist in
txt file he should be allowed to open my excel file...

 

[Mike H]

Hi,

I think the code below will do what you want but a couple of cautionary
notes. Any macro based solution is dependent upon the user enabling macros
and if you want to see how difficult that is then Google it or search these
forums. In addition what is to stop someone editing you text file. If you're
tring to keep information confidential except for privileged users then this
isn't a solution many would have faith in.

Private Sub Workbook_Open()
usrname = Environ("Username")
Open "C:\users.txt" For Input As 1 'edit to your path/file
Do While Not EOF(1)
Input #1, authname
If authname = usrname Then
MsgBox "Hello " & usrname & " OK to open file"
GoTo 100
End If
Loop
Close #1
MsgBox " Not authorised"
ThisWorkbook.Close savechanges:=False
100:
Close #1
End Sub


Mike

 

[Arvi Laanemets]

Hi

Some sugestions for OP.

Instead keeping allowed users in text file, write them into this Open event,
p.e into an array - you can define and fill it at top of procedure, so it'll
be easy to edit in future. P.e.

Private Sub Workbook_Open()

Dim arrUsers(3) As Variant
Dim intUsers As Integer, i As Integer
Dim usrname As String

arrUsers(1)="First user"
arrUsers(2)="Second user"
arrUsers(3)="Third user"
intUsers=3

usrname = Environ("Username")

For i=1 To intUsers
If arrUsers(i) = usrname Then
MsgBox "Hello " & usrname & " OK to open file"
GoTo 100
End If
Next i
MsgBox " Not authorised"
ThisWorkbook.Close savechanges:=False
100:
End Sub

To avoid the user bypassing macros, make all essential sheets very hidden,
with only a dummy sheet with warning about unauthorizaed access visible. Add
a couple of code rows at end of sub (before End Sub), which make right
sheets visible and hide the dummy sheet, when allowed user opens the file.
And the last step will be to protect your VBA-Project with password.

Of course a determinated and skillfull user can hack it without any
problems, but not an average Joe

 

[Arvi Laanemets]

I forget to add, you must write a BeforeClose event too, where dummy sheet
is made visible, and all other sheets very hidden again.

 

[Mike H]

Arvi,

I forget to add, you must write a BeforeClose event too, where dummy sheet
is made visible, and all other sheets very hidden again.

And then you must save it or the changes won't take effect which is very
high risk if an authorised user is closing without saving because he has just
trashed all the data in the workbook.

Mike

 

[Ranjit kurian]

Hi

this code is really good, but i should always open my txt file to input the
name, inspite of that can u do as below

i open txt file only when i require to add any new user, the below macro
should not ask to open txt file, rather it should check behind whether the
name exists or not , if the name exist then it should open my file.

The logic is, txt file is our master file which will be updated by the
developers, when we type the NT name in txt file ,only then the end user
should be able to openthe xls file...

 

[Ranjit kurian]

Hi

its working fine(without opening my txt file)

actually i have saved my txt file in web server (URL;http://.........) so my
macro should check the name form web link behind

 

[Mike H]

Hi,

The OPEN statement in this case doesn't physically open the file as you do
when you open (say) and Excel workbook it simply makes it accessible for
reading and writing depending on what access mode you set.


Mike

 

[Ranjit kurian]

Hi Mike,
thanks,

now your code is looking to txt file, if i save the same detail in
userdetail.xls file, can you please advise me how to change the code...