none
Textbox_Change property to call function only when completely provided RRS feed

  • Question

  • Hi,

    I have a textbox where the user will enter a value and then it searches the worksheet to find the value & if found returns the other textbox values. 

    The code calls the search function everytime a value is entered. How do I fix it? There is no button click to call the search function. Asap the value is entered in textbox1 it must find the next values. 

    Private Sub textbox1_change()
    If TextBox10 = "" Then
    MsgBox "Provide value of relationship accepted"
    Else
    SearchtextboxVal
    End If
    End Sub
    The search function is 
    ub SearchtextboxVal()
         
        Dim TestAPx As String
        Dim FoundRange As Range
        Dim apxfrom As Integer
        TestAPx = TextBox1.Value
        
        Dim indlist As Worksheet
        Set indlist = ThisWorkbook.Worksheets("List")
         
        Set FoundRange = indlist.Cells.find(what:=test9, LookIn:=xlFormulas, LookAt:=xlWhole)
         
        If FoundRange Is Nothing Then
            TextBox2.Text = "not found"
            TextBox3.Text = "not found"
            TextBox4.Text = "not found"
            TextBox5.Text = "not found"
    
        Else
               TextBox2.Text = FoundRange.Offset(0, 1).Value
           TextBox3.Text = FoundRange.Offset(0, 2).Value
            TextBox4.Text = FoundRange.Offset(0, 3).Value
            TextBox5.Text = FoundRange.Offset(0, 4).Value
    
        End If
     
    End Sub

    I have tried cancel,keydown etc. On tab key it jumps to textbox20 where it will call another search function. 

    Thanks


    NewBInVB


    Friday, November 11, 2016 11:41 AM

Answers

  • Use the AfterUpdate event of the text box instead of the Change event.

    The Change event occurs whenever the user enters or deletes a character in the text box; the AfterUpdate event occurs when the user has finished editing the value and moves to another control.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by NewBInCoding Friday, November 11, 2016 12:03 PM
    Friday, November 11, 2016 11:52 AM