Find invoice and fill name accordingly using vba

[Kamal GKJ]

Dear ALL,

Hope you are doing good, I have a dump of data (invoice number's) and multiple data in excel. If there are 10 employee's but each can process different number's of invoice's. Here is what I want to do.

If a person is handling the invoice while taking the invoice he should enter his name(VBA sheet or form) and the name should be amended on the respective column against to the invoice number.

Eg O/P
Invoice number Agent name
12345 XXXX
14567 TTTTT
23567 RRRR
19635 LLLLL

Thanks in advance.

 

[Kamal GKJ]

OK.

I always need specific details.

1.I assume you have a button on a Userform and when you press this button the script will run

And you must have a Textbox on the Userform where the user enters his name

And you must have a Textbox on the Userform where the user enters the invoice number.

Or better yet the user would select the invoice number from a list of invoice numbers in a Combobox or listbox

So the script would be sort of like this:

Look down column A for the invoice number selected in Combobox1 or Listbox1 and enter the name entered in Textbox1 Into same row column B



Now I need to know the name of the Textbox where the user enters his name
And I need the name of the Textbox or Combobox or listbox where he selects or enters the invoice number.

​

We could load all the invoice numbers into a Combobox so you choose from the combobox so there are no mistakes

We would load the Combobox like this:

Combobox1.List= Range("A1:A400").value

Or something like that if you wanted to do things like that.
Please provide specific details like this.

I Am sorry I am new to VBA

Please find the code in userForm:

Private Sub ComboBox1_Change()
ComboBox1.List = Range("B1:B1000")
End Sub


Private Sub TextBox1_Change()


End Sub

 

[My Aswer Is This]

You need a Combobox named Combobox1
You need a Textbox named Textbox1

And put this script in your UserForm

The combobox will be loaded with values when Userform is shown

When you choose a value in the combobox the script will run
Put the Employee name in Textbox1

Private Sub ComboBox1_Change()
'Modified 6/29/18 9:15 AM EDT
If TextBox1.Value = "" Then MsgBox "You failed to enter a value in TextBox1" & vbNewLine & "I will now stop the script": Exit Sub
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim SearchString As String
Dim SearchRange As Range
SearchString = ComboBox1.Value
Set SearchRange = Range("A1:A" & Lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox "Value not found": Exit Sub
SearchRange.Offset(, 1).Value = TextBox1.Value
TextBox1.Value = ""
End Sub
Private Sub UserForm_Initialize()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
ComboBox1.List = Range("A1:A" & Lastrow).Value
End Sub

 

[Kamal GKJ]

Superb Bro,

I Did not expect this will work.


Thanks.....

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Superb Bro,

I Did not expect this will work.


Thanks.....