Calculating / Sorting / Printing

[Paul W Smith]

I have a large table of data which takes a while to update itself following
a recalculation.

My issues is that I what to print this table out after the recalculation and
after sorting the table using criteria which change during the
recalculation. I can write the code to do everything except stop the
printing occuring before the recalculation/sorting has taken place. What
comes out of my printer is the unsorted table.

How to I hold the printing process until the recalculation/sorting has taken
place?

Paul Smith

 

[Bernie Deitrick]

Paul,

Use two macros instead of one, and fire the second one (the print part) using the ontime method,
with a suitable time difference so that the sorting is finished.

Sub ExistingMacro()
'Code here for sorting, etc.
'Then use
Application.OnTime Now + TimeValue("00:00:20"), "PrintMacro" '20 second delay
End Sub

Sub PrintMacro()
Worksheets("SheetName").PrintOut
End Sub

HTH,
Bernie
MS Excel MVP

 

[Paul W Smith]

Thanks for the suggestion.

I could do it all as one macro using something like

t = Now + 20 sec
do
loop until t=

This all seems like a workaround. I am thinkin g there must be some way of
allow a recalculation to take place before progress through code.

Is this possible anyone?

 

[Bob Alhat]

Paul, I think Bernie's suggestion may save a lot of pain. The alternative
might lie along the following lines:

The worksheet_calculate event fires after a sheet has been recalculated, so:
Set a Public boolean in this event to True
Get the Before_Print event to check the value of the Boolean, and set
Cancel=True if
the Boolean is false.
Set the Boolean to False in all other appropriate routines.

The tricky bit will be setting the boolean to True at the right moment. Is
there anything in your table that you could test against in the
Worksheet_Calculate event, in order to set the value
of the boolean?
The Before_Print would need to check the value of the Boolean and the Range
being printed - there may be other ranges you wish to print outside of this
control.

Just some thoughts, I hope they help. But Bernie's idea looks like a
good'n'.

Bob