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
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