How to unroll a row in Excel, to have a better view for editing?

Solution 1:

Other answers have used Excel's built-in Data Form. This provides a powerful mechanism for adding/editing/deleting rows of data in a data list without any need for VBA. A data form has the following features

• It defaults to the first data row when activated from the ribbon/quick access toolbar, though allows intuitive navigation to subsequent rows
• All fields (columns) in the data list are editable and a separate one line textbox is provided for changing each field

In some circumstances, these features may be less than ideal - for example when data items comprise very lengthy text strings.

As an alternative to a Data Form, the solution below uses a UserForm. A userform provides greater flexibility than a data form but has the downside that VBA coding and some design skills are required. The userform shown is based on the following assumptions about the OP's requirements

1. The major requirement of the userform is to view and change the content in columns Note1, Note2, Note2 and Note4 of the data list
2. The Name and UserId columns are simply used to identify the data row. The values within these two columns are correct and will not need changing
3. There is no requirement for the userform to provide the capability for adding or deleting rows of the data list
4. There should be the flexibility to move between rows of the data list
5. The userform should be capable of being switched on and off

The userform is shown in the screenshot below.

Behaviour of the userform

1. When the worksheet is in "form mode" (see 4., below), selecting a single cell within any of the columns headed Note1, Note2, Note3 or Note4 displays the content of the corresponding Note item, along with the Name and UserId values from the start of the row. The Note involved (Note1, Note2, Note3 or Note4) is indicated by the label to the left of the large textbox in the userform. The userform is modal, so the user must interact with the form to progress.
2. The text in the large Note textbox may be changed by the user. The button labelled "Save Changes" writes the current content of the textbox to the corresponding Note cell of the worksheet and "closes" the userform (removes it from the display). The button labelled "Cancel" also "closes" the userform, though without updating the corresponding Note cell.
3. No navigation functionality is provided in the userform. Instead, the user "closes" the form (using either of the two buttons) and can navigate using the standard Excel interface to other cells. The form reappears whenever the current selected cell(s) on the worksheet meet the condition specified in 1., above.
4. The worksheet remains in "form mode" until the userform is closed with the "Close" button (the standard "X" at the top-right of the form). The worksheet can be put back into "form mode" by running the macro FormMode.

Structure and Organisation

1. The data, including the header row and the Name and UserId columns are contained in a named range called MyData
2. The userform is called EditForm. The form contains 5 labels, 1 textbox, 2 buttons and the standard "Close button" on the title bar. EditForm has a boolean property EditOn which determines whether "form mode" is on or off.
3. There is a VBA module which declares a couple of Public variables intDataRow and intDataColumn which are the row and column containing the cell within MyData that provides the contents of the Note textbox displayed in EditForm. The module also contains the macro FormMode which simply switches "form mode" on by the assignment EditForm.EditOn = True.
4. The worksheet containing MyData contains a Worksheet_SelectionChange procedure. This procedure tests
   • whether "form mode" is on
   • whether a single cell has been selected on the worksheet
   • whether this cell is contained within a Note column in MyData (after the header row) If any test fails, the procedure does nothing. Otherwise it calculates the intDataRow and intDataColumn values of the selected cell and causes EditForm to be displayed through its Show method.
5. The module for EditForm contains the following procedures:
   • Property Let and Property Get for the EditOn property of the userform
   • UserForm_Activate event procedure. The event is triggered by EditForm.Show (in 4., above) and populates the userform from the worksheet based on the current values of intDataRow and intDataColumn.
   • button _Click procedures for the "Save Changes" and "Cancel" buttons. The former updates Range("MyData").Cell(intDataRow,intDataColumn) with the contents of the userform's textbox. Both procedures remove the userform from the display ("close" it) through the form's Hide method.

A screenshot of the VBA project is shown below.

The form is basic but functional. It can obviously be adapted and improved to meet requirements. It provides a different model to that provided by Excel's Data Form.

The VBA code utilised is listed below.

CODE FOR WORKSHEET CONTAINING RANGE MyData

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Not EditForm.EditOn Then
        Exit Sub
    End If
  
    If Target.Rows.Count > 1 Or _
        Target.Columns.Count > 1 Then
        Exit Sub
    End If
  
  
    If Target.Row < Range("MyData").Row + 1 Or _
        Target.Row > Range("MyData").Row + Range("MyData").Rows.Count - 1 Or _
        Target.Column < Range("MyData").Column + 2 Or _
        Target.Column > Range("MyData").Column + Range("MyData").Columns.Count - 1 Then
        Exit Sub
    End If

    intDataRow = 1 + Target.Row - Range("MyData").Row
    intDataColumn = 1 + Target.Column - Range("MyData").Column
       
    EditForm.Show
        
End Sub

CODE FOR FORM EditForm

Option Explicit

Private pEditMode As Boolean

Public Property Let EditOn(bValue As Boolean)
    pEditMode = bValue
End Property

Public Property Get EditOn() As Boolean    
    EditOn = pEditMode
End Property

Private Sub UserForm_Activate()
    Name_Field.Caption = Range("MyData").Cells(intDataRow, 1)
    UserID_Field.Caption = Range("MyData").Cells(intDataRow, 2)
    NoteLabel.Caption = "Note" & (intDataColumn - 2) & ":"
    Note_Field.Value = Range("MyData").Cells(intDataRow, intDataColumn)
End Sub

Private Sub CancelButton_Click()
    Me.Hide
End Sub
    
Private Sub SaveButton_Click()
    Range("MyData").Cells(intDataRow, intDataColumn) = Note_Field.Value
    Me.Hide
End Sub

CODE FOR MODULE

Option Explicit

Public intDataRow As Integer
Public intDataColumn As Integer

Sub FormMode()
    EditForm.EditOn = True
End Sub

Solution 2:

Here is how to add a VBA macro that will show a Data Form for the current row.

• In Excel Show the Developer tab

• Save the workbook as a macro-enabled .xlsm file (not .xlsx)

• Go to the Developer tab

• Click Visual Basic

• Select the Menu entry of Insert > Module

• In the text-editor that opens, copy the following text:

    Option Explicit
    Sub CurrRowForm()
    SendKeys "{DOWN " & ActiveCell.Row - 2 & "}{TAB 3}"
    Application.DisplayAlerts = False
    ActiveSheet.ShowDataForm
    Application.DisplayAlerts = True
    End Sub
  

• Save by pressing Ctrl+S and leave the VBA editor by Alt+Q

• To test the new macro:

  • Select your row

  • In the Developer tab click Macros

  • Ensure the CurrRowForm macro is selected

  • Click the Run button

  • The result should look like this when the second row is the current:

    

You may assign the macro a hotkey by :

• Enter Developer > Macros
• Ensure the macro CurrRowForm is selected
• Click Options
• Select your Shortcut key
• Click OK

You may also for CurrRowForm Add a macro button to the ribbon.

Solution 3:

I haven't read any comments mentioning the built in Excel data form on the Data tab, Data Entry group. Alt A Y 2 keys in Windows.

Formulas are read only, cell protection is maintained; however, there's no ability to change heights or widths of fields.

Fields will widen automatically but only to a point. You could set up a hotkey to avoid the Ribbon key sequence.