Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform within a userform ...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform within a userform ...
Do the PrintDone.Show before the Unload.
-- __________________________________ HTH Bob "Ray" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform within a userform ...
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.HorizontalAlig nment = 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform within a userform ...
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" wrote in message ... 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.HorizontalAlig nment = 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform within a userform ...
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform within a userform ...
Maybe you can get an idea at this site:
http://www.cpearson.com/excel/StatusBar.htm "Ray" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform within a userform ...
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform within a userform ...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill userform textbox from userform listbox clik event | Excel Programming | |||
Is there an easy Copy/Paste of a Userform ? (Entire Userform Including tx & cbx's) | Excel Programming | |||
Userform to enter values and shown in same userform in list | Excel Programming | |||
Looping procedure calls userform; how to exit loop (via userform button)? | Excel Programming | |||
Activating userform and filling it with data form row where userform is activate | Excel Programming |