Run/Test Code Without Printing

[golf4]

Hi, everyone -

Still learning VBA :doh: :doh: :doh: , so please bear with me.

Within my projects, I have a great number of macros that users use to print various worksheets. Several of these are extremely long, and print off up to 10 worksheets with one code.

The question I have is whether there is a way to run through my code, in VB Editor, to test it out without printing all of the worksheets? I know the "skipping" over printing the worksheets kind of defeats the purpose of the code, but I'm wasting a lot of paper in just testing out the codes. The problems I'm having is with ther plethora of EndIfs, EndWiths,.......

Thanks for the continued help.

Frank

[Jacob Hilderbrand]

You can just comment out any print code. So somewhere you have a line line something.PrintOut, just put a ' in front of it and that line will not be executed.

[golf4]

Hi, Jake -
Thanks for the quick response, bud. I'll give your suggestion a shot.

The biggest problem I'm having is using a code strip having to do with the a cell entry and what the code does if the entry is the 1st of the month or otherwise.

You think, if I paste the code here and give you some explanation, you can help me out? I KNOW it something simple....... isn't it always??????

Thanks again.

Frank

[mark007]

Sure that's what we're here for!

:)

BTW - when commenting out it might be useful to start the comment:

'TEST:

or something else so that you can search for all occurrences when you need to set them back.

:)

[Aaron Blood]

In most of my applications, I don't ever use the print command. Rather I just take the user to the print preview screen. Gives em an opportunity to opt out of the printing or continue. Granted, if you're automating a print loop it's a different story.


ActiveWindow.SelectedSheets.PrintPreview

Just thought I'd also mention that it's sometimes helpful in the debug process to know where the print action is being processed, or review what data is being printed without killing trees. You can comment out your standard print line and replace it with the print preview... or sometimes just a little message box to say "Printing" is a good enuf indication.

[jolivanes]

Frank.
I use the free version of a .PDF writer available on the internet and set this as the default printer as well as the printpreview method.
Regards.
John

[Cyberdude]

John, sounds like the .PDF technique is great. Now just where did you get the "free" .PDF writer? I sure would like to get it.
Cyberdude

[golf4]

Hi, guys -

Thanks for the great suggestions. I'll try them out when I get home tonight.

Cyberdude - try this: http://www.adobe.com/support/downloads/product.jsp?product=9&platform=Windows


Thanks again for the help.

Frank

[jolivanes]

Cyberdude.

I got it from the following site.

http://www.acrosoftware.com/Products/CutePDF/writer.asp

Another one is called Omniformat and you can also download a PDF writer from the Adobe site. I run the Adobe writer on Win98SE and the Cute PDF Writer on WinXP.
Good Luck
John

[Zack Barresse]

Heya Frank! Great to see you!! Hope everything is going well with you. (I'm still showing off those medals!! :yes )

Also, sometimes in my routines I will give the user the option of printing when completing a procedure. When doing this I will put it in an If/Then MsgBox command. Something like this ...


'.. code

If MsgBox("Would you like to print the report?") = vbYes Then ActiveSheet.Printout

'.. other code


Sometimes this is good, but usually only for one sheet. If you have multiple sheets (like Aaron stated), you may want to think about something else. This code I picked up from J-Walk's site with a contribution (which I added) from Aaron. It's quite a wonderful routine and I use it frequently these days. ;)



Sub Print_SelectedSheets()
Dim i As Long, TopPos As Long, SheetCount As Long
Dim PrintDlg As DialogSheet, CurrentSheet As Worksheet, cb As CheckBox
Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical: Exit Sub
End If
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
SheetCount = 0
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i
PrintDlg.Buttons.Left = 240
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to print"
End With
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
Application.ScreenUpdating = False
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Select Replace:=False
End If
Next cb
Application.ScreenUpdating = True
ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True
ActiveSheet.Select
End If
Else
MsgBox "All worksheets are empty."
End If
Application.DisplayAlerts = False
PrintDlg.Delete
Application.DisplayAlerts = True
CurrentSheet.Activate
End Sub

HTH. Take care Frank!!

[jolivanes]

Hi firefytr.

Would be nice if a userform opened up on the active sheet with the printer dialog box where you could choose between printers, incl the PDF writer, and a possibility to choose the print preview. If I was better at it I would try this. Maybe my next three week spare time project.
Regards.
John

[Zack Barresse]

You can do this with API. Here are a couple of examples you could put together:

Sheet Print (http://www.vbaexpress.com/kb/getarticle.php?kb_id=129) Routine (Much like the dialog box code I posted.)

List Printers (http://www.vbaexpress.com/kb/getarticle.php?kb_id=332) - this is for Word, but you could adapt, as it's using Windows API.

I don't have time to put an example together, but you can practice with these two. :)

[jolivanes]

Hi Zack

Thanks a lot. This will keep me busy for a while.
Thanks and regards
John

[golf4]

Hey, guys -

Thanks so much for the ideas. Thinking of going with the pdf suggestion. Have to work on it this weekend.
*************************************************

Hey Zack -
Thanks for the ideas, bud!!!!!!

I just wished you hadn't mentioned the medals. Anyone looking at em needs the SUPER SECRET SECURITY CLEARANCE!!!!! Now I gotta make a call to some nasty people...... Don't be surprised if you see a whole bunch of dark-colored sedans cruising through Boardman askin about ya.......... :eek::eek::eek:

:rofl::rofl::rofl:

Then, again, maybe I won't make the call. I'd hate to deprive the wonderful citizens of Oregon of one of their lifesavers!!!!!!!!!! :p

Take care.

Frank