Allow user to delete cell ranges?

  • Thread starter Thread starter Dave Mathew
  • Start date Start date
D

Dave Mathew

Is there a way that I can protect a worksheet but specify certain cell
ranges that a user could delete cell ranges in? Essentially what I
have are several "tables" of data and I'd like to protect everything
in the sheet except the data in the tables. I'd like the user to have
the ability to delete rows from the data in a specific table, i.e. the
user should be able to select the cells that make up part of the data
in the table and have the option to delete the selected cells and have
remaining cells shifted up. I've tried unlocking the cell ranges I
want to have this ability and checking the boxes next to "delete rows"
and "insert rows" when protecting the sheet, but so far am not getting
the functionality I'm looking for (delete is grayed out in the context
menu when I select cells in that range).
 
If there are any locked cells in the row to be deleted on a protected sheet,
Excel won't let you delete that row. Try selecting & unlocking entire rows
before protecting the sheet. You also have to put a check in the 'Delete
rows', "Select locked cells', and 'Select unlocked cells' checkboxes in the
Protect Sheet dialog. Then I think it will behave as you want.

Hope this helps,

Hutch
 
I just tried unlocking a row and it will allow me to delete the entire
row but what I am wanting to do is select and delete a specific range
of cells in a row, which it appears still isn't allowed even if I
unlock the entire row... i.e. I can only delete a row by right
clicking on the row header on the left and selecting delete, when I
select a few cells in the row and right click on the selection the
delete option is still grayed out.
 
I suggest you add a command button to the sheet with a caption like "Delete
selected cells'. Set the Locked property of this button to False (so it
doesn't get locked when you protect the sheet). In the click event for the
button, use VBA to unprotect the sheet, delete the selected cell(s), and
reprotect the sheet. For example,

Private Sub CommandButton1_Click()
Dim C As Range
ActiveSheet.Unprotect Password:="aaa"
For Each C In Selection
If C.Locked = False Then
C.Delete Shift:=xlUp
End If
Next C
ActiveSheet.Protect Password:="aaa", _
DrawingObjects:=True, Contents:=True
End Sub

This code looks at each cell selected be the user. If that cell is locked,
it is not deleted. Selected cells that are not locked are deleted and the
cells below shift upward.

Hope this helps,

Hutch
 
Back
Top