VBA lookup i can only enter numbers and not letters please help

[Patriot2879]

Hi i have the code below where it does a search but it will only let me enter numbers in the textbox and not letters, but i want to be able to input both numbers and letters can you help please, i am very new to VBA and still learning, i have searched for the answer alot on internet with no success so far, really hope you can help me.

Sub GetData()

If IsNumeric(UserForm1.TextBox1.Value) Then
    flag = False
    i = 0
    id = UserForm1.TextBox1.Value
    
    Do While ThisWorkbook.Worksheets("Sheet2").Cells(i + 1, 1).Value <> ""
    
        If ThisWorkbook.Worksheets("Sheet2").Cells(i + 1, 1).Value = id Then
            flag = True
            For j = 2 To 3
                UserForm1.Controls("TextBox" & j).Value = ThisWorkbook.Worksheets("Sheet2").Cells(i + 1, j).Value
            Next j
        End If
        
        i = i + 1
        
    Loop
    
    If flag = False Then
        For j = 2 To 3
            UserForm1.Controls("TextBox" & j).Value = ""
        Next j
    End If
    
Else
    ClearForm
End If

End Sub

 

[Patriot2879]

Sorry can i send you the file at all? if i can how do i do that? thank you again

 

[Fluff]

You can uploaded it to OneDrive or Dropbox, mark it for sharing & then post the link to the thread

 

[Patriot2879]

hi this is all the code in Sheet1

Option Explicit

Dim id As Long, i As Long, j As Integer, flag As Boolean

Sub GetData()

If Len(UserForm1.TextBox1.Value) > 0 Then
    flag = False
    i = 0
    i = UserForm1.TextBox1.Value
    
    Do While ThisWorkbook.Worksheets("Sheet2").Cells(i + 1, 1).Value <> ""
    
        If ThisWorkbook.Worksheets("Sheet2").Cells(i + 1, 1).Value = id Then
            flag = True
            For j = 2 To 3
                UserForm1.Controls("TextBox" & j).Value = ThisWorkbook.Worksheets("Sheet2").Cells(i + 1, j).Value
            Next j
        End If
        
        i = i + 1
        
    Loop
    
    If flag = False Then
        For j = 2 To 3
            UserForm1.Controls("TextBox" & j).Value = ""
        Next j
    End If
    
Else
    ClearForm
End If
End Sub

Sub ClearForm()

For j = 1 To 3
    UserForm1.Controls("TextBox" & j).Value = ""
Next j

End Sub

Sub EditAdd()

Dim emptyRow As Long

If UserForm1.TextBox1.Value <> "" Then
    flag = False
    i = 0
    id = UserForm1.TextBox1.Value
    emptyRow = WorksheetFunction.CountA(ThisWorkbook.Worksheets("Sheet2").Range("A:A")) + 1
    
    Do While ThisWorkbook.Worksheets("Sheet2").Cells(i + 1, 1).Value <> ""
    
        If ThisWorkbook.Worksheets("Sheet2").Cells(i + 1, 1).Value = id Then
            flag = True
            For j = 2 To 3
                ThisWorkbook.Worksheets("Sheet2").Cells(i + 1, j).Value = UserForm1.Controls("TextBox" & j).Value
            Next j
        End If
        
        i = i + 1
        
    Loop
    
    If flag = False Then
        For j = 1 To 3
            ThisWorkbook.Worksheets("Sheet2").Cells(emptyRow, j).Value = UserForm1.Controls("TextBox" & j).Value
        Next j
    End If
    
End If

End Sub

 

[Patriot2879]

sorry in MODULE not sheet1

 

[Patriot2879]

this is code in UserForm1

Option Explicit

Private Sub UserForm_Initialize()

TextBox1.SetFocus

End Sub

Private Sub TextBox1_Change()

GetData

End Sub


Private Sub CommandButton2_Click()

ClearForm

End Sub

Private Sub CommandButton3_Click()

Unload Me

End Sub

 

[Patriot2879]

this is the code in Sheet1

Option Explicit

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

 

[Fluff]

Try changing

Dim id As Long

to

Dim id As String

Also when posting code please use code tags (the # icon in the reply window).

 

[Patriot2879]

hiya thanks again for help, that new bit of code does stop all the errrors and now allows me to enter the text and numbers, but it doesnt find the data now in sheet 2, and enter manually into other 2 text boxes

 

[Patriot2879]

will will add the

from now on many thanks, still a beginner on it all but thank you som muxh for all the help i am getting

 

[Fluff]

hiya thanks again for help, that new bit of code does stop all the errrors and now allows me to enter the text and numbers, but it doesnt find the data now in sheet 2, and enter manually into other 2 text boxes

Without knowing what you are putting in the textbox, or what your data looks like, it is very difficult to help.