CODEWITHSUNDEEP
excelinsertrowsexcel-tablesvba
Tam Le
Tam Le

Reputation: 378

Prevent user from inserting row in table except using VBA

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

Upvotes: 2

Views: 1997

Answers (1)

EvR
EvR

Reputation: 3498

When using sheet protection, you could add Userinterfaceonly= true, this will prevent user interference, but VBA code will still work.

Private Sub Workbook_Open()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Protect Password:="secret", UserInterFaceOnly:=True
    Next ws
End Sub

or if you want to protect just one sheet:

Private Sub Workbook_Open()
       Worksheets("YourSheetName").Protect Password:="secret", UserInterFaceOnly:=True    End Sub

Or just take protection off before running your macro and put it on afterwards:

Sub InsertRow_Click()
ActiveSheet.Unprotect Password:="secret"
Dim i As Integer

For i = 1 To 10
    ActiveSheet.ListObjects("Invoice").ListRows.Add alwaysinsert:=True
Next i
ActiveSheet.protect Password:="secret"
End Sub

Userinterfaceonly and tables looks if it's no good match

Upvotes: 1

Related Questions