Delete a Row of a Protected Sheet.... Possible???

[Hardware Man]

I'm developing a spreadsheet which has about 40 columns or so. Half of them are formulas that I want to protect, the other half are cells that the user needs to fill in and are to remain editable. The number of used rows will vary greatly, depending on the user.

The formulas are written in such a way as to always display a blank cell if the user did not enter his data where he's supposed to. This enabled me to copy down about 1000 rows, even though most users will only be using about a hundred rows at the most.

What I'd like to do is protect the cells that I don't want the user to mess with, while enabling him to delete an entire row if that data is no longer needed. I don't want him to be able to insert rows, just delete them.

Is this possible?

 

[Hardware Man]

Yes Joe, that would be fantastic if you could do that!

One other thing though...

I was thinking about it and I really do want the user to be able to use the "sort filter". This really is a must if I want him to be able to keep all of the SKU's in alphabetical order.

Since it doesn't appear that this ability is available on a locked sheet, I wonder if it would be possible to create a macro that would add this functionality? It would be best if the ability was enabled without any user input (such as clicking a button to confirm using sort).

 

[JoeMo]

Yes Joe, that would be fantastic if you could do that!

One other thing though...

I was thinking about it and I really do want the user to be able to use the "sort filter". This really is a must if I want him to be able to keep all of the SKU's in alphabetical order.

Since it doesn't appear that this ability is available on a locked sheet, I wonder if it would be possible to create a macro that would add this functionality? It would be best if the ability was enabled without any user input (such as clicking a button to confirm using sort).

Would you want the data sorted (Ascending or Descending?) whenever the sheet it is on is selected (activated)? Can you provide some layout info such as what column the SKUs are in, is there a header for that column, what cell is the header in....? And please confirm that you still want a separate, button-enabled macro to allow the user to select rows for deletion.

I'm going offline shortly and will have a look at this later today.

 

[Hardware Man]

In thinking about it yesterday, it really is essential for the user to be able to use the filter on all columns. There are just too many instances where this would be a useful tool (sorting by net profit, supplier, R.O.I. etc.).

Do you think there's a work-a-round whereby the filter could be enabled for all columns on a protect sheet? If it can't, I think I'll have to release the spread sheet unprotected and hope the user doesn't screw things up accidentally.

If it is possible, then that would be fantastic, and the icing on the cake would be to also be able to delete a protected row with a confirmation message box.

Thanks a million for your offer to help.

 

[JoeMo]

In thinking about it yesterday, it really is essential for the user to be able to use the filter on all columns. There are just too many instances where this would be a useful tool (sorting by net profit, supplier, R.O.I. etc.).

Do you think there's a work-a-round whereby the filter could be enabled for all columns on a protect sheet? If it can't, I think I'll have to release the spread sheet unprotected and hope the user doesn't screw things up accidentally.

If it is possible, then that would be fantastic, and the icing on the cake would be to also be able to delete a protected row with a confirmation message box.

Thanks a million for your offer to help.

You haven't given me any details about the layout of your sheet so this will likely need some tweaking and you will have to put your password between the quote marks where in indicated in red below. If you place a forms control button on the sheet and unlock it, the user can run the macro from the button. The basic idea is the user will be prompted to select entire rows or a single column header. If rows are selected they will be deleted, if a single column header is selected the data table will be sorted (ascending, but you can modify the code to change this) on that column header.

Sub SortColumnsOrDeleteRows()
Dim  msg As String, R As Range
msg = "Select the rows you want to delete (select entire rows), or a column header you want to sort (select a single header)."
On Error Resume Next
Set R = Application.InputBox(msg, Type:=8)
On Error GoTo 0
If R Is Nothing Then Exit Sub
If R.Columns.Count >= Columns.Count Then    'User has selected one or more rows
    ActiveSheet.Protect Password:="Your Pswd here", userinterfaceonly:=True
    R.Delete
    Exit Sub
End If
If R.Rows.Count = 1 And R.Columns.Count = 1 Then  'User has selected a column header
    ActiveSheet.Protect Password:="Your Pswd here", userinterfaceonly:=True
    R.CurrentRegion.Sort key1:=R(2), order1:=xlAscending, Header:=xlYes
End If
End Sub

 

[Hardware Man]

Hey Joe,

Thanks a million for this! My apologies for not replying sooner, but I haven't gotten into the office until now due to the recent storm.

I'll give this a shot and see if I can implement it correctly. Will let you know how I make out.

Thanks again!

 

[JoeMo]

Hey Joe,

Thanks a million for this! My apologies for not replying sooner, but I haven't gotten into the office until now due to the recent storm.

I'll give this a shot and see if I can implement it correctly. Will let you know how I make out.

Thanks again!

You are welcome - thanks for the reply.