Results 1 to 5 of 5

Problem with Macro printing code

Threaded View

  1. #1
    Registered User
    Join Date
    05-24-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    25

    Problem with Macro printing code

    I have a vba code for a form needing to print out only some specific information. This code is a "before print" code and will change the cell content's font to the background color so the information will not appear. Everything seems to work perfectly except, for some reason, the print screen does not appear when I click print and the form prints directly to my default printer. Since many people will be using/printing this form from different computers, I need this print screen to appear so they may choose which printer to print to. Strangely enough, I am able to get to this print screen when I go to print preview first, but not when I select print directly. I have copied my current code below for editing. Since I was able to find this code online, and I am very bad at vba programming, I was hoping someone could make edits in the code for me. Thanks!

    Option Explicit
     
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
        Dim ary_lFontColorIndex_Old(1 To 18, 1 To 2) As Long
        Dim x           As Long
        Dim y           As Long
        Dim bolSaved    As Boolean
         
        If ActiveSheet.CodeName = "Sheet2" Then
             '// Retain old cell colors one-at-a-time, in case any odd ball variations. This //
             '// would be slow if the range was very big...                                  //
            bolSaved = ThisWorkbook.Saved
            For x = 1 To 17
                For y = 1 To 1
                    ary_lFontColorIndex_Old(x, y) = Sheet2.Range("O32:O48").Cells(x, y).Font.ColorIndex
                Next
            Next
             
             '// When referring to the sheet by its CodeName, there are no quote marks.      //
             '// Just start out with the object's name, like Sheet2, ThisWorkbook, UserForm1.//
            Sheet2.Range("O32:O48").Font.ColorIndex = 2
            For x = 1 To 17
                For y = 1 To 1
                    ary_lFontColorIndex_Old(x, y) = Sheet2.Range("P32:P48").Cells(x, y).Font.ColorIndex
                Next
            Next
            Sheet2.Range("P32:P48").Font.ColorIndex = 15
            For x = 1 To 17
                For y = 1 To 1
                    ary_lFontColorIndex_Old(x, y) = Sheet2.Range("S32:S48").Cells(x, y).Font.ColorIndex
                Next
            Next
            Sheet2.Range("S32:S48").Font.ColorIndex = 2
            For x = 1 To 17
                For y = 1 To 1
                    ary_lFontColorIndex_Old(x, y) = Sheet2.Range("T32:T48").Cells(x, y).Font.ColorIndex
                Next
            Next
            Sheet2.Range("T32:T48").Font.ColorIndex = 15
            For x = 1 To 18
                For y = 1 To 1
                    ary_lFontColorIndex_Old(x, y) = Sheet2.Range("U32:U49").Cells(x, y).Font.ColorIndex
                Next
            Next
            Sheet2.Range("U32:U49").Font.ColorIndex = 15
             
             '// Cancel the print and kill events so that we don't recurse.                  //
            Cancel = True
            Application.EnableEvents = False
             
            Sheet2.PrintOut
             
             '// Reset the font color for each cell, and flag .Saved to what is was before   //
             '// we changed the colors.                                                      //
            For x = 1 To 17
                For y = 1 To 1
                    Sheet2.Range("O32:O48").Cells(x, y).Font.ColorIndex = ary_lFontColorIndex_Old(x, y)
                Next
            Next
            For x = 1 To 17
                For y = 1 To 1
                    Sheet2.Range("P32:P48").Cells(x, y).Font.ColorIndex = ary_lFontColorIndex_Old(x, y)
                Next
            Next
            For x = 1 To 17
                For y = 1 To 1
                    Sheet2.Range("S32:S48").Cells(x, y).Font.ColorIndex = ary_lFontColorIndex_Old(x, y)
                Next
            Next
            For x = 1 To 17
                For y = 1 To 1
                    Sheet2.Range("T32:T48").Cells(x, y).Font.ColorIndex = ary_lFontColorIndex_Old(x, y)
                Next
            Next
            For x = 1 To 18
                For y = 1 To 1
                    Sheet2.Range("U32:U49").Cells(x, y).Font.ColorIndex = ary_lFontColorIndex_Old(x, y)
                Next
            Next
             
            ThisWorkbook.Saved = bolSaved
            Application.EnableEvents = True
        End If
    End Sub
    Last edited by arlu1201; 06-25-2012 at 08:12 AM. Reason: Use code tags in future.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1