Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Worksheet_Calculate Loses the Undo function
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 34
Default Worksheet_Calculate Loses the Undo function
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 :)

*** Sent via Developersdex http://www.developersdex.com ***
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 34
Default Worksheet_Calculate Loses the Undo function
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



*** Sent via Developersdex http://www.developersdex.com ***
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Worksheet_Calculate Loses the Undo function
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 wrote in message ...
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 :)

*** Sent via Developersdex http://www.developersdex.com ***


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 34
Default Worksheet_Calculate Loses the Undo function
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 :)


*** Sent via Developersdex http://www.developersdex.com ***
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,726
Default Worksheet_Calculate Loses the Undo function
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 wrote in message ...
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 :)


*** Sent via Developersdex http://www.developersdex.com ***


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 34
Default Worksheet_Calculate Loses the Undo function
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: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: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: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: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


*** Sent via Developersdex http://www.developersdex.com ***
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 63
Default Worksheet_Calculate Loses the Undo function
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<SHIFTU.

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("C1:D1,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.

On Feb 13, 1:31 am, Aria <Aria wrote:
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: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: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: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: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

*** Sent via Developersdexhttp://www.developersdex.com***


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 34
Default Worksheet_Calculate Loses the Undo function
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 :)

*** Sent via Developersdex http://www.developersdex.com ***
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pasting with multiple sheets selected loses undo history- why? Ruthhd Excel Discussion (Misc queries) 0 July 1st 08 11:09 AM
Excel's Undo Function sp3cialist Excel Worksheet Functions 1 February 8th 06 07:01 PM
Why is my undo function in Excel only can undo the last 1 or 2 ch. 1111111111111111111111111111111111111111 Excel Worksheet Functions 1 November 24th 04 11:13 AM
Recordset object loses validity in call between function & subrout Dick Kusleika[_3_] Excel Programming 1 August 13th 04 08:22 AM
User-Defined Function pre-empting Built-in Function? How to undo???? MarWun Excel Programming 1 August 6th 03 09:31 PM


All times are GMT +1. The time now is 02:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement