VBA Userform - Edit Row

[snuffnchess]

I currently have an Excel sheet with MultiPage userform to add data onto a worksheet. Data has to ability to fill up to 122 columns.

I want to make it so that the sheet cannot be edited directly, I want it to be an "Edit Row" button - so the user can click in any (populated) cell in a row, click the button, and then a userform will come up, similar to "Create new entry" form, but with the active row's data in the text boxes, list boxes etc. When they click "OK" it will update the data for that row.

How do I go about doing this???

 

[dmt32]

Hi,
One idea would be to populate an array with the selected rows data and then you can use it to populate your existing userform controls or a new form if that is what you want.

Example

 Private Sub CommandButton1_Click()    
   Dim c As Long
    Dim arr As Variant


'populate array with selected row data
    arr = Cells(ActiveCell.Row, 1).Resize(, 122).Value
    
    With UserForm1
    For c = 1 To UBound(arr, 2)
'add array data to userform textbox controls
            .Controls("TextBox" & c).Text = arr(1, c)
    Next c
        .Show
    End With
End Sub

In this example I have assumed that your textboxes have their default names (TextBox1, TextBox2 etc.) and just loop through the array to populate them.
Code would need to be adapted to take in to account other controls but this is just a suggestion which hopefully, gives you something you can adapt to your project

Others here may have alternative ideas.

Dave

 

[snuffnchess]

Hi,
One idea would be to populate an array with the selected rows data and then you can use it to populate your existing userform controls or a new form if that is what you want.

Example

 Private Sub CommandButton1_Click()    
   Dim c As Long
    Dim arr As Variant


'populate array with selected row data
    arr = Cells(ActiveCell.Row, 1).Resize(, 122).Value
    
    With UserForm1
    For c = 1 To UBound(arr, 2)
'add array data to userform textbox controls
            .Controls("TextBox" & c).Text = arr(1, c)
    Next c
        .Show
    End With
End Sub

In this example I have assumed that your textboxes have their default names (TextBox1, TextBox2 etc.) and just loop through the array to populate them.
Code would need to be adapted to take in to account other controls but this is just a suggestion which hopefully, gives you something you can adapt to your project

Others here may have alternative ideas.

Dave

Thank you for the suggestion. It definitely makes sense!

Let me see if i can get it to work, and will let you know if I have any questions.

 

[dmt32]

Thank you for the suggestion. It definitely makes sense!

Let me see if i can get it to work, and will let you know if I have any questions.

You can include things like changing caption on your current forms submit button to something like "Update" to show you are in Edit Mode and amend existing code to return record to correct row.

If you do need further help, always useful to forum if you post your forms code.

Dave

 

[snuffnchess]

That is interesting! Did not even think of that. Form code is super long as there are over 100 fields. Is there a way to upload a file on this forum?

 

[dmt32]

That is interesting! Did not even think of that. Form code is super long as there are over 100 fields. Is there a way to upload a file on this forum?

You place file on a file sharing platform like dropbox & provide link to it here

Dave

 

[snuffnchess]

Thanks!

I have not started programming the Edit button yet... but here is a link to the file so far

https://www.dropbox.com/s/udekk14fj6qwh9d/Client List - Before Edit Button.xlsm?dl=0

 

[snuffnchess]

When I added in the VBA

Private Sub CommandButton2_Click()Dim c As Long
    Dim arr As Variant




'populate array with selected row data
    arr = Cells(ActiveCell.Row, 1).Resize(, 122).Value
    
    With UserForm
    For c = 1 To UBound(arr, 2)
'add array data to userform textbox controls
            .Controls("TextBox" & c).Text = arr(1, c)
    Next c
        .Show
    End With
End Sub

and tested I received the following error
Run-Time Error '2147024809 (80070057)':
Could not find the specified object

File Link: https://www.dropbox.com/s/kivqg9ntbxckxu1/Client List - working - VB Next.xlsm?dl=0
Error Link: https://www.dropbox.com/s/yhy0gkmo52s6mvl/4-25-2019 3-37-58 PM.png?dl=0


The other question that I have is how will things like Check Boxes, Radio buttons, drop downs etc get populated?

 

[snuffnchess]

I tried to edit my post, but was not able to.... so I did some playing around, and was able to get some stuffs to work!

Where problems are going to come into play are for areas like Column I which is a check box. Is there a way for me to map the columns back into the form, just like i mapped the form into the columns?

'populate array with selected row data    arr = Cells(ActiveCell.Row, 2).Resize(, 3).Value
    
    With UserForm
    For c = 1 To UBound(arr, 2)
'add array data to userform textbox controls
            .Controls("TextBox" & c).Text = arr(1, c)
    Next c
        .Show
    End With
End Sub

 

[dmt32]

Hi,
I did state that suggested idea would need to be adapted to take in to account other controls in your form.

You will need to consider a method to map the return value of those controls that have Boolean value against the character you have placed in each of the cells
One possible way to do this would be to use a select case statement in the For Next Loop to help map which column(s) need to return different data type (Text or Boolean) & if Boolean, then test the character in the cell to match to the right control to set its value (True / False).

Something like following

     For c = 1 To UBound(arr, 2)
'add array data to userform controls
        Select Case c
'specifiy columns that are for boolean controls
        Case 9, 18, 22
'index the control
            bx = bx + 1
            Me.Controls("CheckBox" & bx).Value = CBool(arr(1, c) = "A")
        Case Else
'columns that are text
            txt = txt + 1
            Me.Controls("TextBox" & txt).Value = arr(1, c)
        End Select
    Next c

Above is intended only as a suggestion not a working solution but hopefully give you an idea you may be able to build on.

Dave