VBA code to pause before printing

[Richard H Knoff]

I have a workbook containing several sheets. In one of them there's
a list of organizational units. I've written a macro that grabs
selected unit identifiers from the sheet "F Units", copies them one
by one to a cell called Filter, and prints sheets with diagrams and
statistics for each of the units.

The procedure sometimes partly fails, as the filter isn't applied
to the statistics sheet. I guess this happens because the sheets
are printed before Excel finishes recalculating. If this is true, I
believe I can prevent the error by adding a "Wait" command, halting
the printing process for something like 3 seconds.

How can I do this? (Part of) the macro appears below.

---

Sub Print_selected_C_and_D()

For Each cell_in_loop In Selection
Sheets("C Diagram").Unprotect Password:="dgt6"

Set TargetSheet = Sheets("C Diagram")
TargetSheet.Range("Filter").Value = ActiveCell.Value
Sheets(Array("C Diagram", "D Statistics")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("F Units").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Next

 

[Don Guillett]

try
EnableEvents Property
See Also Applies To Example Specifics
True if events are enabled for the specified object. Read/write Boolean.

Example
This example disables events before a file is saved so that the BeforeSave
event doesn't occur.

Application.EnableEvents = False
ActiveWorkbook.Save
Application.EnableEvents = True

 

[Earl Kiosterud]

Richard,

The filter uses the active cell, which is not set by the macro, so it must
first be set by the user. Could that be why the filter doesn't always work?

The line that prints won't get executed until the prior lines have been
executed. It's strictly procedural -- none of the operations is
asynchronous. As for calculating, I don't see anything in the macro that
would initiate a calculation. And Excel doesn't calculate in the background
or anything like that anyway. It would finish the calculation before the
next line was performed.

 

[Richard H Knoff]

Earl,

I'm not sure about the "active cell" stuff ...
the macro actually doesn't copy the cells containing the unit
numbers into the Filter cell, as that would result in loss of the
conditional formatting. The "active cell" is definerd, I believe,
by the selection the user makes before the macro is invoked. The
macro appears to "import" the value from the first cell in the
selction to the Filter cell and perform a recalc before it prints
(and then repeats these operations within the selection).
As I stated, the diagram sheet prints correctly - but sometimes the
statistics sheet prints with a Filter set = 1 (which means "all").
The recalc appears to depend on automatic recalculation set as a
general option; I've been wondering if I should add a Calculate
line before the Print commands to force a calculation, but from
what you say it should have no effect because the whole workbook
should supposedly be recalulated before the macro prints the first
sheet. But - sometimes the second sheet just doesn't come out
right.
Any further thoughts on this??

Regards,
Richard

 

[Richard H Knoff]

Don,
I don't quite follow you - are you suggesting I add an EnableEvents
before the Print commands? What kind of parameters? (The weird
thing is that he first sheet prints recalculated values, but the
second sheet doesn't!)

Regards,
Richard

 

[SidBord]

You might get a better response posting this on the
"Programming" forum. There are probalby several ways to do
this. You might read up on the "On Time" event handler.

 

[Richard H Knoff]

Thanks - I'll pursue it a bit further!

Regards,
Richard

 

[Don Guillett]

The same people read both so a waste of time

 

[Earl Kiosterud]

Posting the same question in another group is not a good idea. Since the
thread has been started here it should be kept here. YOu don't want people
working on it who didn't see it in this group -- it wastes their time with
redundant work and answers. The suggestion for using .programming should be
for future questions relating to programming.

 

[Earl Kiosterud]

Richard,

What I meant is that if the active cell (the selected cell, or the white one
if multiple cells have been selected) hasn't been set properly, the filter
won't be using the correct data. Is that a possibility (that the necessary
cells hasn't been selected before running the macro) when the filter has
failed?

 

[Richard H Knoff]

I'll bear it in mind ... but I was actually offered one "unique"
solution from a .programming user, so it was informative this time.

Regards,
Richard

 

[Richard H Knoff]

Earl,
I don't believe this can be the case, as the first sheet (diagram)
prints OK with filtered results, while the second sheet
(statistics) "forgets" the filter and prints unfiltered results.
Same thing happens with the next item in the "units" list. But -
usually it works fine. It's quite confusing.

Regards,
Richard