VBA is stopping me from being able to undo

**FraserMc97** · 07-19-2017, 11:12 AM

Hi

So some of the excel wizards on here have created some coding for me to count coloured cells from conditional formatting however I have just realised that I can not undo anything because of this!

I'm not sure whether it is the following module:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult
    lCol = rColor.DisplayFormat.Interior.ColorIndex
    If SUM = True Then
        For Each rCell In rRange
            If rCell.DisplayFormat.Interior.ColorIndex = lCol Then
                vResult = WorksheetFunction.SUM(rCell, vResult)
            End If
        Next rCell
    Else
        For Each rCell In rRange
            If rCell.DisplayFormat.Interior.ColorIndex = lCol Then
                vResult = 1 + vResult
            End If
        Next rCell
    End If
    ColorFunction = vResult
End Function

Thanks

**CK76** · 07-19-2017, 11:16 AM

By default, all VBA action cannot be undone. You just have to save it before you run the code and if you want to cancel the result, load without saving.

VBA clears any "Undo" cache as well. So you will need some code to reverse the operation.

Read link for more detail (and sample way to do it in code).
http://spreadsheetpage.com/index.php...ba_subroutine/

And below link for way to write class module to handle this.
http://www.jkp-ads.com/Articles/UndoWithVBA00.asp

**FraserMc97** · 07-19-2017, 11:21 AM

The problem is that it updates every time I click which in one way is good however I would rather be able to undo and just update the vba manually e.g CTRL ALT F9

Is there a way to change this?

**CK76** · 07-19-2017, 11:27 AM

So you must be using the UDF in some event code (forgot to mention that UDF alone won't negate Undo cache, when event code/subroutines are run, that's when Undo is cancelled).

Can you upload sample workbook, and demonstrate exactly how you are using this UDF?

**FraserMc97** · 07-19-2017, 11:50 AM

Okay so I have attached the excel spread sheet, I have just taken that section of a spread sheet and edited all the data as it was confidential and I have adjusted formulas so it should all work now.

So I am using the VBA to count the number of coloured cells in column T so I can work out an appraisal percentage.

**MrShorty** · 07-19-2017, 12:05 PM

It is not the Function procedure that is clearing the Undo stack, it is the selectionchange event procedure that clears the undo stack.

You can create an "on undo" procedure in which you can put whatever code you need to "undo" the actions of the selectionchange procedure, but you still cannot preserve the undo stack before that point. https://msdn.microsoft.com/VBA/Excel...o-method-excel

If being able to undo is critically important to what you are doing in Excel, it might be wisest to figure out how to use that function procedure in a cell rather than calling it from a selection change event.

**FraserMc97** · 07-19-2017, 12:07 PM

So is there no way I can stop it automatically updating with every cell click?

**Winon** · 07-19-2017, 12:18 PM

Hello FraserMc97,

So is there no way I can stop it automatically updating with every cell click?

Apart from what MrShorty stated, the answer is no, there is no way you can stop it automatically updating with every cell click.

Regards.

**FraserMc97** · 07-19-2017, 12:24 PM

Right okay, thanks for the help, I might need to find out whether there is another code I can use which will not update until opening the workbook or pressing CTRL ALT F9 because I need to be able to undo really...

**CK76** · 07-19-2017, 12:25 PM

It's usually bad idea to base something off of cell color. Instead, utilize data validation list and/or helper column to track status.

Use that value in formula (be it UDF or otherwise) and avoid use of VBA subroutines and event codes to retain ability to do Undo.

If you need to retain current format for Sheet1. Create data table where status etc are tracked and use formula to update Sheet1.

**FraserMc97** · 07-19-2017, 12:33 PM

I tried using formulas to get the answer I needed however conditional formatting was the only way I could figure it out as some rules may take priority over others.

I'm not overly advanced with excel and the conditional formatting has worked out well, why isn't there a formula to count colour cells

**MrShorty** · 07-19-2017, 02:15 PM

So is there no way I can stop it automatically updating with every cell click?

What exactly are you trying to do here?

To stop cells T3 and U3 from updating with each cell click, delete the "selection change" event procedure (this is what "selection change" means -- every time you click a different cell, the selection changes, and the selection change event is triggered).

I could see putting =colorfunction(T2,T9:T158) into T3 then copy over to U3. As the function is written, these cells would only update when you enter the cell, press F2 and enter (since cell color will not always trigger these cells to calculate since cell color change is not something that fits into the calculation dependency tree). You could add Application.volatile to the UDF, then it would calculate with each calculate event (color changes will not usually trigger a calculate event, though F9 triggers a calculate event). Using a VBA UDF inside of a cell like this does not effect the undo stack.

Those are just some possible ideas. In the end, this is a case where I think you need to think through exactly what you are trying to do with these cells and this spreadsheet so you can figure out a good way to achieve these goals.

If it helps, I would agree with CK76 -- cell color is not a good "datum" to use in a spreadsheet. Perhaps the best approach to this is to come up with a different datum type to base these cells on. Again, this probably means going back to the beginning and thinking through exactly what this sheet is supposed to do.

**FraserMc97** · 07-20-2017, 10:01 AM

I tried deleting the selection change procedure and entering the function into cells T3 and U3 and it just returns #VALUE! error

**MrShorty** · 07-20-2017, 10:22 AM

I am not in a position to debug for you right now. As I explain in this post (https://www.excelforum.com/tips-and-...uild-udfs.html ), one of the first things I would do would be to add a stop statement to the beginning of the function to enter debug mode. A #VALUE! error like that usually indicates that there is some critical syntax error in the function call (wrong number of arguments or incorrect data types or something) or some syntax error in the function that is causing it to terminate early. If you add the Stop statement and it fails to enter debug mode, then something is wrong with the function call. If it enters debug mode, then you can step through the function to find the statement that is causing it to terminate early (and maybe get a more meaningful error message).

**FraserMc97** · 07-20-2017, 12:22 PM

I have managed to use the procedure but on a command button which seems to work

this way I can still undo any changes unless I press the button to update the values which is fine as the whole workbook is massive with around 30 tabs which is updated by many different people who will not be using this feature anyway

Thanks for the help though

VBA is stopping me from being able to undo

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA is stopping me from being able to undo

Re: VBA is stopping me from being able to undo

Re: VBA is stopping me from being able to undo

Re: VBA is stopping me from being able to undo

Re: VBA is stopping me from being able to undo

Re: VBA is stopping me from being able to undo

Re: VBA is stopping me from being able to undo

Re: VBA is stopping me from being able to undo

Re: VBA is stopping me from being able to undo

Re: VBA is stopping me from being able to undo

Re: VBA is stopping me from being able to undo

Re: VBA is stopping me from being able to undo

Re: VBA is stopping me from being able to undo

Re: VBA is stopping me from being able to undo

Re: VBA is stopping me from being able to undo

Thread Information

Users Browsing this Thread

Similar Threads

Hope this is not impossible. An Undo Handler/ Macro to undo using keyboard shortcut

[SOLVED] Undo command has changed to Can't Undo

Copy Row, RtClick Row, Insert Copied Cells - pauses and auto undo! No undo path

Code is stopping undo

Undo function to undo Visual Basic commands

VBA Undo & Excel's built-in Undo

[SOLVED] UNDO - how many times can I UNDO?

Bookmarks

Bookmarks

Posting Permissions