Prevent user from inserting row in table except using VBA

Question

I've created a table for data entering. However, as user use it, they insert rows in the middle of the table. That messes the formula up as the functions were designed only work forward. Also sometimes when the user add row manually (just by typing into the next row after the last row of the table), the function were filled automatically but the function is incorrect quite often.

So I added a button to add the rows to the table and that works without problems. Now I want to disable the ability for user to add rows manually, meaning rows can ONLY be added via clicking the button.

As far as I research, people all suggesting using protect sheet functionality. But it would remove all ability to add rows including via VBA. Other offer the VBA that only prevent inserting rows via right click at the Rows Column. I need to disable all user-accessible ways.

This is the code for the button (if it's of any relevant).

Sub InsertRow_Click()

Dim i As Integer

For i = 1 To 10
    ActiveSheet.ListObjects("Invoice").ListRows.Add alwaysinsert:=True
Next i

End Sub

Answer 1

@VuNguyen7

As others suggested protect the sheet with a password - 1234 below - (you should also protect your VBA project...)

in ThisWorkbook

Private Sub Workbook_Open()

'

    Call OnWorkbookOpen

End Sub

in Module1

Option Explicit

Private pwd As String

Sub OnWorkbookOpen()

'

    pwd = "1234"

End Sub

Sub InsertRow_Click()

'

    Dim i       As Integer

    Dim oList   As ListObject

    Set oList = ActiveSheet.ListObjects("Invoice")

    Application.ScreenUpdating = False

    With ActiveSheet

        .Unprotect pwd

        For i = 1 To 10

            oList.ListRows.Add alwaysinsert:=True

        Next i

        .Protect pwd

    End With

    Application.ScreenUpdating = True

End Sub