Update a row by clicking any cell in the row

[tigerdel]

Good Afternoon experts

What I am trying to do is:

If a user needs to edit content in a row, they click a cell and it opens a Userform that contains the content of specific cells in the row and allows them to make the necessary changes.

For example:
[there can be up to 400 rows of content]

So, if the user needs to change a rows values [Can only change value in column E:I], what I would like to happen is that they click a cell in the row and it opens a form that contains the data in the Ex, Fx, Gx, Hx and Ix
The user may then make the change(s) and Submit them back to those cells

Is this possible?

Many thanks

 

[Yongle]

Here are the basics
- but you may need to do something further to convert some of the values from strings when placed back in workbook


Create userform with 5 textboxes and 1 command button and use the following code

Place in Userform window

Option Explicit
Private cel As Range

Private Sub CommandButton1_Click()
    cel = TextBox1.Text
    cel.Offset(, 1) = TextBox2.Text
    cel.Offset(, 2) = TextBox3.Text
    cel.Offset(, 3) = TextBox4.Text
    cel.Offset(, 4) = TextBox5.Text
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Set cel = ActiveSheet.Cells(ActiveCell.Row, "E")
    TextBox1 = cel.Value
    TextBox2 = cel.Offset(, 1).Value
    TextBox3 = cel.Offset(, 2).Value
    TextBox4 = cel.Offset(, 3).Value
    TextBox5 = cel.Offset(, 4).Value
End Sub

Place in Sheet code window - will NOT work if placed in a module like Module1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row > 1 Then UserForm1.Show
End Sub