Userform within a userform ...

[Ray]

Hi -

I've put together a userform which allows my stores to perform
multiple tasks ... thanks to all of you whose posts were pieced
together to create this beauty!

One of the tasks is a print-out of filtered data ... the nature of the
data requires that it be formatted 'live' (eg, auto-fitting columns,
etc). This all works great, but takes about 15-20secs to complete, so
I'd like to have a 2nd userform pop up to indicate that the Print
macro is running ... when that's done, the 2nd userform should close
and a 3rd one will appear to indicate that the Printout is complete.
2nd userform has no 'close' function (X is disabled), 3rd userform has
a 'done' button to close the form.

I've built the userforms and successfully opened the 2nd userform at
the start of the Print macro. However, that userform never closes and
the 3rd one never opens! What am I doing wrong?

to open 2nd userform: Prnting.Show
[...formatting and printing code...]

Unload Me
PrintDone.Show

End Sub

TIA, Ray

 

[Bob Phillips]

Do the PrintDone.Show before the Unload.

 

[Ray]

Hi Bob -

Thanks for a quick response ... tried your idea, but didn't work ...
Here's the whole code segment:

Private Sub CommandButton3_Click()

Prnting.Show

'Adjust column-widths for printing
With Sheet2
With .Range("Z1:AF1")
.EntireColumn.AutoFit
End With

For Each C In .Columns("Z:AF")
C.ColumnWidth = C.ColumnWidth * 1.5
Next

.Columns("AC").ColumnWidth = .Columns("AC").ColumnWidth *
0.7

' Set formatting
With .Range("Z1:AF1")
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlMedium
.Font.Bold = True
End With
End With

Sheet2.Range("Z1:AE1").EntireColumn.HorizontalAlignment =
xlCenter
Sheet2.Range("AF1").HorizontalAlignment = xlCenter

'Set the Print-Area
With Sheet2
.Range("Z1").CurrentRegion.Name = "PrintReport"
With .PageSetup
.PrintArea = "PrintReport"
.Orientation = xlLandscape
.CenterHorizontally = True
.BottomMargin = 0.5
.TopMargin = 1#
.LeftMargin = 0.5
.RightMargin = 0.5
.FitToPagesTall = 1
.FitToPagesWide = 1
.PrintGridlines = True
.LeftHeader = "&""Verdana,Bold""& 20" & "Missed Punch
Report"
.RightHeader = "Printed on: " & Format(Now, "mm/dd/yy")
End With
End With

PrintDone.Show
Unload Me


End Sub

 

[JLGWhiz]

Since the Me refers to the object holding your command button, you probably
need to use:

Unload Printing

to be specific to the Printing UserForm.

 

[Ray]

I threw a 'stop' command on the line following the Prnting.show line,
so that I could step through and see where my code was hanging up ....
imagine my surprise when my code never GOT to the Stop line! So, it
seems that my 2nd userform (Prnting) is causing my problem ...

All I want is a small pop-up to appear while the Formatting and Page-
Setup code is run, and then another pop-up to appear when it's done
(with original pop-up disappearing). Seems so simple and yet ...

Any ideas out there?

Thanks, ray

 

[JLGWhiz]

Maybe you can get an idea at this site:

http://www.cpearson.com/excel/StatusBar.htm

 

[JLGWhiz]

I looked at the macro you call the print macro, but I do not see where you
use the PrintOut command. It looks like you only set up the sheets for
printing without actually doing the print. In that case the reference I
gave to Chip's site is probably useless.

Back to your second UserForm. Since you are showing it modal, you cannot
have any other activity until you hide it or it closes. But if you show it
like:

Printing.Show vbModeless

will allow the rest of the code to execute.

 

[Ray]

JLGWhiz -

You read correctly ... the macro currently doesn't print anything.
I've been 'playing' with different settings and didn't want to keep
printing pages that weren't what I wanted. [I use Print Preview to
check the results.]

Your suggestion worked well, although it took me a little while to
realize that I had to set the primary Userform to vbmodeless as well.
After doing that, all worked as planned ...

Thanks to all for input!

//ray