Prevent user from using Excel Built-in Sort functions

Question

I have a document that functions (using many already written and functioning Macros) to essentially stand as a visual representation of a calendar, which can then be exported to a powerpoint graphic. Because of the way this process is conducted, it is essential that empty rows are kept between each data row. I wrote my own sort and filter functions to preserve this condition while giving the user proper functionality, and now am working on document security.

Thus, the primary question I have (although this can be spread more generally to all worksheet actions) is whether or not it is possible to restrict users from ever using the default Sort functions from excel (as this would immediately destroy the data structure)?

To clarify, the sheet already has protect and unprotect functionality, but I need the AllowSorting property to remain false even when the sheet is unprotected. Is this possible?

Edit:
I attempted to solve the issue by not unprotecting the sheets, but instead protecting them with all protections "off" with the exception of the AllowSorting and AllowFiltering properties, which remain false in both the Locked and Unlocked configurations. However, this still allowed me to sort data when the sheets were "unlocked"

Edit 2: Since further explanation seems necessary, I already have Macros that protect and unprotect the sheet accordingly. I need to be able to give FULL functionality to any user who has the correct password, with the exception of sorting, as no user should ever use the sort functions. My code for these functions is shown (abbreviated) below:

Sub LockSheets()

For Each WS In ActiveWorkbook.Worksheets
        WS.Protect Password:=MasterPass, _
                DrawingObjects:=True, _
                Contents:=True, _
                Scenarios:=True, _
                AllowFormattingCells:=False, _
                AllowFormattingColumns:=False, _
                AllowFormattingRows:=False, _
                AllowInsertingColumns:=False, _
                AllowInsertingRows:=False, _
                AllowInsertingHyperlinks:=False, _
                AllowDeletingColumns:=False, _
                AllowDeletingRows:=False, _
                AllowSorting:=False, _
                AllowFiltering:=False, _
                AllowUsingPivotTables:=False
        Next WS
End Sub



Sub UnlockSheets()

For Each WS In ActiveWorkbook.Worksheets
            WS.Protect Password:=MasterPass, _
                DrawingObjects:=False, _
                Contents:=False, _
                Scenarios:=False, _
                AllowFormattingCells:=True, _
                AllowFormattingColumns:=True, _
                AllowFormattingRows:=True, _
                AllowInsertingColumns:=True, _
                AllowInsertingRows:=True, _
                AllowInsertingHyperlinks:=True, _
                AllowDeletingColumns:=True, _
                AllowDeletingRows:=True, _
                AllowSorting:=False, _
                AllowFiltering:=False, _
                AllowUsingPivotTables:=False
        Next WS

End Sub

Is it just the sort-function you dont want users to use or prevent them to change anything in that worksheet? — Tom K., Jun 21 '16 at 08:15
@Tom Specifically the sort function, although I'd like, purely to satisfy my curiousity, to know how to disable any specific built-in functions. I've built in mild securities to ensure that only users with a password (indicating that they have been trained in using the document) can do any editing procedures, but I want to avoid someone accidentally using a sort function while the document is editable — RGA, Jun 21 '16 at 08:19
You may want to have a look at this: http://stackoverflow.com/questions/28979396/excel-vba-filter-change-event-handler or this http://stackoverflow.com/questions/1362342/how-to-detect-if-filters-changed-on-a-worksheet and for disabling all changes you can always 'Protect' your worksheet via right-click on your sheet and check "Protect sheet..." :) — Tom K., Jun 21 '16 at 08:24
@Tom none of those options do what I need. I have built in functionality to protect the sheet from untrained users, but the users who have to edit the document need to be given full control, thus the sheet is unprotected. those two links don't help me, because those only detect if the Data has _already_ been filtered/sorted, which would be too late in my case, having already broken the data set. Thanks anyway though. Hopefully another answer will come along — RGA, Jun 21 '16 at 08:27

score 1 · Answer 1 · answered Jun 21 '16 at 11:10

1

There are two problems in your Unlock code as posted in your original post.

Contents:=False

will override protecting the sheet and is why the worksheet remains unprotected after you run the macro.

AllowDelectingRows:=True

should have given a Compile error: Named argument not found. due to the typo: AllowDele**c**ting Rows I don't understand why it did not on your system.

Here is the modified code:

========================================

Option Explicit
Sub UnlockSheets()
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
            WS.Protect Password:=MasterPass, _
                DrawingObjects:=False, _
                Contents:=True, _
                Scenarios:=False, _
                AllowFormattingCells:=True, _
                AllowFormattingColumns:=True, _
                AllowFormattingRows:=True, _
                AllowInsertingColumns:=True, _
                AllowInsertingRows:=True, _
                AllowInsertingHyperlinks:=True, _
                AllowDeletingColumns:=True, _
                AllowDeletingRows:=True, _
                AllowSorting:=False, _
                AllowFiltering:=False, _
                AllowUsingPivotTables:=False
        Next WS

