Protect sort

[CHSLax60]

Here is my scenario. I have multiple people who need to enter and edit data in a worksheet. However, instead of just filtering to find what they need, people are sorting to find the things they need to edit BUT they aren't including all of the colunms when they sort. This is corrupting the data.

Protecting cells isn't the issue; besides they need to have the ability to edit all of the cells and save their changes. I basically need to protect the function. Is there a way to NOT allow people to resort the file but still allow them to filter it?

A second option would be that I can turn on the filter then if I could just hide the entire ribbon that would work as well.

 

[Greg Truby]

When you turn on worksheet protection, you can specify as to whether or not the user can use autofilter and whether on not they can sort. So that might work for you. Do note that in my testing (on Excel 2010), Excel doesn't behave as I would expect. Even though you set to allow user of Autofilter, the Autofilter command is disabled. You would have to turn on filtering before protecting the worksheet.

Having said that, generally this problem is caused by the existence of blank rows in a data block. If a block of data is all related then there should be NO blank rows or blank columns. If a user attempt to select only certain columns and sort them and there is contiguous non-selected data, the user will get a warning. My first solution would be to simply check for blank columns and remove any, if found.

 

[VoG]

To hide the ribbon

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""RIBBON"", FALSE)"

 

[Greg Truby]

Didn't know that one, Peter.

I'd use the trick Peter's showing you as a last resort. Users tend to get a little peeved if you start wiping out their interface. (Unless you're good enough to create a class module that intercepts workbook activation events and toggle ribbon visibility based on which WB is active. But, if you knew how to do that, you probably wouldn't be asking questions about ws protection issues. :wink: )

 

[VoG]

Hiya Greg

An alternative

Sub hide_unhide_Ribbons()
Application.SendKeys ("^{F1}")
End Sub

which may or not work.

Credit for the first one goes to Andy Pope.

 

[Greg Truby]

Peter,

Your first solution would work in that it also kills the Alt+D+F+F shortcut option. Simply using Ctrl+F1, only toggles the ribbon's "peek-a-boo" bit. So the keystroke shortcut remains available.

 

[CHSLax60]

When you turn on worksheet protection, you can specify as to whether or not the user can use autofilter and whether on not they can sort. So that might work for you. Do note that in my testing (on Excel 2010), Excel doesn't behave as I would expect. Even though you set to allow user of Autofilter, the Autofilter command is disabled. You would have to turn on filtering before protecting the worksheet.

Having said that, generally this problem is caused by the existence of blank rows in a data block. If a block of data is all related then there should be NO blank rows or blank columns. If a user attempt to select only certain columns and sort them and there is contiguous non-selected data, the user will get a warning. My first solution would be to simply check for blank columns and remove any, if found.

Thanks Greg but this is also making it to where they can't enter new data or edit the current date which they need to be able to do. Maybe there is a step I'm missing.

 

[Greg Truby]

Yes, the step you missed was to get smarter help. You did say you need them to edit and enter data, which protection is going to prohibit since there's no way to allow users to change values but not to sort using built-in protection.

Again - the primary solution would be ensure that there are no blank columns. Are there blank columns?

 

[CHSLax60]

Yes, the step you missed was to get smarter help. You did say you need them to edit and enter data, which protection is going to prohibit since there's no way to allow users to change values but not to sort using built-in protection.

Again - the primary solution would be ensure that there are no blank columns. Are there blank columns?

The problem is that it apears that some of the people (for whatever reason) are sorting they are only selecting 3 or 4 of the 7 columns that contain data. Why? I have no clue.

Now as for the first solution. Am I to take your comment after the first one to mean that this will disable the ribbon on the users computer even after this workbook is no longer active? If so, that would be a deal breaker. Is this true for the second one as well or is that one affecting the users application only while this workbook is open.

What about just hiding or disabling only the "sort filter" button?

 

[Greg Truby]

Hmmm, I searched here and perused some threads and they all basically say the same thing - using the built-in protection to prevent sorting.

If killing any blank columns and a bit of user education fails, then really about the only idea that occurs to me is the old "hotkey to edit" system where you turn on protectoin and then define a macro that will unprotect he sheet and use SENDKEYS to sent an F2 keystroke to put the user into edit mode. You then tie this macro to a hotkey (like Ctrl+E or something).

Or you can use the ONKEY to intercept F2, but that's a litte more involved.

Then you use the SELECTIONCHANGE event handler to restore protection.