Worksheet_Calculate Loses the Undo function

[Aria]

Hello,
I'm using the Worksheet_Calculate code but unfortunately, the Undo icon
is grayed out indefinitely. Any workaround to preserve the Undo
function while keeping my Worksheet_Calculate code?

Please help.

Thanks,
Aria

 

[Aria]

One thing to add, I use Worksheet_Calculate to point only to cell A6
(which is formulated) so when that cell changes, the code will trigger.

The problem is that I lose the Undo function. Any ideas? I need to use
that Undo function.

Thanks,
Aria

 

[Bob Phillips]

That is just the way Excel/VBA works. If you need it, you will have to
preserve it yourself, or maybe just call the Undo method from your code.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Aria]

Hi again Bob,
Glad you replied. I'm working on the same spreadsheet that you helped me
with earlier. So how do I call the Undo method from code? Would I add
a button and label it as Undo and would it work the same way as the
original Undo icon where the user could Undo the previous 5 or so
entries?

Thanks again,
Aria

 

[Bob Phillips]

It depends on what circumstances that you want to use undo. You mentioned
earlier that you were using the calculate event, so presumably you have some
code in there, and if a certain condition is met, you want to undo some
action.

What is your code?
What is the condition?
Which action do you want to undo?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Aria]

Hi Bob,
I'm using the Worksheet_Calculate event to hide some columns dependant
on the formula updated change in cell A6. When users enter data
anywhere on the sheet, I'd like them to be able to use the Undo icon in
case they want to revert back to the original data prior to overwriting.
How do I make changes to my code to make it possible? Is it possible to
lock cell A6 to calculate only when it's changed? So that the Undo icon
would still be feasible? Here's my code:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Range("A6").Value = 1 Then
Range("B:B").EntireColumn.Hidden = False
Range("C:C").EntireColumn.Hidden = True
Range("D").EntireColumn.Hidden = True
Range("E:E").EntireColumn.Hidden = True
Range("AA:AA").EntireColumn.Hidden = False
Range("AB:AB").EntireColumn.Hidden = True
Range("AC:AC").EntireColumn.Hidden = True
Range("AD:AD").EntireColumn.Hidden = True
ElseIf Range("A6").Value = 2 Then
Range("B:B").EntireColumn.Hidden = False
Range("C:C").EntireColumn.Hidden = False
Range("D").EntireColumn.Hidden = True
Range("E:E").EntireColumn.Hidden = True
Range("AA:AA").EntireColumn.Hidden = False
Range("AB:AB").EntireColumn.Hidden = False
Range("AC:AC").EntireColumn.Hidden = True
Range("AD:AD").EntireColumn.Hidden = True
ElseIf Range("A6").Value = 3 Then
Range("B:B").EntireColumn.Hidden = True
Range("C:C").EntireColumn.Hidden = False
Range("D").EntireColumn.Hidden = False
Range("E:E").EntireColumn.Hidden = True
Range("AA:AA").EntireColumn.Hidden = True
Range("AB:AB").EntireColumn.Hidden = False
Range("AC:AC").EntireColumn.Hidden = False
Range("AD:AD").EntireColumn.Hidden = True
ElseIf Range("A6").Value = 4 Then
Range("B:B").EntireColumn.Hidden = True
Range("C:C").EntireColumn.Hidden = True
Range("D").EntireColumn.Hidden = False
Range("E:E").EntireColumn.Hidden = False
Range("AA:AA").EntireColumn.Hidden = True
Range("AB:AB").EntireColumn.Hidden = True
Range("AC:AC").EntireColumn.Hidden = False
Range("AD:AD").EntireColumn.Hidden = False
Else
Range("B:AD").EntireColumn.Hidden = False
End If
Application.EnableEvents = True
End Sub

 

[Carl Hartness]

Like Bob says, there's no undo for macros. I like to pull the ranges
which could change into arrays, then dump them to another sheet. You
could use two sets of saved values, with a cell indicating which is
most recent, and your change macro updating the older set and marking
it as the newest. Make a macro to copy the older values back, and run
it with a button or something like <CTRL><SHIFT>U.

Do you have Calculation set to Manual or Semiautomatic? If it is set
to Automatic then I would use the Worksheet_Change event, as in
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'
' save values here
'
Select Case Range("A6").Value
Case 1
Range("B1,AA1").EntireColumn.Hidden = False
Range("C1:E1,AB1:AD1").EntireColumn.Hidden = True
Case 2
Range("B1:C1,AA1:AB1").EntireColumn.Hidden = False
Range("D1:E1,AC1:AD1").EntireColumn.Hidden = True
Case 3
Range("C11,AB1:AC1").EntireColumn.Hidden = False
Range("B1,E1,AA1,AD1").EntireColumn.Hidden = True
Case 4
Range("D1:E1,AC1:AD1").EntireColumn.Hidden = False
Range("B1:C1,AA1:AB1").EntireColumn.Hidden = True
Case Else
Range("B1:AD1").EntireColumn.Hidden = False
End Select
Application.EnableEvents = True
End Sub

Carl.

 

[Aria]

Hi Carl,
I like how you have cleaned up my code. Thanks for that. I'm not
looking for an Undo macro to reverse what the code has done but rather,
when a user does their data entries and they wish to revert back to
their previous entry, they usually go right to the Undo icon. But
because of the Worksheet_Calculate or Worksheet_Change events, that
convenient Undo feature is no longer usable.

Looks like there's no way around this. So I may have to look at putting
the code into a module and manually activating it so that I can preserve
the Undo arrow icon's usage.

Thanks,
Aria