Search and Populate Matching Text

If you need to search your text and populate a list box based on the matching texts then here is a simple trick.

The list box will refresh after every new character typed in the text box and populate the list box with all matching characters.

The same can be used to populate a combo box or storing the matching texts in an array.

Data :

Fig: Data Structure

Search :

Fig: Search Sheet

Code :


Private Sub txt_item_Change()
    
    If txt_item.Text = "" Then
        lb_item.Clear
        search.Range("F2").Value = 0
        Exit Sub
    End If
    
    Dim rowCount, count, i As Long
    Dim colno As Integer
    
    search.lb_item.Clear
    
    colno = CInt(search.Range("E11").Value)
    rowCount = data.Range(search.Range("D11").Value & Rows.count).End(xlUp).Row
    count = 0
    
    For i = 2 To rowCount
        If InStr(1, Left(CStr(data.Cells(i, colno).Value), Len(txt_item.Text)), txt_item.Text, 1) Then
            search.lb_item.AddItem data.Cells(i, colno).Value
            count = count + 1
        End If
    Next i
    
    search.Range("F2").Value = count
    

End Sub

However to use this type of search for a data source exceeding 1 million records optimization is required. Indexing before search would be best in that case. Will update the same in the next blog.

Example File Link

Simple Login Screen using VBA

Here is a simple example to create a login screen to validate user credentials in excel

Step 1:

Create a simple user form with required text boxes and buttons

Step 2:
Write the following code for the respective buttons:

Private Sub cmd_login_Click()
    
    If txt_user.Text = "" Or txt_pass.Text = "" Then
        MsgBox "User ID or password field cannot be blank", vbCritical, "Invalid Login"
        Exit Sub
    End If
    '=========================
    '       USER ID's
    '=========================
    Dim userID(10) As String
    userID(0) = "sunny"
    userID(1) = "guest"
    
    '=========================
    '       PASSWORDs
    '=========================
    Dim passID(10) As String
    passID(0) = "admin"
    passID(1) = "guest"
    
    Application.DisplayAlerts = False
    Dim i As Integer
    Dim userName, passWord As String
    Dim userStatus, passStatus As String
    Dim loginStatus As String
    userStatus = passStatus = ""
    userName = txt_user.Text
    passWord = txt_pass.Text
    For i = 0 To UBound(userID)
        If userName = userID(i) Then
            userStatus = "Found"
            Exit For
        End If
    Next i
    If userStatus = "Found" Then
        If passWord = passID(i) Then
            passStatus = "Found"
            Call cmd_reset_Click
            frm_login.Hide
            welcomescreen.Visible = xlSheetVisible
            lockScreen.Visible = xlSheetVeryHidden
            Call updateUserDetails(userName)
            ActiveWorkbook.Save
            Application.DisplayAlerts = True
        Else
            MsgBox "Incorrect username or password.", vbCritical, "Login Failed"
            Call cmd_reset_Click
        End If
    Else
        MsgBox "Incorrect username or password.", vbCritical, "Login Failed"
        Call cmd_reset_Click
    End If
End Sub

Private Sub cmd_reset_Click()
    txt_user.Text = ""
    txt_pass.Text = ""
    txt_user.SetFocus
End Sub

Private Sub cmd_cancel_Click()
    Application.DisplayAlerts = False
    frm_login.Hide
    ActiveWorkbook.Close
End Sub

Step 3:
Add these lines of code for hiding whats there in your excel file. So that the user cannot see the stuff behind the login screen before logging into the file. lockScreen is the sheetname of the worksheet we'll use as a default screen.

Private Sub UserForm_Activate()
    lockScreen.Visible = xlSheetVisible
    lockScreen.Select
End Sub

Private Sub UserForm_Terminate()
    ActiveWorkbook.Close
    lockScreen.Visible = xlSheetVeryHidden
End Sub

The user id password for using the example file is:
user id: sunny
password: admin

 

It also keeps track of all the user login instances with time stamp. In a hidden log Sheet.

Link for the example file