Change code from "on button click" event to On_Change event.

I am very new to Excel VBA so please be gentle.  I have some code that is triggered on a button click.  However, I want it to be triggered on a change event (basically that a text box contains information) like another set of code I have.  What I need help with is sort of a merge of code here.  I need the fuction of the 1st set of code below to be triggered like the second set of code.

Just so you know I am scanning in a barcode and and I want it to find the barcode in the correct row then increase the next cell to the right by 1.  Then the value clears in the form so I can scan again.

If you need me to upload a file, I can certainly do that.  Just tell me to.

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

Open in new window


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

Open in new window

rmc71291Asked:
Who is Participating?
 
ltlbearand3Commented:
Ok.  We have a couple options.  One is to add a tab into the scanner itself - many scanners can be programmed to add a tab suffix - check your documentation for the scanners you use.

The other option is to make the code check the length of the value in the text box on the change event and only respond when a certain length is found.  Are the barcodes always the same length?  Suppose they are always 12 digits, you could do something like this:

Private Sub TextBox1_Change()
    ' Only run this code when the full 12 digits are received.
    If Len(TextBox1.Text) = 12 Then
        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
        TextBox1.Text = vbNullString
        TextBox1.SetFocus
        Worksheets("Main").Protect
    End If
End Sub

Open in new window

0
 
ltlbearand3Commented:
I don't know if I follow everything you need, but lets start with some things I think I understand from you question.

First, I don't recommend using the change event for this type of code.  The change event fires every time something changes in the text box.  For example if you type the word test in the box, it will fire for the "t", then the "e", then the "s", and then the last "t."  I would be better to use another event like the exit event.  Not knowing what your form looks like, you will need at lest one other control on your form (like a button for exit).  You can then program you scan gun to scan the barcode and send a tab at the end.  This would trigger the exit code.  If your textbox is text box 1, in the code for the form you could put:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    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
    TextBox1.Text = vbNullString
    TextBox1.SetFocus
    Worksheets("Main").Protect
End Sub

Open in new window

0
 
rmc71291Author Commented:
Ah.  Well the form code has a 3 second delay before it tells the other code to run.  I need it like this because a person is scanning in inventory with a barcoder and there is no typing.  So they open the form and the code does nothing when it is blank.  Then when it sees data it runs this code and performs the function of looking at the string equivalent of that barocde, then increases the number in the cell to the right of the barcode string, then clears the form so we can scan the next item.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
rmc71291Author Commented:
I did try your code but when I scan in data into my form, nothing happens.  We never really exit the text box because we are not at the workstation.  We are walking around scanning items with a wireless barcode scanner.
0
 
rmc71291Author Commented:
Ok.  With a few changes in your code I was able to make it work.  I don't know how I figured out how to change your code but I'm proud of myself for finding out how to do it.  Thank you so very much.  The code below does exactly what I was looking for.

Private Sub TextBox1_Change()
    ' Only run this code when there is information in UserForm5.TextBox1
    If UserForm5.TextBox1.Text = vbNullString Then Exit Sub
        Worksheets("Main").Unprotect
        Dim TargetCell As Range
        If WorksheetFunction.CountIf(Sheets("Main").Columns(4), UserForm5.TextBox1.Value) = 1 Then
            Set TargetCell = Sheets("Main").Columns(4).Find(UserForm5.TextBox1.Value, , xlValues, xlWhole).Offset(0, 1)
            TargetCell.Value = TargetCell.Value + 1
            UserForm5.TextBox1.Value = ""
            UserForm5.TextBox1.SetFocus
        Else
            MsgBox "Item Not Found"
        End If
        UserForm5.TextBox1.Text = vbNullString
        UserForm5.TextBox1.SetFocus
        Worksheets("Main").Protect
End Sub

Open in new window

0
 
rmc71291Author Commented:
Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.