End Sub

========================================

answered Jun 21 '16 at 11:10

Ron Rosenfeld

53,870
7
28
60

Yeah sorry about the typo. I corrected it in my code and forgot to correct it in the post. However, I had already tried changing "Contents" to True, but when I do that I lose more functionality than just the sorting (ie the insert Rows and columns options grey out on the ribbon). Incredibly frustrating – RGA Jun 21 '16 at 11:22
I also lose Merging functionality, which is essential for the document. I'm beginning to think I may just have to accept that I can't disable the sort and pray no one uses it – RGA Jun 21 '16 at 11:24
@RGA If you change your `AllowEditRanges` to include the entire worksheet `=$1:$1048576` you will be able to Insert Rows or Columns (but not Cells) – Ron Rosenfeld Jun 21 '16 at 11:39
Even doing this, I still lose other functionality. I've returned to a simple unprotect and just removed the sorting functions from the ribbon so that one wouldn't accidentally use them. Frustrating that there isn't a simpler solution that does what I need, but I appreciate you taking the time to try to help me figure it out – RGA Jun 21 '16 at 11:44
@RGA based on your final comments to Ron on his answer, would you kindly post your updated answer including code for removing of sorting function for benefit of others. Yours is an interesting situation of practical use. – skkakkar Jun 21 '16 at 12:23
@skkakkar I didn't use code, literally just removed the entire item from the taskbar in the settings window – RGA Jun 21 '16 at 13:34
@RGA Got it.Thanks. – skkakkar Jun 21 '16 at 13:37

skkakkar · Answer 2 · 2016-06-21T09:37:14.247

0

If you just want a working security for accidental misuse that happens in a workbook shared with others this should work in the Worksheet_Activate event

Private Sub Worksheet_Activate()
 Dim WhoCanSort As String
  WhoCanSort = ThisWorkbook.WriteReservedBy
   If WhoCanSort = "skkakkar" Then
     ActiveSheet.Unprotect
    Else:
     ActiveSheet.Protect AllowSorting:=False
   End If 
End Sub

EDIT Protecting worksheet elements By default, when you protect a worksheet, all the cells on the worksheet are locked, and users cannot make any changes to a locked cell. For example, they cannot insert, modify, delete, or format data in a locked cell. However, you can specify which elements users will be able to change when you protect the worksheet. Before you protect a worksheet, you can unlock the ranges that you want users to be able to change or enter data in. You can unlock cells for all users or for specific users.

Lock or unlock specific areas of a protected worksheet I think by following this method you may be able to prevent users from using any commands to sort data (Data tab, Sort & Filter group).Users can't sort ranges that contain locked cells on a protected worksheet, regardless of this setting.

edited Jun 21 '16 at 09:37

answered Jun 21 '16 at 08:30

skkakkar

2,772
2
17
30

Still not solving my problem, but closer to it. Essentially, I need `AllowSorting` to remain false _even when the sheet is Unprotected_. Is this possible? – RGA Jun 21 '16 at 08:37
Would it maybe be that I flip the functionality, rather than "unprotecting" the sheet (ie keep the sheet protected but remove the all protected aspects except for the sorting funcitonality?) – RGA Jun 21 '16 at 08:40
I dont know if this works, but maybe you could set all these properties to true, set AllowEditRanges to the range of your data and set AllowSorting to False. https://msdn.microsoft.com/en-en/library/office/ff839026.aspx – Tom K. Jun 21 '16 at 08:43
@RGA This solution does not allow sorting without entering password even if the sorting is visible. Other thing like disabling sorting by writing a routine for ribbon commands may be possible. Unfortunately my office system has just gone down and I am not able to test alternatives at the moment. – skkakkar Jun 21 '16 at 08:54
@Tom Thanks for your suggestion. Unfortunately I am not able to test alternatives as my Office system has just recently gone down. – skkakkar Jun 21 '16 at 08:57
@Tom I have deep respect for your expertise in data security, data protection and data privacy and shall work it out as soon as my Office system is restored. – skkakkar Jun 21 '16 at 09:05
Uuuh, what now? :D – Tom K. Jun 21 '16 at 09:05
1

@Tom You post your answer, if you feel that it meets OP requirements adequately. I shall give it due credit. – skkakkar Jun 21 '16 at 09:08
@Tom as per my edit to the original post, that solution (surprisingly) doesn't actually work (though I have no idea why) – RGA Jun 21 '16 at 09:14
1

You have to set AllowFiltering to False as well. See my answer. – Tom K. Jun 21 '16 at 09:17
@skkakkar FYI, i think that the way you've set up the protection aspect would prevent the other user from doing anything other than viewing the sheet, which is definitely not what I am asking for – RGA Jun 21 '16 at 09:28
@skkakkar this still is not aligned with my desired functionality but thank you anyway – RGA Jun 21 '16 at 09:51

Tom K. · Answer 3 · 2016-06-21T09:22:22.153

0

