Use text file to hold usernames and then VBA to read the file to check if the user is list

I'm trying to create an excel workbook that when opened, looks at a text file to check the environ("username") exists in the text file. My vba skills are quite good, but have never had to do anything referring to another file.

Why not simply store the list on a very hidden worksheet in the workbook itself...?

Would make things a whole lot simpler.

cytop, thanks for the reply - yes I did think about it. However, I will eventually have a vast number of workbooks that will require the username check and I will need to add users to the list over time.

Then again, you may want to maintain the file outside of Excel... *

To prevent a name like 'Vic' matching 'Victor', each name should be on it's own line with a space before and after. The

Option compare Text

statement is also required. This goes at the top of the module the function is added to and makes text comparisons case insensitive.

Option Explicit
Option Compare Text

Public Function CheckName() As Boolean

   Const NameFile As String = "c:\temp\names.txt"
   Dim iFile As Integer
   Dim strNames As String
   
  On Error GoTo Catch
   
   iFile = FreeFile
   Open NameFile For Input As #iFile
   strNames = Input(LOF(iFile), #iFile)
   Close #iFile
   
   CheckName = InStr(strNames, " " & Environ("UserName") & " ") > 0
   Exit Function
   
Catch:
   
   CheckName = False
   
End Function

It's a simple call to check:

If Not CheckName() Then
    Thisworkbook.close SaveChanges:=xlDoNotSaveChanges
End If

______________________________________________________________

* Always helps to think things through (me, that is)

Cytop, many thanks for your invaluable help. With a slight tweek, this code has worked perfectly. Thank you.

Slight tweak was to name the Name file with the same root name of the workbook so that each workbook has its own list of names?