Prevent user from inserting row except using VBA

[nvu1991]

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.

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

 

[Yongle]

It is possible to remove protection automatically with VBA, do what you want and then reapply it with VBA like this:

Sub DoWhatEver()

ActiveSheet.[COLOR=#ff0000]Unprotect[/COLOR] "password"

'code to do whatever you want here
    ActiveSheet.Rows(2).Insert

ActiveSheet.[COLOR=#ff0000]Protect[/COLOR] "password", True, True

End Sub

Steps
1. Select all cells in sheet \ right-click\ format cells \ protection \ uncheck Locked
(this is critical!)

2. Protect the sheet manually allowing everything except insert and deleting rows (and columns?)

3. VBA will reapply the same rules when it unprotects & protects your sheet

 

[nvu1991]

Nice solution, I will try this out.

 

[nvu1991]

Unfortunately, protect and unprotect worksheet using this method will always have the Excel default protection.
I tried to set a default protection that allow user to insert rows and delete rows (just for testing only). But whenever I run the code, the sheet will be protected without allowing user to delete rows and add rows.

 

[Yongle]

I am confused
The title of this thread is Prevent user from inserting row except using VBA
and now you are unhappy because "whenever I run the code, the sheet will be protected without allowing user to delete rows and add rows"

Now I want to disable the ability for user to add rows manually, meaning rows can ONLY be added via clicking the button

- which is what my code allows you to do

If you create a procedure using my method and attach it to a button, then the user can insert or delete rows

But you said that the "user messes things up" when inserting rows manually, so your procedure must include whatever is necessary to prevent "messing things up"

What exactly should happen when a button is pressed to add a row?
- ie where should row be inserted (at bottom of existing data?)
- what should the new row contain automatically? (formulas etc?)

 

[nvu1991]

What exactly should happen when a button is pressed to add a row?
- ie where should row be inserted (at bottom of existing data?)
- what should the new row contain automatically? (formulas etc?)

For the 2 last question that you have:
- Yes, the rows would be add at the bottom of the table
- The new rows should have formulas.

I am confused
The title of this thread is Prevent user from inserting row except using VBA
and now you are unhappy because "whenever I run the code, the sheet will be protected without allowing user to delete rows and add rows"

- which is what my code allows you to do

I wanted user to be able to delete rows. Because when the button is pressed, the code add 10 rows. There will be extra rows with some empty cells and that will create error notification in the file (from me, not from excel, ie: the cell will change color to red, the title rows will change to orange). I want user to be able to delete the extra rows so they don't feel like they still have done the job properly.

 

[Yongle]

Add command button and rename it DeleteSelectedRow
The code below deletes whichever row is selected with the table (or the first row if more than one selected)
The Resume Next avoids VBA error if user does not select a cell within the table

Private Sub DeleteSelectedRow_Click()
    On Error Resume Next
    ActiveSheet.ListObjects("Invoice").ListRows(Selection.Row - Selection.ListObject.Range.Row).Delete
End Sub