Ok, here it is. I put this into the Workbook_open-event, as I think, this is the best place.

Private Sub Workbook_open()
ActiveSheet.Protect _
                AllowDeletingColumns:=True, _
                AllowDeletingRows:=True, _
                AllowFormattingCells:=True, _
                AllowFormattingRows:=True, _
                AllowFormattingColumns:=True, _
                AllowInsertingHyperlinks:=True, _
                AllowInsertingRows:=True, _
                AllowUsingPivotTables:=True, _
                AllowSorting:=False, _
                AllowFiltering:=False

ActiveSheet.Protection.AllowEditRanges.Add _
                Title:="yourtitle", _
                Range:=Range("yourrange")

End Sub

This allows everything, except sorting. All other properties are listed here

edited Jun 21 '16 at 09:22

answered Jun 21 '16 at 09:15

Tom K.

1,020
1
12
28

Unfortunately (as explained in the edit to my original post), this does not work and still allows sorting. I honestly have no idea why that is though – RGA Jun 21 '16 at 09:20
Definitely works on my end. I edited the post, maybe you missed something? – Tom K. Jun 21 '16 at 09:22
I am certain I've missed nothing. Already wrote that code myself and attempted it. When that didn't work, I copy-pasted yours and it still does not prevent sorting – RGA Jun 21 '16 at 09:27
Does it throw any errors or is it just not working? And: what version of Excel do you use? – Tom K. Jun 21 '16 at 09:28
No errors, just still allows sorting. I'm dumbfounded because everything points to that solution being correct (so much so that I posted that as an answer myself before testing it and subsequently deleted the answer when it didn't work) – RGA Jun 21 '16 at 09:29
@RonRosenfeld Yes. All cells are locked by default (and I am preventing action on the entire sheet so there is no need to adjust that range) – RGA Jun 21 '16 at 09:34
@RGA Did you unprotect the sheet? I can confirm that Tom's solution seems to work here, also allowing editing of the desired range. – Ron Rosenfeld Jun 21 '16 at 09:38
Do you use `ActiveSheet.Protect AllowSomething:=whatever` later on or at some other point? Because it resets all properties except for the specified one to false. – Tom K. Jun 21 '16 at 09:39
@RGA Another thought. Perhaps there is something else in your code that is unprotecting the sheet. Can you check what shows up in the `Review ► Changes` tab? Does it say `Protect worksheet` or `Unprotect worksheet`? – Ron Rosenfeld Jun 21 '16 at 09:40
@RonRosenfeld For whatever reason, after the "UnlockSheets" sub is run, the sheet still says "protect worksheet", while the "Locksheets" leaves the expected "unprotect" command. I am certain that there are no "unprotect" calls being made anywhere though – RGA Jun 21 '16 at 09:46
@RGA Can you post a link to a "sanitized" copy of the workbook that demonstrates the problem? – Ron Rosenfeld Jun 21 '16 at 09:47
@RonRosenfeld Unfortunately I can't due to the sensitivity of the data containted. – RGA Jun 21 '16 at 09:48
@RonRosenfeld Interestingly, after running the macros, if I click on `Protect Worksheet`, the sort, filter, and pivot table properties are unchecked, so it seems to be recieving my desired settings just not acting accordingly – RGA Jun 21 '16 at 09:51
@RGA If the sheet is reading `Protect` then it is not protected and sorting will be allowed. – Ron Rosenfeld Jun 21 '16 at 10:03
@RonRosenfeld To confirm, I added code to check if the sheet was protected after the protect call was made in the unlocksheets sub, and it is indeed not protected. Do you have any idea why a sheet would not be protected _immediately after_ the protect method is called?? – RGA Jun 21 '16 at 10:08
@RGA I do not see any variable declarations in your code. Select in `Options` to require variable declarations and place `Option Explicit` at the top of your code. Use a breakpoint and understand what `WS` is referring to on each loop through your macro. – Ron Rosenfeld Jun 21 '16 at 10:15
@RGA Also ensure that `Break on unhandled errors` is set. – Ron Rosenfeld Jun 21 '16 at 10:17
@RonRosenfeld I didn't include all the code because I felt it was unnecessary, being a fairly experienced VBA user. The variables are properly declared and function as expected. the only part that is not functioning is the protect call in the "Unlock" sub. It functions as expected in the "Lock" sub and the code is identical except for the values specified – RGA Jun 21 '16 at 10:23
@RGA See my answer – Ron Rosenfeld Jun 21 '16 at 11:06
@RonRosenfeld Maybe a bit unnecessary to open yet another answer for that. ;) – Tom K. Jun 21 '16 at 11:12
@Tom Maybe, but I thought the part about the `Contents` argument apparently overriding the `Protect` to be worthy of inclusion in an answer, where it might be more likely to be seen by others searching for similar issues. – Ron Rosenfeld Jun 21 '16 at 11:17

Prevent user from using Excel Built-in Sort functions

3 Answers3