UNDO and REDO buttons are inactive

[Shailesh]

I have created a Pivot table having one Macro in the
Worksheet_calculate sub.In this macro I dynamically change the
formula of a calculated field depending upon the selection made in one
of the page fields.

How ever I am having a strange problem in this pivot. Here UNDO and
REDO buttons are disabled/

Can any one please suggest me how to correct this?

 

[Tom Ogilvy]

Most macro code clears the undo history as a by product of running.

It appears your code fits into this category.

 

[Shailesh]

First of all thanks for your time and apologies for direcly copying
you .I am doing this as this report is urgent for me and ai am struck
in it.

Is there any remedy to it. Or do you have any other solution for what
I am doing with this macro?

I have created a Pivot table and to dynamically calculate a calculated
field depending upon the selection in a page field I have written a
small piece of VBA code in Worksheet_Calculate event.
But this disables/deactivates Undo/Redo button at the top.

For your reference the VBA code is written below.

Private Sub Worksheet_Calculate()
On Error Resume Next
Application.EnableEvents = False

Select Case ActiveSheet.PivotTables("PivotTable1").PivotFields("Income
").CurrentPage
Case "(All)"

ActiveSheet.PivotTables("PivotTable1").CalculatedFields( _
"INC%").StandardFormula = "=inc_segT/appr_apps"

Case "HIC"
ActiveSheet.PivotTables("PivotTable1").CalculatedFields( _
"INC +%").StandardFormula = "=inc_segH/appr_apps"

Case "NHIC"
ActiveSheet.PivotTables("PivotTable1").CalculatedFields( _
"INC- %").StandardFormula = "=inc_segN/appr_apps"

End Select

Me.Calculate
Application.EnableEvents = True
End Sub

 

[Susan]

tom said, (translated somewhat differently):

running ANY code clears the undo/redo history.
macros cannot be undone unless they are coded that way.
so once you run the macro, you have nothing to undo, until you have
done other things.

susan

 

[Susan]

try this out to see for yourself..........

open a blank workbook.
type some text or numbers in a column, several rows down, such as

3829378
3829
482394
3827834

now you see above, that the undo arrow is lit up - you can use it.

now open the vb editor, and paste in this code into that worksheet:


Sub lets_see()

Dim ws As Worksheet
Set ws = ActiveSheet

ws.Range("a2") = "Patty Smith"

End Sub


run the code (from the vba editor).
now switch back to the worksheet.
Patty Smith is now in A2, and the undo button is disabled.
you can no longer undo entering those numbers.

type more numbers, and the undo button becomes enabled again.

hope this helps
susan

 

[Shailesh]

Susan /Tom,
Thank you so much for explaining this to me.I have realised since my
macro is in Worksheet_Calculate event its running each time I do any
thing on Worksheet and ultimately hiding the Undo button.
As you can realise that although I obtain the desied metric correct by
writing the macro its creating another trouble where users can not
undo their actions.For exaample if they bring down the Page field in
the row they can not undo it and to get the normal Pivot they have to
close it and then open it again.

Do you have some solution where I can get the correct metric and at
the sam time have the mormal Excel features working.

Regards,
Shailesh

 

[Susan]

shailesh -
nope, sorry. i can explain it, but i have no idea how to get around
it. maybe tom has an answer.

susan

 

[Shailesh]

Thanks Susan.Tom , Could you please take call?

Regards, Shailesh