Populate user form from Excel spreadsheet

[kawa13]

Hi guys,

I have a user form, which I use to collect data. Each record is given a unique number on creation i.e. DCR0001 (from cell A3 to Cell S3), DCR0002 (from cell A4 to Cell S4) etc., which may / will need to be amended at some point in the future

I'd like to be able to select, by double clicking, an individual record, which would then open up that record on my user form, allow the user to amend the record as they see fit, and then save it back to the same line on the Excel spreadsheet


Any help you can give would be very much appreciated

Regards,

kawa13

 

[dmt32]

Hi,
you do not share any information about your userform but as a suggestion you could do something like this


Assuming your userform has it's default name (UserForm1) & contains 19 TexBoxes also with their default names (TexBox1, TextBox2 etc)

Place ALL following code In your userforms code page

Dim Row As Long
Dim arr As Variant
Private Sub CommandButton1_Click()
'Submit Button
    Dim i As Integer
'Data Entry
    ReDim arr(1 To UBound(arr, 2))
    For i = 1 To UBound(arr)
        arr(i) = Me.Controls("TextBox" & i).Text
    Next
     Cells(Row, 1).Resize(, UBound(arr)).Value = arr
     MsgBox "Record Updated", 64, "Record Updated"
    Unload Me
End Sub


Private Sub CommandButton2_Click()
'Cancel Button
    Unload Me
End Sub


Private Sub UserForm_Initialize()
    Dim i As Integer
    Dim cell As Variant
    Row = ActiveCell.Row
'get row data
    arr = Cells(Row, 1).Resize(, Cells(3, Columns.Count).End(xlToLeft).Column).Value2
'add array data to textboxes
    For Each cell In arr
        i = i + 1
        Me.Controls("TextBox" & i).Text = cell
    Next cell
End Sub

Place following code in your worksheets code page

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rng As Range
    Dim LastRow As Long
    LastRow = Me.Cells.Find(What:="*", Lookat:=xlPart, SearchOrder:=xlByRows, _
                                         SearchDirection:=xlPrevious).Row - 1
    Set rng = Me.Range("A3").Resize(LastRow, 19)


    On Error GoTo exitsub
    If Not Application.Intersect(Target, rng) Is Nothing Then
    Application.EnableEvents = False
        Cancel = True
'open userform & display target row data
        UserForm1.Show
    End If
exitsub:
    Application.EnableEvents = True
End Sub

Hopefully when you double click on a row your form should appear populated with data - Pressing commandbutton1 (Submit) should write the record back to the correct row on your worksheet.

As stated, this is just a suggestion & you will need to develop as required to meet your specific project need.

Alternatively, you could consider using the builtin Data Form

https://support.office.com/en-ie/ar...ata-form-17bca0a4-3ba5-444a-983c-a8ce70609374

Dave