UserForm Text Box Text/Value Deletion

[arijitirf]

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.

 

[DanteAmor]

I cannot delete the final character of the value/text in TextBox

What do you mean with that?

Comment a little about what you do in the userform, that is, do you start capturing data in textbox1 and then want to delete the characters from the textbox1?

 

[DanteAmor]

Replace all your code and try the following:

Private Sub TextBox1_Change()
  Dim j As Long, i As Long
  For j = 2 To 5
    Controls("TextBox" & j).Value = ""
  Next j
  With ListBox1
    .ListIndex = -1
    If TextBox1 = "" Then Exit Sub
    For i = 0 To .ListCount - 1
      If LCase(.List(i)) Like LCase(TextBox1) & "*" Then
        .ListIndex = i
        For j = 2 To 5
          Controls("TextBox" & j).Value = .List(i, j - 1)
        Next j
        Exit For
      End If
    Next
  End With
End Sub

Private Sub UserForm_Activate()
  Dim rng As Range
  Set rng = Sheets("Master").Range("A2:E" & Sheets("Master").Range("A" & Rows.Count).End(3))
  With ListBox1
    .ColumnHeads = True
    .ColumnCount = 5
    .ColumnWidths = "28;130;50;30;100"
    .RowSource = rng.Address
  End With
End Sub

 

[arijitirf]

Sir Dante Amor,
Your code is working perfectly. Really I wrote a little what I was wanting and in spite of that you did it for me. I must say you are just awesome.

Attaching the link of my Workbook for your ready reference

Grocery Household VBA Programming.xls

Shared with Dropbox

www.dropbox.com

Requesting help on below mention topic (I don't have enough knowledge in VBA)
1) Only numeric value in TextBox 1 will be allowed else a message box popup saying "Only Numeric"
2) If the value entered in the TextBox1 is not found in the range a message box will popup saying "Not in the list"

Thank you so much Sir.

With regards,

 

[DanteAmor]

In Texbox1 only you are going to allow the values you have in column A of "Master" sheet?

 

[arijitirf]

Yes Sir. Only numeric value in Col A. And if value is not in Col A will be discarded by a pop up message box.

Thank you Sir

 

[DanteAmor]

In that case, a combobox is better.
Remove textbox1 and create combobox1.
Replace all the code with the following and test it.

Private Sub ComboBox1_Change()
  Dim j As Long, i As Long
  For j = 2 To 5
    Controls("TextBox" & j).Value = ""
  Next j
  With ListBox1
    .ListIndex = -1
    If ComboBox1.ListIndex = -1 Or ComboBox1 = "" Then Exit Sub
    .ListIndex = ComboBox1.ListIndex
    For j = 2 To 5
      Controls("TextBox" & j).Value = .List(.ListIndex, j - 1)
    Next j
  End With
End Sub

Private Sub UserForm_Activate()
  Dim sh As Worksheet, rng As Range, lr As Long
  Set sh = Sheets("Master")
  lr = sh.Range("A" & Rows.Count).End(3).Row
  Set rng = sh.Range("A2:E" & lr)
  ComboBox1.RowSource = "'" & sh.Name & "'!" & rng.Address
  '
  With ListBox1
    .ColumnHeads = True
    .ColumnCount = 5
    .ColumnWidths = "28;130;50;30;100"
    .RowSource = "'" & sh.Name & "'!" & rng.Address
  End With
End Sub