UserForm in Excel- need to write code to auto fill a text field

[PANTECH]

I have started an inventory UserForm in Excel with nine text boxes. Two text boxes are drop downs, one shows the date. The first text box (material) the user will use a barcode reader to populate the field. I want the second field (bin) to automatically populate when the material number is entered in the first field. For example material number 5010 is in bin G4, so when 5010 is scanned into the (material) field then the (bin) field would auto populate with G4. Below is the code I have so far. Please help my VBA skills are limited and I want this to be really goof proof user form.

Please Login or Register  to view this content.

[Norie]

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

Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/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

[PANTECH]

Thank you, new to this kind of thing.

[Norie]

You didn't quite get the code tags right, you are missing a / in front of code for the last tag.

[PANTECH]

Sorry, how does it look now?

[Norie]

Still not quite right, I'm afraid.

The easiest way to add code tags is to select the code and then press # on the toolbar.

By the way, you mention material no and bin no, where is that data stored?

Also, does scanning a barcode trigger any event?

[PANTECH]

I would like all data entry into frmInventory to go to sheet1. When scanning the barcode each record should go into column A, so the first scanned record would go into A1 and the second into A2 and so on. Then when Column A (material) is filled then Column B (bin) will fill with the corresponding bin number. Mind you there are roughly three hundred material numbers and they have bin numbers.

[Norie]

Where do the bin numbers come from?

[PANTECH]

Sheet1 the list of material numbers in under column AB and the bin numbers are under column AC.

Private Sub txtMaterial_Change()
Dim rngMat As Range
If Len(txtMaterial.Value) > 13 Then
Set rngMat = Sheets("Sheet1").Range("AB:AB").Find(What:=txtMaterial.Value, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rngMat Is Nothing Then
If IsEmpty(rngMat.Offset(, 1)) Then
txtBin.Value = "N/A" 'Match but no Bin Location
Else
txtBin.Value = rngMat.Offset(, 1).Value 'Bin Location
End If
Else
txtBin.Value = vbNullString 'No materials no. match
End If
Else
txtBin.Value = vbNullString 'Incomplete materials no.
End If
End Sub