AOB
Well-known Member
- Joined
- Dec 15, 2010
- Messages
- 660
- Office Version
- 365
- 2016
- 2013
- Platform
- Windows
I have a worksheet which is intended to be used for guided user entry. It contains one table and I have VBA code which dynamically applies validation options per cell etc.
I want the users to be able to sort the table as they please; however, while there are only 9 or 10 columns visible to the user, there are actually about 30 columns in the table. The other columns are hidden "worker" columns that I use to "manage" the logic (it's just so much quicker to use formulas than rely on VBA for everything)
So if the users want to use the native Sort option from the ribbon / context menu, they can see all 30 columns available from the Sort by dropdowns, which is obviously going to be very confusing as they will only be expecting to see 9 or 10 column names in there. I'd like them to only be able to see the visible columns of the table.
I have one option, which is to disable the native Sort function completely by protecting the sheet, and then create my own custom sort function using a form, which I could then add as a custom option from the context menu for the table (right-click) Obviously that gives me the flexibility to only populate the visible columns into the form (via a listbox or some suitable control) and use VBA to apply the sort to the table.
Another option I'd considered was to have two versions of effectively the same sheet; one which contains all the worker columns, but hidden, and a visible sheet linked to the hidden sheet with just the columns the user needs to see. So as they make changes to the visible sheet, the hidden sheet is updated and applies the necessary validations back to the visible. Really not keen on this as I think it will be difficult to manage all that back-and-forth accurately, not to mention the potential latency it would inevitably introduce.
But I just wanted to check if there might be any way of manipulating the native Sort to exclude certain columns, or only allow sorting of visible columns, before I go down either road because obviously that's going to take considerably more work and is adding complexity to something that's already complex as it is.
Would appreciate any suggestions?
I want the users to be able to sort the table as they please; however, while there are only 9 or 10 columns visible to the user, there are actually about 30 columns in the table. The other columns are hidden "worker" columns that I use to "manage" the logic (it's just so much quicker to use formulas than rely on VBA for everything)
So if the users want to use the native Sort option from the ribbon / context menu, they can see all 30 columns available from the Sort by dropdowns, which is obviously going to be very confusing as they will only be expecting to see 9 or 10 column names in there. I'd like them to only be able to see the visible columns of the table.
I have one option, which is to disable the native Sort function completely by protecting the sheet, and then create my own custom sort function using a form, which I could then add as a custom option from the context menu for the table (right-click) Obviously that gives me the flexibility to only populate the visible columns into the form (via a listbox or some suitable control) and use VBA to apply the sort to the table.
Another option I'd considered was to have two versions of effectively the same sheet; one which contains all the worker columns, but hidden, and a visible sheet linked to the hidden sheet with just the columns the user needs to see. So as they make changes to the visible sheet, the hidden sheet is updated and applies the necessary validations back to the visible. Really not keen on this as I think it will be difficult to manage all that back-and-forth accurately, not to mention the potential latency it would inevitably introduce.
But I just wanted to check if there might be any way of manipulating the native Sort to exclude certain columns, or only allow sorting of visible columns, before I go down either road because obviously that's going to take considerably more work and is adding complexity to something that's already complex as it is.
Would appreciate any suggestions?
