Sort and filter on a protected sheet

Xtamel

New member
Joined
Feb 10, 2024
Messages
4
Reaction score
0
Points
1
Excel Version(s)
365
Hello,

I am working on an Excel spreadsheet where I would like it to be possible to sort while the sheet is protected, as it contains formulas and will be used by multiple users. So far, I have only managed to add a macro that allows filtering and clearing filters, but I would also like sorting to be possible.

Could you help me, please?
 

Attachments

  • TEST Sort and Filter.xlsm
    27.6 KB · Views: 3
cross posted without links:

Xtamel, for your information, you should always provide links to your cross posts.
This is a requirement, not just a request.
If you have cross posted at other places, please add links to them too.
Why? Have a read of https://excelguru.ca/a-message-to-forum-cross-posters/
 
When you protect a sheet a dialogue pops up:
1707617169984.png
If you record a macro of you doing this and checking the Sort box, what code appears?

A bit of further digging reveals problems; have a look here:
 
When you protect a sheet a dialogue pops up:
View attachment 11552
If you record a macro of you doing this and checking the Sort box, what code appears?

A bit of further digging reveals problems; have a look here:
I cant use this, i have a macro that protect sheet automatically when add a row
 
possible to sort while the sheet is protected, as it contains formulas and will be used by multiple users
Where are these formulae? In the table?
I have some ideas but it depends on your answers to the 2 questions.

I would suggest you separate what the users see, from the data itself. By doing this, you can produce many 'reports' or versions of the data using other tables, pivot tables, charts from that hidden sheet.
In the attached, a start. The sheet you see is very similar to the sheet you had before but it's a very different and separate sheet.
Your original sheet is hidden and has been renamed. The sheet you see grabs data from the hidden sheet.
When you click the Add button, it adds a row to the hidden sheet's table then updates the sheet you see. That visible sheet retains its sort and filters during this operation, but since that visible sheet is not protected at all you can change these at any time.
As a demo, you can delete large parts of the data in the visible table, but when you click the Refresh button you'll see the data come back. That refresh operation is simply grabbing the data afresh from the hidden sheet.
 

Attachments

  • ExcelGuru11814TEST Sort and Filter.xlsm
    36 KB · Views: 1
Where are these formulae? In the table?
I have some ideas but it depends on your answers to the 2 questions.

I would suggest you separate what the users see, from the data itself. By doing this, you can produce many 'reports' or versions of the data using other tables, pivot tables, charts from that hidden sheet.
In the attached, a start. The sheet you see is very similar to the sheet you had before but it's a very different and separate sheet.
Your original sheet is hidden and has been renamed. The sheet you see grabs data from the hidden sheet.
When you click the Add button, it adds a row to the hidden sheet's table then updates the sheet you see. That visible sheet retains its sort and filters during this operation, but since that visible sheet is not protected at all you can change these at any time.
As a demo, you can delete large parts of the data in the visible table, but when you click the Refresh button you'll see the data come back. That refresh operation is simply grabbing the data afresh from the hidden sheet.
I really apreciate your work but its not what I´m looking for
 
I really apreciate your work but its not what I´m looking for
Finally I find a way to do it, maybe its not the best, but it helps me at the moment.

If someone Knows another better I would like to hear about it
 

Attachments

  • muestra - ORDENAR POR COLUMNA, FILTRAR COLOR Y BORRAR FILTROS.xlsm
    44.5 KB · Views: 1
Back
Top