Hi!
In my UserForm, I cannot delete the final character of the value/text in TextBox (TextBox is linked with a ListBox which is linked with a WorkSheet). Below is code which I have been using in my UserForm and Module.
UserForm Code:
Private Sub TextBox1_Change()
GetData
Dim s As String
Dim i As Integer
Dim flg As Boolean
s = TextBox1.Text
Dim rng As Range
With Sheets("Master")
'Set rng = Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
Set rng = Worksheets("Master").Range("A2:E123")
End With
With UserForm1.ListBox1
.ColumnHeads = True
.ColumnCount = 5
.ColumnWidths = "28;130;50;30;100"
.RowSource = rng.Address
End With
For i = 0 To ListBox1.ListCount - 1
'use the LIKE operator to compare
'convert both to Uppercase as well so case does not matter
If UCase(ListBox1.List(i, 0)) Like UCase(s) & "*" Then
ListBox1.ListIndex = i
flg = True
Exit For
End If
Next
If Not flg Then
MsgBox "Not in the list"
Else
End If
End Sub
Module Code:
Sub GetData()
Sheet2.Activate
If IsNumeric(UserForm1.TextBox1.Value) Then
flag = False
i = 0
id = UserForm1.TextBox1.Value
Do While Cells(i + 1, 1).Value <> ""
If Cells(i + 1, 1).Value = id Then
flag = True
For j = 2 To 5
UserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Value
Next j
End If
i = i + 1
Loop
If flag = False Then
For j = 2 To 5
UserForm1.Controls("TextBox" & j).Value = ""
Next j
End If
Else
ClearForm
End If
End Sub
Sub ClearForm()
Sheet2.Activate
For j = 2 To 5
UserForm1.Controls("TextBox" & j).Value = ""
Next j
End Sub
Please help. Thanks in advance.
In my UserForm, I cannot delete the final character of the value/text in TextBox (TextBox is linked with a ListBox which is linked with a WorkSheet). Below is code which I have been using in my UserForm and Module.
UserForm Code:
Private Sub TextBox1_Change()
GetData
Dim s As String
Dim i As Integer
Dim flg As Boolean
s = TextBox1.Text
Dim rng As Range
With Sheets("Master")
'Set rng = Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
Set rng = Worksheets("Master").Range("A2:E123")
End With
With UserForm1.ListBox1
.ColumnHeads = True
.ColumnCount = 5
.ColumnWidths = "28;130;50;30;100"
.RowSource = rng.Address
End With
For i = 0 To ListBox1.ListCount - 1
'use the LIKE operator to compare
'convert both to Uppercase as well so case does not matter
If UCase(ListBox1.List(i, 0)) Like UCase(s) & "*" Then
ListBox1.ListIndex = i
flg = True
Exit For
End If
Next
If Not flg Then
MsgBox "Not in the list"
Else
End If
End Sub
Module Code:
Sub GetData()
Sheet2.Activate
If IsNumeric(UserForm1.TextBox1.Value) Then
flag = False
i = 0
id = UserForm1.TextBox1.Value
Do While Cells(i + 1, 1).Value <> ""
If Cells(i + 1, 1).Value = id Then
flag = True
For j = 2 To 5
UserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Value
Next j
End If
i = i + 1
Loop
If flag = False Then
For j = 2 To 5
UserForm1.Controls("TextBox" & j).Value = ""
Next j
End If
Else
ClearForm
End If
End Sub
Sub ClearForm()
Sheet2.Activate
For j = 2 To 5
UserForm1.Controls("TextBox" & j).Value = ""
Next j
End Sub
Please help. Thanks in advance.