Why do some Excel documents NOT allow me to Undo/Redo when using THIS Macro?

[Sandcastle]

Hello Everyone,

Some of my Excel files work perfectly in terms of undo/redo, while others simply cannot use that feature. I took a stroll through the internet looking for the answer, but the issues that I found seemed to point to the application losing its ability, which was resolved with a registry edit/update/change. I don't think that's the case here because I CAN undo is SOME documents.

After some investigation with my problem documents I have found that THIS Macro is the one thing all my undo-free documents have in common:
http://youtu.be/bGjqDGF7xaM

Does anyone know how to fix this?

Thank you.

[zumbalj]

I am not able to access youtube from this computer, so I don't know about the macro specifically. I do know that once a macro is run, you can't undo anything you did before it was run (it basically resets that list of actions to nothing). I would guess that the macro runs automatically fairly frequently in the background, which would cause what you're seeing.

[Sandcastle]

I do know that once a macro is run, you can't undo anything you did before it was run (it basically resets that list of actions to nothing). I would guess that the macro runs automatically fairly frequently in the background, which would cause what you're seeing.

Wow, that seems silly. I would expect the operations of the Macro to be separate from the history of the document's actions. I come from the world of graphic design so I am used to things like Adobe's History pallets.

What this Macro is doing is highlighting the current line, so your description explains what's going on. Here are the instructions that I wrote to a help file, created from the video's instructions:

Highlight Selected Cell’s Entire Row (Via Macro, in Excel 2010)
Source: http://www.youtube.com/watch?v=bGjqDGF7xaM

1. Click on FORMULAS (tab)
   1. Click on DEFINE NAME (button), then enter:
      NAME: ActiveRow
      REFERS TO: =1
2. Click on CONDITIONAL FORMATTING (button in HOME tab)
   1. Click on MANAGE RULES…
   2. Click on NEW RULE…
   3. Choose USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT
   4. Enter this formula:

      Please Login or Register  to view this content.

   5. Click on FORMAT (button) and style how you want the highlighted row to look
   6. Accept the appearance (OK)
   7. Accept the formula (OK)
   8. In your list of rules ensure that this rule applies to:

      Please Login or Register  to view this content.

3. Click on DEVELOPER (tab)
   1. Click on VISUAL BASIC (button)
   2. Double-click on THIS WORKBOOK (in list on left)
   3. Click on GENERAL (1st of 2 menus on the newly opened right side) and choose WORKBOOK
   4. Click on OPEN (2nd of the 2 menus) and choose SHEETSELECTIONCHANGE
   5. Select all of the code in this window and replace it with:
      

      Please Login or Register  to view this content.

4. Close the code window (Visual Basic editor)
5. Save the document as a MACRO-ENABLED WORKBOOK (*.XLSM)
   • Note: Apparently you will lose the ability to Undo as the Macro resets the list of actions.

[zumbalj]

Does that seem like it might be your solution with the way the workbook is used, or do we need to try to think of something else that might cause this?

[Sandcastle]

Does that seem like it might be your solution with the way the workbook is used, or do we need to try to think of something else that might cause this?

I am in the process of duplicating the workbook and removing the Macro. I'll report back in a few moments. Due to the size of my workbooks, this row highlighting is very useful.

[Sandcastle]

I am in the process of duplicating the workbook and removing the Macro. I'll report back in a few moments. Due to the size of my workbooks, this row highlighting is very useful.

OK, just removed the Macro and BLAM: Undo works again.
Is there another way to have row highlighting without losing undo?
Here is my file, redacted:
Highlight Rows.xlsm

[zumbalj]

Without making it a manual process, none that I am aware of (although I'm the first to admit, I don't know everything, so it IS still possible).

[ragulduy]

I think to get this to work without a macro you would need a way of returning whatever the selected cell is, you can use something like:
=CELL("row")
which sort of gives you the activerow but it won't change by selection, you would need to actually get excel to recalculate for it to work (i.e. press enter on a cell or force calculate CTRL ALT F9.

You could then replace your conditional formatting with:
=if(Cell("row")=row(A1),True,False)

but again, you would need to actually recalculate - it won't change with selection.

