Disable sorting

[PetarTen]

Hi Folks,
I have a File here Filebin | 2jezz8wdpo1sm01j that needs a bit of a tweak.
It needs to have Sorting disabled and was wondering "How to do it"?

It's a xlsm file and I think I found the right Code here:
Redirecting
```vba
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect userInterfaceOnly:=True, AllowFiltering:=True, _
AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=False
```
Unfortunately, I don't know where (and how) to add it.
Any help is much appreciated.
PS The problem is that, when the user chooses the 10 Cylinder (from A2 cell and then B2) there's no data coming up underneath A2 (BP oil)
All the rest (4, 6 and 8 Cylinder Engines) work like a charm.
I found that when change the figure to 9 (and not 10 Cylinder), the Table gives me all the requested info.
Obviously it messes with Sorting.

 

[xlhelp15]

Hi @PetarTen - Welcome to MrExcel !

Please use the xl2bb and update the details here instead of posting the files in somewhere.

To answer your question, it can be placed inside the vba editor based on your need.

 

[PetarTen]

Hi @PetarTen - Welcome to MrExcel !

Please use the xl2bb and update the details here instead of posting the files in somewhere.

To answer your question, it can be placed inside the vba editor based on your need.

Hi mate, I have no clue what you’re talking about! When I was asked WHERE to Post the Thread, I chose “Excel Group”.
What else should have done?
Please, if you know a better place for the Question (Thread), feel free to replace it
Best regards

 

[kvsrinivasamurthy]

Try this worksheet even code.

Private Sub Worksheet_Activate()
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect userInterfaceOnly:=True, AllowFiltering:=True, _
AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=False
End Sub

How to use worksheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm

 

[PetarTen]

Try this worksheet even code.

Private Sub Worksheet_Activate()
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect userInterfaceOnly:=True, AllowFiltering:=True, _
AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=False
End Sub

How to use worksheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm

Тhx mate, but it didn't work here.
Please check out the screenshot and see where the question-mark is
If you can download the file and add the Code for me (and test it), that'd be awesome!!!

PS Maybe I should RUN TEST and then Create (under Name) a Macro....and then close the VB window?

 

[kvsrinivasamurthy]

Before Pasting the code Select Worksheet Form the drop down instead of general.

 

[PetarTen]

Before Pasting the code Select Worksheet Form the drop down instead of general.

Surprises, surprises...
One after the other

PS I've made a Trusted Folder so that Excel wont bother me with "Security BS", but...this is something new here

 

[kvsrinivasamurthy]

Select the entire worksheet using ctrl+A.
In format cell-->protection uncheck locked.
Select the range not be sorted.
In format cell-->protection check locked.
Now protect the sheet with password.
Now the selected range cannot edited and sorted.
No need of VBA code.

 

[PetarTen]

Select the entire worksheet using ctrl+A.
In format cell-->protection uncheck locked.
Select the range not be sorted.
In format cell-->protection check locked.
Now protect the sheet with password.
Now the selected range cannot edited and sorted.
No need of VBA code.

I don't see the "Range", so I can "not sorted it"
Please, If you can make those changes on the File and send it over to me no matter Filebin or Dropbox, I'd highly appreciate it
...and we'll save a lot of time!

 

[PetarTen]

Select the entire worksheet using ctrl+A.
In format cell-->protection uncheck locked.
Select the range not be sorted.
In format cell-->protection check locked.
Now protect the sheet with password.
Now the selected range cannot edited and sorted.
No need of VBA code.

I even tried the VBA on Sheet2 (because the Table on Sheet1 takes the refs from Sheet2 and I though it might sort the problem)
Unfortunately - this also didn't work