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]

Wow! I didn't know that. In testing this, I found that using the "autosort" buttons do show the warning if a less-than-complete range is selected. However using the big Sort button does not give this warning. That's just plain incomprehensible.

 

[Greg Truby]

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.

If you were to put the code in the workbook's ACTIVATE/DEACTIVATE event handlers then it would only happen when the user activated the workbook in question. Do note that this would still leave SORT available on the popup right-click menu.

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

Possible, but much easier said than done.

 

[CHSLax60]

If you were to put the code in the workbook's ACTIVATE/DEACTIVATE event handlers then it would only happen when the user activated the workbook in question.

Now a little page thumbing tells me that I can even do this down to the worksheet level using "worksheet_Activate()", is that true?

Being fairly new to this marco/vb side of excel, I'm not exactly sure how to construct it so any help you could give me to shorten the trial & error process so I can get my weekend started would be greatly appreciated.

 

[Greg Truby]

Right-click the tab for sheet in question and you'll see a popup where one of the options is View Code. Click that. You'll hop in to the VBE and at the top of the code pane you'll see (General) on the left and (Declarations) on the right.

Click the left dropdown and pick Worksheet. The right will automatically change to SelectionChange. Click the right dropdown to Activate and drop Peter's code into the code stub. Then change the right side to Deactivate and put the code again inside the stub, chaning the FALSE to a TRUE.

Clean up (delete) the stub that was created for Worksheet_SelectionChange.

 

[CHSLax60]

Right-click the tab for sheet in question and you'll see a popup where one of the options is View Code. Click that. You'll hop in to the VBE and at the top of the code pane you'll see (General) on the left and (Declarations) on the right.

click the left dropdown and pick Worksheet. The right will automatically change to SelectionChange. Click the right dropdown to Activate and drop Peter's code into the code stub. Then change the right side to Deactivate and put the code again inside the stub, chaning the FALSE to a TRUE.

Clean up (delete) the stub that was created for Worksheet_SelectionChange.

Thank you sir. You are a gentleman and a scholar… and worthy of the silent toast preceding that first draw of a cold Shiner Bock in my very near future.