How to update worksheet when listbox value is selected

[batibot]

Hi guys,

I have crunching on my brain for days to come up with a code. I'm extremely novice to VBA/Userforms and hoping you would be able to help me out.

I'm trying to build a code where if the value in the lisbox is selected, the values are then shown on its corresponding textbox. This is so if a user requires to edit the value, they will be able to just type on the textbox and click the Update button.

So far this is what I've written, except the update part. Woud appreciate all the help and advice I can get.

'send data to worksheet by typing into corresponding textbox
Private Sub cmdSend_Click()

Dim lastrow As Long
lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Cells(lastrow + 1, "A").Value = txtIssue.Text
Cells(lastrow + 1, "C").Value = txtDateReceived.Text
Cells(lastrow + 1, "D").Value = txtAgency.Text
Cells(lastrow + 1, "E").Value = txtService.Text
Cells(lastrow + 1, "F").Value = txtSource.Text
Cells(lastrow + 1, "G").Value = txtIssueType.Text
Cells(lastrow + 1, "H").Value = txtIssueNonIssue.Text
Cells(lastrow + 1, "I").Value = txtOwnership.Text
Cells(lastrow + 1, "J").Value = txtTimeSpent.Text
Cells(lastrow + 1, "K").Value = txtDateCompleted.Text
Cells(lastrow + 1, "L").Value = txtActiveDuration.Text

End Sub
'select value in lisbox - values is then shown on corresponding textbox
Private Sub ListOfData_Change()

txtIssue.Value = Application.VLookup(ListOfData, Sheets("Sheet1").Range("A:L"), 1, False)
txtDateReceived.Value = Application.VLookup(ListOfData, Sheets("Sheet1").Range("A:L"), 3, False)
txtAgency.Value = Application.VLookup(ListOfData, Sheets("Sheet1").Range("A:L"), 4, False)
txtService.Value = Application.VLookup(ListOfData, Sheets("Sheet1").Range("A:L"), 5, False)
txtSource.Value = Application.VLookup(ListOfData, Sheets("Sheet1").Range("A:L"), 6, False)
txtIssueType.Value = Application.VLookup(ListOfData, Sheets("Sheet1").Range("A:L"), 7, False)
txtIssueNonIssue.Value = Application.VLookup(ListOfData, Sheets("Sheet1").Range("A:L"), 8, False)
txtOwnership.Value = Application.VLookup(ListOfData, Sheets("Sheet1").Range("A:L"), 9, False)
txtTimeSpent.Value = Application.VLookup(ListOfData, Sheets("Sheet1").Range("A:L"), 10, False)
txtDateCompleted.Value = Application.VLookup(ListOfData, Sheets("Sheet1").Range("A:L"), 11, False)
txtActiveDuration.Value = Application.VLookup(ListOfData, Sheets("Sheet1").Range("A:L"), 12, False)

End Sub

'select value in listbox, value then shows on the textbox and if required edit and update
Private Sub cmdUpdate_Click()


End Sub

 

[My Aswer Is This]

Try this:

Private Sub ListBox1_Click()
TextBox1.Value = ListBox1.Value

End Sub

 

[batibot]

Hi,

Thanks for the suggestion - however it still doesn't work??

 

[My Aswer Is This]

I have built numerous UserForms and I know this script does what you want.
When editing your userform you double click the listbox and then post in the below code. Then when you select a value in the listbox it will go into the text box.
You must have your textbox named textbox1.

I do not understand this in your post

Private Sub cmdUpdate_Click()

You do understand the script below has to be in the Userform Textbox script.
This is not a modular script.

Private Sub ListBox1_Click()
TextBox1.Value = ListBox1.Value
End Sub

 

[My Aswer Is This]

If your building a UserForm this is normally not what you would see.

Private Sub ListOfData_Change()

This is a module script not a Userform script.

Is this the first time you have ever built a UserForm?

You said in your OP:
"I'm extremely novice to VBA/Userforms and ..."
The scripts I see here do not look like they are written for a UserForm.

 

[batibot]

Hi agan, yes this is the first time I have every attempted to build a Userfrom (please bare with me )

The codes I have seems to work where if I click on the list, the value appears on the corresponding text box.

However, what I would essentially then like to do is if I need to update the value and keep it on the same row of data, how would I be able to do so??

 

[My Aswer Is This]

batibot.
So now it sounds like you have written a few lines of code which will put the value in the listbox into the textbox
Every time you ask a question about your script you need to post the script here.
And Mr. Excel likes you to post your code inside "tags" look in the box where you post your questions on this forum and you see a menu bar you should click on the tag icon which looks like this # and then paste your code in. In my post to you you will see my code is inside a nice little box.

So post the code you are now using where you want your data you enter to go in the same line as per your question of:
"I need to update the value and keep it on the same row of data, how would I be able to do so?? "

Post the code I will read what you have and will then be able to help you.

I was wondering what is all this other code all about shown in your original post which looks like this:

Dim lastrow As Long
 lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
 Cells(lastrow + 1, "A").Value = txtIssue.Text
 Cells(lastrow + 1, "C").Value = txtDateReceived.Text
 Cells(lastrow + 1, "D").Value = txtAgency.Text
 Cells(lastrow + 1, "E").Value = txtService.Text

Did you write all this code or just get it from some other Forum.
I know a lot of folks we work with here find code and then try to figure out how to modify it.
Take care and get back with me.
Be sure and answer all my questions it helps things go faster here.

 

[Trebor76]

This has been cross-posted here