VBA writes to first Excel cell, then aborts without error

David James 1 Reputation point
2021-12-17T10:40:51.693+00:00

I am using a form to process data and write it into a worksheet. The form is working but whenever I write to a cell, the first data is written and then the subroutine exits without error.

I have written some test code; "aaa" is written but "bbb" is not. MsgBox appears before writing to first cell but not after writing to it - subroutine aborts. When stepping, routine aborts after first cell write.

Private Sub CommandButton1_Click()

    On Error GoTo ErrorHandler

    MsgBox (ThisWorkbook.Sheets("DJ").Cells(10, 1).Value)
    ThisWorkbook.Sheets("DJ").Cells(10, 1) = "aaa"
    MsgBox (ThisWorkbook.Sheets("DJ").Cells(10, 1).Value)
    ThisWorkbook.Sheets("DJ").Cells(10, 2) = "bbb"

    Exit Sub ' Exit to avoid handler.

ErrorHandler: ' Error-handling routine.

    MsgBox (Err.Description)
    Resume Next

End Sub
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,306 questions
{count} votes