Code modification

[Mas]

Hi,

I am trying to add to list box available items based on
textbox text.

but my list box get added the same item several times.

what will be wrong with my following code?

Thanks
MAS

Private Sub TextBox1_Change()

Dim cell As Range
Dim Sh As Worksheet
Dim sString As String
If TextBox1.Text = "" Then
Label2.Caption = ""
Label1.Caption = ""
Else
Label2.Caption = "Available Title(s)"
End If
TextBox1.Text = UCase(TextBox1.Text)

Set Sh = Sheets("Define")
LR = Sh.Range("B" & Rows.Count).End(xlUp).Row

If TextBox1.Text = "" Then
ListBox1.Clear
Else
AmountOfChars = Len(TextBox1.Text)
For Each cell In Sh.Range("B2:B" & LR).Cells


sString = Left(cell.Value, AmountOfChars)

If sString = TextBox1.Text Then
ListBox1.AddItem cell.Value
End If
Next cell
End If
End Sub

 

[Rob van Gelder]

Your code appears to be retriggering the TextBox1_Change event.

Try turning off events temporarily:


Application.EnableEvents = False
TextBox1.Text = UCase(TextBox1.Text)
Application.EnableEvents = True

 

[Bob Phillips]

Hi Mas,

I think you are using a Userform, yes? If so, Rob's solution doesn't work.
You need to set a flag to stop re-entry. Here is an example

Dim fReEnter As Boolean

Private Sub TextBox1_Change()
Dim cell As Range
Dim Sh As Worksheet
Dim sString As String
Dim LR, AmountOfChars

If Not fReEnter Then
fReEnter = True
If TextBox1.Text = "" Then
Label2.Caption = ""
Label1.Caption = ""
Else
Label2.Caption = "Available Title(s)"
End If
TextBox1.Text = UCase(TextBox1.Text)

Set Sh = Sheets("Define")
LR = Sh.Range("B" & Rows.Count).End(xlUp).Row

If TextBox1.Text = "" Then
ListBox1.Clear
Else
AmountOfChars = Len(TextBox1.Text)
For Each cell In Sh.Range("B2:B" & LR).Cells
sString = Left(cell.Value, AmountOfChars)
If sString = TextBox1.Text Then
ListBox1.AddItem cell.Value
End If
Next cell
End If
Else
fReEnter = False
End If
End Sub

By the way, is Change the correct event to use? Are you trying to restrict
to 1 char? If so, use KeyDown and don't allow more than 1.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Rob van Gelder]

Bob. Nicely spotted. I wasn't concentrating..

Application.EnableEvents has no effect while within UserForms.