Excel Macro with Combo Box

This is a very basic example but it should give you an idea of how this can be accomplished. The example limits the update to the first 100 rows of column A.

Private Sub ComboBox1_AfterUpdate()
Dim Add_Text As String
Dim i As Integer

Add_Text = ComboBox1.SelText

For i = 1 To 100
If Sheet1.Cells(i, "A").Text <> "" Then
Sheet1.Cells(i, "A").Value = Sheet1.Cells(i, "A").Text + " " + Add_Text
End If
Next I
End Sub

Do you mean that you want to prevent duplicate text? Will you also need to add additional text such as:

12345 Test Passed?

Or will you only be adding a single value?

Ok, try this:

Public Class Form1
Dim blnUpdated As Boolean = False

Private Sub ComboBox1_AfterUpdate()
Dim Add_Text As String
Dim i As Integer

If blnUpdated Then
Exit Sub
End If

Add_Text = ComboBox1.SelText

For i = 1 To 100
If Sheet1.Cells(i, "A").Text <> "" Then
Sheet1.Cells(i, "A").Value = Sheet1.Cells(i, "A").Text + " " + Add_Text
End If
Next I
blnUpdated = True
End Sub

Sorry, the "Public Class" statement assumes that you have a form called Form1 in your program. If you do, then add "End Class" at the very last line of the program (after the "End Sub" statement).

However, I am going to assume that you already have a class definition so what you really need to do is just add the Dim statement for blnUpdated to a public area of your program (usually immediately after the first statement in the program).

Is this the entire piece of code? What confuses me is the "Unload Me" statement which implies that there is a form or a dialog that has been displayed.

The reason that you keep getting duplicate code is that the subroutine begins each time by setting blnUpdate to False. Therefore, the If statement that checks its value will never evaluate to True. This allows the subroutine to alter the cell contents repeatedly. You must declare and set blnUpdate outside of the cmdAdd_Click subroutine. This means that blnUpdate must be declared as a public variable.

Ahh! If you are closing the form, then declaring the blnUpdate variable publicly will not work either because it will be reset to False each time the form is reloaded. The only thing I can think of in this case would be to use an empty cell (perhaps hidden) in the workbook in place of blnUpdate.

Assuming that cell B1 is empty, you could try something like this:

Option Explicit

Public Sub cmdAdd_Click()

Dim Add_Date As String
Dim Add_Text As String
Dim i As Integer

If Sheet1.Cells(1, "B").Text = "1" Then
Exit Sub
End If

Add_Text = ComboBox1.SelText
Add_Date = txtdate.Text

For i = 1 To 100
If ActiveSheet.Cells(i, "A").Text <> "" Then
ActiveSheet.Cells(i, "A").Value = ActiveSheet.Cells(i, "A").Text + " " + Add_Text
End If
Next i
Sheet1.Cells(1, "B").Value = "1"

Unload Me
End Sub

Always happy to help.