Re-populate User Form from the database record

My Excel document takes the form of a datasheet lines of entry, populated by a Userform, which transfers the data across to a new row

The complication is that each entry will change in some regards later down the line, and information needs to be updated.

Ideally, the practitioner should be able to edit the record in the user form, rather than the spreadsheet.

Therefore, is there any code which, for example clicking in a row, re-populates the User form so that the data can be amended and then put back into the row as a replacement?
I suppose for example this could take the form of an event handler upon double clicking in a persons record, or a command button from which you select the appropriate record from a 'Go to' interface.

Thanks in advance for any assistance.
Henry Bukowski

linked Post: http://www.mrexcel.com/forum/excel-q...ml#post4099094

Originally Posted by gmr4evr1

Are the users inputting data into the form once per day or is it several times per day? If once per day, you could create a "Submit" button on the form. This would transfer the data and keep the form open.
If they input the data several times a day, can you create a button on the spreadsheet and assign a macro for "Show UserForm", that they can click on when they need to?

Thanks - so actually this data will be updated less frequently, so keeping the form open might not be suitable.
It might be every couple of days when new information is gathered regarding the client which needs updating.

Cheers

Originally Posted by john55

maybe this, just try

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'click in any cell from a2 to a200 and the userform is populated...
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A2:A200")) Is Nothing Then Exit Sub
UserForm1.Show
End Sub


Private Sub UserForm_Initialize()
'read the data from active row
    With Me
        .TextBox1.Value = Cells(ActiveCell.Row, "A").Value
        .TextBox2.Value = Cells(ActiveCell.Row, "B").Value
'more textboxes
'
'
    End With
End Sub

the button for transfering yr dada
Private Sub CommandButton1_Click()

         Cells(ActiveCell.Row, "A").Value = TextBox1.Value
         Cells(ActiveCell.Row, "B").Value = TextBox2.Value
   
End Sub

Thanks John.
So bear with me here - the code you posted sucessfully brings up the UserForm...but what it doesn't yet seem to do is 'take up' the values of the row in question.

I have amended the particular routine so that the text box names match my own:

Private Sub UserForm_Initialize()
'read the data from active row
    With Me
        .cboDATE.Value = Cells(ActiveCell.Row, "A").Value
        .cboREF.Value = Cells(ActiveCell.Row, "C").Value
        .cboCI.Value = Cells(ActiveCell.Row, "D").Value
        .age.Value = Cells(ActiveCell.Row, "E").Value
        .ComboBox25.Value = Cells(ActiveCell.Row, "F").Value
        .cboGEN.Value = Cells(ActiveCell.Row, "G").Value
'more textboxes
'
'
    End With
End Sub

Thanks!