See if this is what you had in mind:
Private Sub CommandButton1_Click()
Dim currRow As Long
ActiveCell.Activate
currRow = ActiveCell.Row
uChoose = MsgBox("Insert new row at " & currRow & "?", vbYesNo, "Please confirm ...")
If uChoose = vbNo Then Exit Sub
'insert new row
Me.Rows(currRow).Insert
'put formula in column A
With Me.Range("A" & currRow)
.FormulaR1C1 = "= R[-1]C+1"
.NumberFormat = "000"
End With
'adjust formula for the old column
Me.Range("A" & currRow + 1).FormulaR1C1 = "= R[-1]C+1"
End Sub
Bookmarks