Change function from on button click to when value entered

fI seem to always have to start these with I don't know anything about coding so please bare with me.

I have some code that does a funtion on a button click that looks like below

Private Sub btn_Add_Click()
Worksheets("Main").Unprotect
Dim TargetCell As Range
If WorksheetFunction.CountIf(Sheets("Main").Columns(4), TextBox1.Value) = 1 Then
    Set TargetCell = Sheets("Main").Columns(4).Find(TextBox1.Value, , xlValues, xlWhole).Offset(0, 1)
    TargetCell.Value = TargetCell.Value + 1
    TextBox1.Value = ""
    TextBox1.SetFocus
Else
    MsgBox "Item Not Found"
End If
Worksheets("Main").Protect
End Sub

However, I would rather the function above be achieved when text is entered. I have the following code that functions this way but does something different. I tried to see if I can have the action do what it does above but without a button click.

Dim IsActive As Boolean

Sub CopyToCell()
Worksheets("Main").Unprotect
If UserForm2.TextBox1.Text = "" Then Exit Sub

    With Worksheets("Main")
        If Application.CountIf(.Range("D:D"), UserForm2.TextBox1.Text) = 0 Then
        .Range("D" & .Rows.Count).End(xlUp)(2).Value = UserForm2.TextBox1.Text
            Else
            MsgBox "That Item Already Exists"
        End If
    End With

UserForm2.TextBox1.Text = ""
UserForm2.TextBox1.SetFocus
IsActive = False
Worksheets("Main").Protect
End Sub

I have a mess on my hands. My new code looks like below but it keeps throwing an error on the If Worksheet.Function line.

Dim IsActive As Boolean

Private Sub Add_Freestyle()
Worksheets("Main").Unprotect
Dim TargetCell As Range
If UserForm5.TextBox1.Text = "" Then Exit Sub

With Worksheets("Main")
        If WorksheetFunction.CountIf(Sheets("Main").Columns(4), TextBox1.Value) = 1 Then
    Set TargetCell = Sheets("Main").Columns(4).Find(TextBox1.Value, , xlValues, xlWhole).Offset(0, 1)
    TargetCell.Value = TargetCell.Value + 1
    TextBox1.Value = ""
    TextBox1.SetFocus
Else
    MsgBox "Item Not Found"
End If
    End With

UserForm5.TextBox1.Text = ""
UserForm5.TextBox1.SetFocus
IsActive = False
Worksheets("Main").Protect
End Sub

If you need me to upload the file, I can do that. I give big thanks ahead of time to anyone willing to help this poor slob.

Hi rmc,

I think you need to learn about VBA Events. Then you need to see how they are triggered. I think you are looking for a worksheet On_Charnge event to trigger your code. Read these:
http://www.tutorialspoint.com/vba/vba_events.htm and
http://www.cpearson.com/excel/Events.aspx

It seems to me you have learned that a button click can trigger some code but haven't yet found worksheet events.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here