Excel VBA: Make InStr find whole string, not part of it

Question

I'm trying to make a login/register system.

This is the registration UserForm.

Private Sub regReg_Click()
Dim TextFile As Integer
Dim FilePath As String

If regAParole.Text = "aparole" Then
    FilePath = ThisWorkbook.Path & "\accounts.txt"
    TextFile = FreeFile

    Open FilePath For Append As #1

    Print #TextFile, regID; regAmats; regParole

    Close TextFile

    MsgBox ("Registracija veiksmiga.")
    Unload Registracija
Else
    MsgBox ("Nepareiza administratora parole.")
End If
End Sub

The "aparole" thing is basically just a keyword to enter in a field so only administrators can create new accounts.

accounts.txt content looks like:

1DirectorPassword (ID+jobposition+password)

This is the authentication:

Private Sub logAuth_Click()

Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String
Dim find As String
Dim result As Integer

find = logID & logAmats & logParole
FilePath = ThisWorkbook.Path & "\accounts.txt"
TextFile = FreeFile
Open FilePath For Input As TextFile
FileContent = Input(LOF(TextFile), TextFile)
result = InStr(FileContent, find)


If result >= 1 Then

    MsgBox ("Autorizacija veiksmiga!") ' Success
    Unload Autorizacija
End If

Basically when logging in I search within the accounts.txt for the string combo (ID+jobposition+password) which I use when registering. So in general the approach works, but:

If I enter everything perfectly matched = works great

If I enter the password half of it, like in a format of = 1DirectorPass it still works, so basically how can I tell to only search for the whole string and not parts of it?

I think the issue lies within InStr...

Is "1DirectorPassword " a literal string, or is it actually something variable? And are the brackets that follow literal? It is not clear which is the format. — trincot
– trincot, Commented May 7, 2016 at 7:10
I have a UserForm with 3 TextBoxes. ID: textbox, JobPosition: textbow(with dropdown choices) and Password: textbox - whatever gets entered is saved into accounts.txt in a format of IDJobPositionPassword (1Directorpassword) Each entry is in a single line. — user3688039
– user3688039, Commented May 7, 2016 at 7:15
What you put between brackets is an example, right? The brackets are not actually there? — trincot
– trincot, Commented May 7, 2016 at 7:18

trincot · Accepted Answer · 2016-05-07 07:17:14Z

1

You could test for the newline markers in your file content, like this:

result = InStr(vbCrLf & FileContent, vbCrLf & find & vbCrLf)

This will only match complete lines. An extra newline is added before the file content so also the first line can be matched. At the end of the file content you would already have a vbCrLf character, because Print is supposed to add that.

answered May 7, 2016 at 7:17

trincot

357k38 gold badges282 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA: Make InStr find whole string, not part of it

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related