Text box data entry - need some code

Hozz

Board Regular
Joined
Feb 10, 2005
Messages
140
Sorry about that really crap subject..i couldn't think of anything better.

I have a spread with two columns in it.

Column A from 3:1000 contains names. Column B from 3:1000 contains a number.

I also have two text boxes i'll call them A and B.

My aim is to type a name into text box A. When enter is pressed, text box B is selected where I will enter a number.

When Enter is pressed a second time, the name typed into A should be found in the list in Column A and then the value that was typed into B should be added the value in column B next to the name.
The text boxes should then be cleared and text box A will once again be selected.

How do I do this in code?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,255
A few assumptions need to be made by someone attempting to answer your question because you left out 3 important details.

Assumption #1 is that your textboxes are created from the activex control toolbox, which if they are not, should be.

Assumption #2 is that the names in column A are unique, because you did not infer pluralization of a found name in your text:
"should be added the value in column B next to the name."

Assumption #3 is that your textboxes really are named TextBoxA and TextBoxB. I doubt that really is the case, but that is how I coded it below to follow your example, so you will need to change the names in the code, or the textbox objects' property names, so the code matches the real name of your textboxes.

For the worksheet containing those 2 textboxes and 2-column list, right click on that sheet tab, left click on View Code, and paste the following code into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Remember, the code will fail if your textboxes are not named as TextBoxA and TextBoxB, so modify as needed.




Private Sub TextBoxA_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
KeyCode = 0
TextBoxB.Activate
End If
End Sub


Private Sub TextBoxB_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
KeyCode = 0

If Len(TextBoxA.Text) = 0 Or IsNumeric(TextBoxA.Text) = True Then
MsgBox "A name must be entered into textBox A.", 48, "Can't find what's not there."
Exit Sub
ElseIf TextBoxB.Text = "" Then
MsgBox "You entered nothing in this text box.", 48, "Nothing to add."
Exit Sub
ElseIf Not IsNumeric(TextBoxB.Text) Then
MsgBox TextBoxB.Text & " is not a number.", 16, "Numbers only please."
Exit Sub
End If

Dim varFind As Variant
Set varFind = Columns(1).Find(What:=TextBoxA.Text, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not varFind Is Nothing Then
Cells(varFind.Row, 2).Value = Cells(varFind.Row, 2).Value + Val(TextBoxB.Text)
MsgBox TextBoxB.Text & " has been added to cell B" & varFind.Row & " for " & TextBoxA.Text & ".", 64, "Done."
TextBoxA.Text = "": TextBoxB.Text = ""
TextBoxA.Activate
Else
MsgBox TextBoxA.Text & " was not found in column A.", 64, "No such animal."
End If

End If
End Sub
 

Forum statistics

Threads
1,137,195
Messages
5,680,095
Members
419,880
Latest member
suarezprado

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top