Excel2007: Resetting Autofilters on a protected sheet in a shared workbook

Topic

New Reply

Hi

In a shared workbook, you can have Autofilters working on a protected sheet, provided you set AllowFiltering:=True as one of your protection options, and set this protection on before you share the workbook.

In the shared workbook, users can then set several filters, as required.
To clear and reset the filters, users can manually select each of those filters currently in use and set them back to show ‘All’.

I would like to simplify this and have a macro reset any filters to show all records.
Unfortunately, it doesn’t seem you can do this on a protected sheet in a shared workbook, once the workbook has been saved, and then re-opened.

To have a macro operate on a protected sheet, you need to include UserInterfaceOnly:=True as one of your protection options.
But UserInterfaceOnly:=True is not ‘retained’ in the workbook when you save the workbook, i.e. it is a ‘runtime only’ value.
When you re-open a saved shared workbook, UserInterfaceOnly:=True is ‘lost’, and you will get a ‘can’t do that on a protected sheet’ vba error.

In ‘unshared’ workbooks, you can easily re-implement the UserInterfaceOnly:=True option by turning your protection on again as part of the workbook open event.
But you can’t do this in a shared workbook, since you cannot change ANY protection state once a workbook has been shared.

So my question is:
Is there any way to set Autofilters to show ‘ALL’ records on a protected sheet in a shared workbook, other than to manually select each filter in use????
Has anyone managed to find a way to do this?

And for anyone wondering about shared worbooks, don’t ever use them. (Unless you are using Excel2013 web version).

zeddy

Reply | Quote

Replies

Can you workaround the restriction by saving the Workbook without the Worksheet being protected, but have the AutoOpen macro protect the Worksheet before users can get at it ?

Then intercept the Save routine to unprotect the Worksheet before saving.

Edit: I don’t think this will work – just trying to think outside the box and hoping it might give you some fresh ideas !

Reply | Quote

Hi Martin

Many thanks for thinking about this.
The problem relates to the protected sheet. The workbook could certainly be saved with the worksheet being unprotected. If the workbook is then saved in shared workbook mode, I can then have a pre-defined macro button reset Autofilters easily on the unprotected sheet and do as I need. However, if the workbook is in shared mode, you cannot change the protection status either manually or via vba. You can only change the protection status of worksheets in a shared workbook by unsharing the workbook first. Which is the catch-22 situation.
I would really like to keep the sheet in ‘protected’ mode in the shared workbook, for example to prevent users from ‘deleting’ the macro buttons etc.

I too have tried thinking outside the box.
So far, using the ‘unprotected’ sheet option, I can replicate the ‘can’t change locked cells’ feature of protected mode by using the change event to detect if cells have been ‘changed’ and if so, check if any of those cells include any cell formatted as ‘locked’, and if so, then use the Application.Undo option to reverse any changes made. I can also include a message box to replicate the ‘can’t change locked cells’ message usually shown when trying to change a locked cell on a protected sheet.
However, my message box is shown after the user presses the Enter key (since the ‘change’ has to occur to trigger the change event etc), whereas in a ‘normally protected sheet, the warning message is shown as soon as you type the first character when on a locked cell.

So I’m still looking for answers.
For example, is there a way to prevent shapes from being deleted or moved on an unprotected sheet????

zeddy

Reply | Quote

Hi Zeddy

I understand you are heading towards “how do I replicate protection” on the Worksheet in question, without using Excel’s inbuilt protection scheme and you have provided a workaround for when users try change stuff within cells. Sure you are trapping the event a little later but does that really matter as long as the end-result is the same ?

As for other things, eg shapes, that’s much more tricky as there isn’t an event you can trap. It sounds dreadfully clunky, but can you simply force restoration of them to their proper configuration . . .
. . . whenever the Worksheet change event is triggered
. . . whenever the Workbook is saved
. . . ??

I suppose a more radical alternative would be to re-design the Worksheet without the features you are struggling to control and put them elsewhere in the Workbook ie on a protected Worksheet.

Martin

Reply | Quote

Hi Martin

It’s the Client’s specific wishes that are driving this.
For the shapes, perhaps it might be possible to use the ‘before-right-click’ event, since a user would right-click on a shape in order to select it, move it, or delete it??
Does the Worksheet change event trigger if a user deletes a shape????
I suppose I could check named shape values, and restore them from a hidden master sheet if they were not found, and put them back in pre-specified locations.
Definitely getting clunky!

I might just go back to the protected sheet method, and tell them if you want to reset the autofilters, select each one manually as required to switch back to ‘All’.

Such is life.

zeddy

Reply | Quote

You can select a shape, then delete, you don’t need to right-click to get the context menu.

But even so, selecting a shape does not trigger a selection change, nor does right-clicking a shape trigger that event either…

The “ShowAll” menu item from previous versions has been replaced with the Data – Clear (under sort and filter), but like previous versions, it remains disabled in a protected worksheet so is absolutely no help here [even if autofilter is allowed – the “allowing” seems essentially to be only using the autofilter buttons]

Steve

Reply | Quote

I suppose I could check named shape values, and restore them from a hidden master sheet if they were not found, and put them back in pre-specified locations.

Quite so :rolleyes:

What are the shapes “for” ?

Reply | Quote

Hi Martin

The shapes are logos and form buttons with vba code assigned.
Some of the vba works OK on a protected sheet in a shared workbook.

It’s the Autofilters that can’t be controlled on a protected-sheet in a shared-workbook.
Interestingly, the code line..
ActiveSheet.AutoFilterMode = False
..will turn Off all the autofilters (and autofilter dropdown arrows) on a protected-sheet in a shared-workbook.
..it’s just that you cannot then subsequently turn them back on.

Regarding Excel’s ‘Custom Views’ facility, it is possible to pre-create and save a setting which shows filters in the all cleared state.
However, I have tested this in protected-shared-workbook mode and any such prior-saved custom view filter settings will NOT be shown if selected from the View>Custom Views option in the top-panel interface, or via vba. Pity.

I have also tested whether the Excel-session-dependant ‘enableUserInterfaceOnly’ can be set in a ‘trigger file’ before loading the protected-sheet shared-workbook.
Unfortunately, unlike calc mode and event-trapping mode, which can be set in one workbook and still take effect in another workbook, this cannot be done with ”enableUserInterfaceOnly’. Double pity.

So I’m back working in the protected-sheet shared-workbook mode.

Thanks for your interest.

zeddy

Reply | Quote

There are still possibilities in the infinite world of Excel VBA !

You can, in effect, assign a macro to a cell by trapping either the BeforeRightClick event or the BeforeDoubleClick event. With appropriate text in the cell (maybe saying “Right click here to . . . ” you can launch the Macro with something which cannot be moved . . .

For the Logo, can you move it to one of the Headers or Footers ? I’m sure you know the capabilities of that method but, just in case, there’s a nice summary here: http://spreadsheets.about.com/od/excelformatting/ss/2011-11-19-excel-watermark-tutorial.htm The result can look very “professional”.

Reply | Quote