Undo / Redo Button Shortcut or VBA Code

[TonyNZ]

Hi

In my worksheet (attached for quick reference), I have disabled Formula Bar and Headings (from View). This file is password protected. Password 8761.

I have ‘auto-hide’ ribbon too.

However, by doing this, the ‘undo’ and ‘redo’ buttons that sits on the top in the “Quick Access Toolbar” disappears. Yes, we can always take our mouse to the top, which reveals the blank bar and quick tap on this will reveal the Quick Access Toolbar. This works ok but feel cumbersome.

As a substitute to this, can we, somehow, (a) place these ‘undo’ and ‘redo’ buttons on e.g. Cell B2 / their Shortcut? (b) if not, can we write VBA and create a Macro to assign ‘undo’ and ‘redo’ functionalities, and assign this macro to an object / button / Active X control button which then can be placed around Cell B2?

Your help would be greatly appreciated.

Regards
Tony

 

[Peter Bartholomew]

I suspect you are doomed. My understanding is that once you have made changes to the workbook content using VBA you no longer have an undo stack, irrespective of the visibility of the buttons. The good thing is that undo/redo are mainly used at the workbook development stage when it is open and unprotected whereas the end user, working with the book in its protected, locked-down state should have little cause to use undo.

 

[TonyNZ]

Dear Peter. Thank for your reply. I agree with you. Let me explain better. Scenario: Workbook has 10 worksheets. Each worksheet has 10 Slicers. 10 slicers means there are 10 search parameters. User can keep narrowing down their search using Slicers, starting with one parameter followed by others. Yes, I understand that user can use the 'red' cross in each slicer to cancel the last or any search parameter (Slicer). This feature as I mentioned, if possible, will come in very handy to the user. My apologies if I am not able to make crisp clear my reasoning. On the similar lines, if this is possible, I would like to create Shortcut for 'clearing' all Slicers with one go. Ctrl + Z does work in the protected workbook also, with Slicers. Regards. Tony

 

[vletm]

TonyNZ
Like 'UNDO' would be possible to do, if You SAVE those Your parameters know place and reload & use those those 'like UNDO'.
As well as 'REDO' could create same way ( You could have list of used 'parameters' ).
I cannot test those because You've written to use those with 'some Slicers and ActiveXs' which I won't use.

 

[Peter Bartholomew]

Clearing or resetting the slicers would appear to be eminently doable.

    Sub ResetMonthSlicer()
    With ActiveWorkbook.SlicerCaches("Slicer_month")
        .SlicerItems("Jan").Selected = True
        .SlicerItems("Feb").Selected = False
        .SlicerItems("Mar").Selected = False
    End With

Something that would appear to be far harder is to build an UNDO stack because I have found no event that is triggered by changing a slicer setting. That gives you no opportunity to 'stash away' previous values or step-by-step changes.

 

[TonyNZ]

Dear Peter, Thanks very much for this solution. I will try this today. UNDO stack - I agree in what you say, it will be much harder to build. Regards Tony

 

[TonyNZ]

TonyNZ
Like 'UNDO' would be possible to do, if You SAVE those Your parameters know place and reload & use those those 'like UNDO'.
As well as 'REDO' could create same way ( You could have list of used 'parameters' ).
I cannot test those because You've written to use those with 'some Slicers and ActiveXs' which I won't use.

Thanks. I think Peter's solution will work better in the given circumstances. Appreciate your reply though.
Regards
t

 

[TonyNZ]

Dear Peter, Thanks very much for this solution. I will try this today. UNDO stack - I agree in what you say, it will be much harder to build. Regards Tony

Dear Peter. Thanks for giving me ideas. I have found a solution, which appears simple to a novice like me. I created a Shortcut to 'Clear' all slicers from the Analyze tab. Then I recorded a Macro using this shortcut to clear all slicers. Then I created a box and assigned this macro to the box. And it works perfect. I have attached file. i would appreciate if you can just have a look and see. Regards t

 

[Peter Bartholomew]

@TonyNZ
That's at least one part of your question with an elegant solution!

 

[TonyNZ]

Thanks Peter, much appreciated. So far, this has served my purpose and I am more than happy. Thanks again for your discussions. Regards t