Why does the list get bigger ?

[Corey]

The below code populates a Combobox from the selected value in Textbox2.

But i found if i change the value in Texbox2, the List STILL has the other
values from the previous list adding them to the new list.

How can i make sure when/If a value in Textbox2 is modified ONLY those
vlaues are listed ?


Private Sub TextBox2_Change()
Application.ScreenUpdating = False
With ActiveWorkbook.Worksheets("names")
On Error Resume Next
For i = 3 To 21
If .Cells(i, Val(TextBox2.Value)).Value <> "" Then
ComboBox3.AddItem .Cells(i, Val(TextBox2.Value))

End If
Next i
End With
Application.ScreenUpdating = True
End Sub


Corey....

 

[Corey]

Worked it out ended up with

Private Sub TextBox2_Change()
Application.ScreenUpdating = False
With ActiveWorkbook.Worksheets("names")
On Error Resume Next
For i = 3 To 21
If .Cells(i, Val(TextBox2.Value)).Value <> "" Then
Combobox3.Clear ' <==== Added this line here
ComboBox3.AddItem .Cells(i, Val(TextBox2.Value))

End If
Next i
End With
Application.ScreenUpdating = True
End Sub

 

[Tom Ogilvy]

Private Sub TextBox2_Change()
Application.ScreenUpdating = False
Combobox1.Clear '<== added
With ActiveWorkbook.Worksheets("names")
On Error Resume Next
For i = 3 To 21
If .Cells(i, Val(TextBox2.Value)).Value <> "" Then
ComboBox3.AddItem .Cells(i, Val(TextBox2.Value))

End If
Next i
End With
Application.ScreenUpdating = True
End Sub

 

[Tom Ogilvy]

Guess you are using Combobox3

Private Sub TextBox2_Change()
Application.ScreenUpdating = False
Combobox3.Clear '<== added
With ActiveWorkbook.Worksheets("names")
On Error Resume Next
For i = 3 To 21
If .Cells(i, Val(TextBox2.Value)).Value <> "" Then
ComboBox3.AddItem .Cells(i, Val(TextBox2.Value))

End If
Next i
End With
Application.ScreenUpdating = True
End Sub

After I posted, I saw you came up with something similar, although I don't
know why you would loop through that range and remove anything you had just
added. That is what you are doing with the clear inside the loop - but
maybe that is what you really want.