I'm not sure how you would go about getting it to change with selection without using a macro.

Edit: Actually, I don't think that the macro will "break" the undo history if you just get it force calculation. So change your conditional formatting to:
=if(Cell("row")=row(A1),True,False)
and your macro to

Please Login or Register  to view this content.

and that should work ok, note - you don't need the named range bit in this case.

[Sandcastle]

Edit: Actually, I don't think that the macro will "break" the undo history if you just get it force calculation. So change your conditional formatting to:
=if(Cell("row")=row(A1),True,False)
and your macro to

Please Login or Register  to view this content.

and that should work ok, note - you don't need the named range bit in this case.

I just tried this. I replaced the VB to what you supplied, then replaced the Conditional Formatting to what you provided. No love. Would you mind trying it in my excel doc, so that I can see what you did?

Thanks

[ragulduy]

Sorry, I didn't realise the macro was in the thisworkbook object. Delete the existing macro you had and then put the new one in the "Sheet1 (On)" object.

I've tried it on your example workbook and it seems to be ok.

[Sandcastle]

Thank you for your help. I am not entirely sure that I am doing it correctly so I've uploaded what I did.
Highlight Rows v2.xlsm

[ragulduy]

You are almost there, you need to put the code in the worksheet, not the workbook in the vba editor: V3.xlsm

[Sandcastle]

You are almost there, you need to put the code in the worksheet, not the workbook in the vba editor: Attachment 250289

Oh geez! I didn't DOUBLE click on the worksheet so I thought I pasted it there! Duh!

Thank you, I appreciate it. Problem SOLVED!

[ragulduy]

No problem, it was an interesting problem and it's actually quite a neat trick. Might start using it myself sometimes.

[Sandcastle]

No problem, it was an interesting problem and it's actually quite a neat trick. Might start using it myself sometimes.

It might be too soon to celebrate. I've updated all my documents to reflect this new method and it indeed does enable me to undo, however I cannot maintain a selection. Example, click a cell, or range then copy it. You get the "marching ants" running around it, but as you click something else, you lose the selection.

Strangely, it only happens in my full-scale documents and not the test file we created here. Clearly I have a conflict.

[ragulduy]

Try changing the macro line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

?

[zumbalj]

Would it help if you added lines to the code to define what's selected at the start of the macro as a range, and at the end, tell it to select that range again?

[JosephP]

forcing a calculation clears the clipboard. I reckon you have two options
1 lock the clipboard, calculate then close the clipboard. this will allow you to paste, but only as values
2 skip the calculation if the application is in cut/copy mode

[ragulduy]

Did you try using .calculationfull? When I tried it using .calculation cleared the clipboard but .calculationfull did not.

[Sandcastle]

Try changing the macro line to

Please Login or Register  to view this content.

I just did that and was able to paste, with formatting too.

Excel has been behaving oddly since this change of code though. Like it's trying to function on 1MB of RAM.

[ragulduy]

If you've got a big spreadsheet I imagine it would, you are forcing it to recalculate every cell every time you change selection...

[Sandcastle]

Man, this is a lot of fuss over highlighting a row, lol

[JosephP]

plus calculatefull will recalculate all open workbooks

[ragulduy]

Maybe integrating what Joseph suggested:

Please Login or Register  to view this content.

might be slightly more cpu-friendly

[Sandcastle]

Thank you everyone.
I'm going to work with this update for a while and see how things work out through the day.

[JosephP]

I reckon all the cf is gonna slow everything down anyway-the cost would outweigh any benefit for me ;-)

[Sandcastle]

It may get to that point, indeed. The row highlight is very helpful to my workflow, but if it cripples me then obviously I'll have to revert back to SHIFT + SPACEBAR.

[Peter_Excel]

I just came accros this thread and it's exactly what I was looking for, great solution. The only problem I'm having is if I have 2 workbooks open it calculates both and highlights the same row# on both. Is there a way to only calculate the active workbook active sheet so the last highlighted row remains unchanged on the other open workbook?

[TMS]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

[eagle4x]

I had same problem. I saved the file as Excel Workbook and then "undo" started working.