I have attached a demo version of my work sheet along with the script that I am running.
What I am trying to figure out is the script to allow the second userform (Document Details) to be changed which would then update the spreadsheet.
On the spreadsheet currently, there is a search button in the left corner. Once selected, a search userform will show up where you can either search based on document number of document name. Once the parameters of the search are entered, select the search button on the userform which will populate the table on the userform. This will show all documents that have matched the search parameters.
Once you have found the document you are after, select it from the list and then click "Show Selected". This will bring up a second userform that provides extensive details about the selected document. This the form that I would like to script to change the data in the workbook. For example, if the version number was to change, I could change it on this userform and it would update the worksheet once the "Update Information" button was selected.
Any help would be greatly appreciated.
Cheers,
Hi Matthew,
When you press Show Selected, you do know the row number from the worksheet, you just have to pass it to the other form.
You can do that using a tag, of the form, or of another object from that form.
Here is the updated code:
Private Sub showselected_Click()
If Results.ListIndex = -1 Then Exit Sub ' no row selected
Dim RW As Long, Wks As Worksheet
Set Wks = ThisWorkbook.Worksheets("Forms")
RW = Results.List(Results.ListIndex, 3)
DocumentDetails.TextBox2.Value = Wks.Cells(RW, "A")
DocumentDetails.TextBox6.Value = Wks.Cells(RW, "C")
DocumentDetails.TextBox3.Value = Wks.Cells(RW, "B")
DocumentDetails.TextBox7.Value = Wks.Cells(RW, "D")
DocumentDetails.TextBox8.Value = Wks.Cells(RW, "E")
DocumentDetails.TextBox9.Value = Wks.Cells(RW, "F")
DocumentDetails.TextBox10.Value = Wks.Cells(RW, "G")
DocumentDetails.TextBox11.Value = Wks.Cells(RW, "H")
DocumentDetails.TextBox12.Value = Wks.Cells(RW, "I")
DocumentDetails.TextBox13.Value = Wks.Cells(RW, "J")
DocumentDetails.TextBox14.Value = Wks.Cells(RW, "K")
DocumentDetails.TextBox15.Value = Wks.Cells(RW, "L")
'continue with the rest of the textboxes needed
DocumentDetails.Tag = RW 'store the row number of the worksheet in the form tag
DocumentDetails.Show
End Sub
Then, from the Update Information button:
Private Sub CommandButton1_Click()
Dim RW As Long
RW = CLng(Me.Tag) 'read the row that was stored in the current form tag
Worksheets("forms").Cells(RW, 2).Value = TextBox3.Text
Worksheets("forms").Cells(RW, 3).Value = TextBox6.Text
Worksheets("forms").Cells(RW, 4).Value = TextBox7.Text
Worksheets("forms").Cells(RW, 5).Value = TextBox8.Text
Worksheets("forms").Cells(RW, 6).Value = TextBox9.Text
Worksheets("forms").Cells(RW, 7).Value = TextBox10.Text
Worksheets("forms").Cells(RW, 8).Value = TextBox11.Text
Worksheets("forms").Cells(RW, 9).Value = TextBox12.Text
Worksheets("forms").Cells(RW, 10).Value = TextBox13.Text
Worksheets("forms").Cells(RW, 11).Value = TextBox14.Text
Worksheets("forms").Cells(RW, 12).Value = TextBox15.Text
End Sub
