Hiding Active X control without disabling Undo

[StefC]

Hi There: I inserted and Active X control (checkbox) and want it hidden if a certain cell (V1) = FALSE. I have the following code I have entered:

Private Sub worksheet_calculate()
EnterMonthlyBox1.Visible = Not Range("v1").Value
End Sub

Problem is when I do this the UNDO function is completely disabled. Any suggestions of a better way to do this? I can't hide the row either because it just shifts the box down and it's still visible. I should also add that if I open any other spreadsheets after this one (even blank ones), my undo function is completely disabled (in case that is relevant or helps?)

The macro triggered by checking the box does not seem to be affecting my undo function, only the one I listed above.

Any suggestions would be appreciated.
Thanks,
Stef.

 

[Veritan]

Hi Stef. After looking around the internet for a while, it appears that the general consensus is a qualified yes. Running a subroutine will end up clearing the Undo history. However, this can be gotten around if you're willing to put the work into it. Here's an article from John Walkenbach that, in my opinion, did the best job of explaining what's going on when macros run regarding the Undo history. It also gives some example code you could try modifying. A more technical article, but one that is very in-depth and has a great deal of code you can work with, is found here. Ultimately, it's not going to be easy to prevent the Undo List from being cleared, but it can be done if you want to put in the time and effort required. The most common recommendation seemed to be simply saving a copy of the workbook and then working with the copy, using the original as a way to get back to a prior state if needed. Best of luck, hope these help!

 

[StefC]

Thank you for taking the time to dig and provide an answer. Before I go to too much trouble I may just simplify what I need the sheet to do without using that button and/or subroutine. I know my boss won't approve the cost/benefit of the time I'd likely spend on it. I'll just have to put on my thinking cap and might just go with the KISS principle on this one!