Is there a way to restrict deleting rows in a sheet using VBA?

0
votes

I have an excel sheet in which i want to prevent any rows from being deleted. I have this piece of code that actually does what i want to do but as soon as i run the macro, i am unable to delete any row from any excel workbook. 

Sub DeleteRowsRestrictions()
Dim xBarControl As CommandBarControl
For Each xBarControl In Application.CommandBars.FindControls(ID:=293)
    xBarControl.Enabled = False
Next
For Each xBarControl In Application.CommandBars.FindControls(ID:=294)
    xBarControl.Enabled = False
Next
End Sub

Is there a way to prevent deleting rows only from one excel sheet? And not a macro that disables deleting row in all the excel workbooks as my code does?

Thank you :)

2
excelvbarows
Look into Worksheet.Protection – Mathieu Guindon
In the Workbooks Events, there is one everytime you change to a different worksheet. So you can combine this code with Workbook_SheetActivate to get the name of the active sheet if it changes,check the name and if it's the one you want to block, then activate your code. IF not, deactivate your code. – Foxfire And Burns And Burns
So @FoxfireAndBurnsAndBurns if i want it to restrict me from deleting rows from the "Parking" spreadhsheet only, i should add "Worksheets("Parking").Activate" only? – Celine N
You're solution wouldn't stop a user pressing Shift-Space,Control-minus to delete a row – Harassed Dad

2 Answers

4
votes

Don't reinvent the wheel, that's already a feature of the Excel object model. You can do this directly in the worksheet, by protecting it (I wouldn't bother with a password though, it's easily beaten/hash-collided):

Protect Sheet dialog

Programmatically, that's done with the Worksheet.Protect method - you control what's enabled by providing a Boolean value for each permission - AllowDeletingRows being one of them:

Sheet1.Protect Password:=vbNullString, _
               DrawingObjects:=False, _
               Contents:=False, _
               Scenarios:=False, _
               UserInterfaceOnly:=True, _
               AllowFormattingCells:=True, _
               AllowFormattingColumns:=True, _
               AllowFormattingRows:=True, _
               AllowInsertingColumns:=True, _
               AllowInsertingRows:=False, _
               AllowInsertingHyperlinks:=True, _
               AllowDeletingColumns:=False, _
               AllowDeletingRows:=False, _
               AllowSorting:=True, _
               AllowFiltering:=True, _
               AllowUsingPivotTables:=True

In my experience the protection alone is a sufficient deterrent for the vast majority of users; a password only serves to eventually be lost/forgotten, plus hard-coding a password in VBA code is rather naive, security-wise: worksheet protection is not a security feature.

-1
votes

If you want to avoid deleting rows from Parking WorkSheet, in the event Workbook_SheetActivate you should put something like this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name = "Parking" Then
        'CODE TO BLOCK DELETING ROWS
    Else
        'CODE TO ALLOW DELETING ROWS
    End If
End Sub

Sh is the Worksheet you activate when you change from 1 worksheet to a different one, so when you activate Parking then the code will trigger blocking deleting rows. If it's not PArking then it will allow deleting rows.

UPDATE: You must type this code in the VBA editor, in ThisWorkbook Object.

enter image description here

Once donde, you don't need to pass a parameter, it will work by itself. Events are triggered when a specific action takes places, and don't need the user to pass a parameter (they get it instantly). In this case, the event triggers when user changes from 1 worksheet to another worksheet, and the parameter is the target worksheet (it's stored in the parameter Sh) but you don't need to call it with lines of codes. It will trigger by itself.

You can read more info here about this event:

Workbook.SheetActivate event

If you want to know more about other events, check:

Excel VBA Events

IMPORTANT: Once you have code in any event, that event will be ready to trigger at anytime. In this case, if any other macro of you activates sheet Parking, then it will be triggered.

Also, as other users have posted,your codes just prevents deleting rows from command bar, but probably users can still delete rows with rightClick of mouse, or keyboards shortcuts, or other macros.

If you really want to protect that sheet from any deleting, best option is, indeed, @MathieuGuindon answer, protecting the sheet. That way would be more secure.