Interactive User Form Data retreval

Hello,

I'm trying to learn more VBA and have a problem, proberbly easy but here we go....

I have a interactive user form and when i reun it i can only search for Numbers (see atatched)

I have tried changing the code from IsNumeric to IsText but doesnt work, what am I missing here?

Also for some reason if i enter more than 6 charecters it gives me a error.

Thanks for looking if you do.

D

Slightly different approach here. Rather than looping through until you find a match, this just uses FIND to locate the row and recalls the data if found.

Sub GetData()
    Dim RecallRow As Long
    Dim rng As Range
        
    With ThisWorkbook.Sheets("Sheet1")
        On Error Resume Next
        Set rng = .Range("A:A").Find(UserForm1.TextBox1, LookIn:=xlValues, LookAt:=xlWhole)
        On Error GoTo 0
        If Not rng Is Nothing Then
            RecallRow = rng.Row
            UserForm1.TextBox4 = Format(.Cells(RecallRow, "D").Value, "dd/mm/yyyy")
            UserForm1.TextBox2 = .Cells(RecallRow, "B")
            UserForm1.TextBox3 = .Cells(RecallRow, "C")
        End If
    End With
End Sub

Any use?

BSB

I copied the code in and it works great, can't say i totally understand it but it works......

thankyou

Hi just had a run through and now i get an error when i press the Edit/Add Button.

D

Originally Posted by deanblew

Hi just had a run through and now i get an error when i press the Edit/Add Button.

Sorry, I didn't look at that bit of code to test for errs.

I have to go out for a while now but will come back to it when I return if nobody else has stepped in.

BSB

Added some comments in green to explain what it's all doing.

Sub GetData()
    Dim RecallRow As Long
    Dim rng As Range
        
    With ThisWorkbook.Sheets("Sheet1") 'START WITH BLOCK MEANING THE EVERY ACTION BETWEEN HERE AND THE END WITH IS PERFORMED ON SHEET1 UNLESS STATED OTHERWISE
        On Error Resume Next 'STOPS THE NEXT LINE THROWING UP AN ERROR WHEN THE VALUE TYPED IS NOT FOUND
        Set rng = .Range("A:A").Find(UserForm1.TextBox1, LookIn:=xlValues, LookAt:=xlWhole) 'SETS THE rng VARIABLE BY LOOKING FOR THE VALUE IN TextBox1 IN COLUMN A ON SHEET 1.  IF FOUND THEN rng = THE CELL REFERENCE IT WAS FOUND IN.
        On Error GoTo 0 'RESETS THE ERROR NUMBER TO 0.  YOU CAN RESEARCH THIS FOR MORE DETAIL
        If Not rng Is Nothing Then 'IF THE A&A NUMBER WAS FOUND THEN DO THE NEXT BIT, IF NOT THEN DON'T
            RecallRow = rng.Row 'SETS THE ROW TO RECALL FROM TO THE ROW THE A&A NUMBER WAS FOUND IN
            UserForm1.TextBox4 = Format(.Cells(RecallRow, "D").Value, "dd/mm/yyyy") 'RECALLS THE DATE
            UserForm1.TextBox2 = .Cells(RecallRow, "B") 'RECALLS THE NAME
            UserForm1.TextBox3 = .Cells(RecallRow, "C") 'RECALLS THE CITY
        End If
    End With
End Sub

BSB

Add the below to the beginning of the EditAdd sub:

Dim id As String

BSB