Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2007
    Location
    Cincinnati, Ohio, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Print (Excel 2002, SP3)

    I'm building a small app for work where I'll be presenting employee compensation data to managers. I've built a list box where they can select one/several/all employees to print individual sheets for. I'm focused on 4 columns where I'll display annual salary and 3 columns for various bonus programs. Since not all employees are eligible for every plan, I want to hide(change font color to white) the column headers where their column amount is blank. This works somewhat, but when I pick multiple people, all printed documents retain the column settings of the first record. I'm also getting "x" sets of print outs if I select "x" people. Please help!! Thanks!!
    Code in question below.

    If rowcnt = 1 Then
    If ActiveWindow.ActiveCell.Offset(0, 11).Value = "" Then Range("L6").Font.ColorIndex = 2
    If ActiveWindow.ActiveCell.Offset(0, 12).Value = "" Then Range("M6").Font.ColorIndex = 2
    If ActiveWindow.ActiveCell.Offset(0, 13).Value = "" Then Range("N6").Font.ColorIndex = 2

    'for debugging
    If de###### = True Then
    colorL = Range("L6").Font.ColorIndex
    colorM = Range("M6").Font.ColorIndex
    colorN = Range("N6").Font.ColorIndex
    MsgBox (colorL & colorM & colorN)
    Else
    'for production
    ' Selection.PrintOut Copies:=1, Collate:=True
    'ActiveWindow.ActiveCell.PrintPreview
    Selection.Areas.PrintPreview

    End If

    Else
    If rowcnt > 1 Then
    If ActiveWindow.ActiveCell.Offset(0, 11).Value = "" Then Range("L6").Font.ColorIndex = 2
    If ActiveWindow.ActiveCell.Offset(0, 12).Value = "" Then Range("M6").Font.ColorIndex = 2
    If ActiveWindow.ActiveCell.Offset(0, 13).Value = "" Then Range("N6").Font.ColorIndex = 2

    Else
    for production
    Selection.PrintOut Copies:=1, Collate:=True
    Range("L6:N6").Font.ColorIndex = 3
    End If

    End If

    End If

    'reset the colors
    range("L6:N6").Font.ColorIndex = 3

    End If
    Next i

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Print (Excel 2002, SP3)

    You have posted only part of your code, so it is impossible to know what it does. Please provide sufficient information for us to help you.

  3. #3
    New Lounger
    Join Date
    Feb 2007
    Location
    Cincinnati, Ohio, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Print (Excel 2002, SP3)

    Sorry, I thought you might focus on the loop in question. I attached entire file for your review. Thanks again.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Print (Excel 2002, SP3)

    You hadn't posted the entire loop - that made it difficult.

    If I understand you correctly, you want one printout even if more than one employee has been selected. That is a problem if you select rows individually - each row will print on a separate page.
    Also, you should take the Printout instruction out of the loop, otherwise it'll be executed once for each selected employee.

    I have a hunch I may be misinterpreting what you want, so please clarify.

  5. #5
    New Lounger
    Join Date
    Feb 2007
    Location
    Cincinnati, Ohio, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Print (Excel 2002, SP3)

    My goal is to create an individual recap sheet for each employee; one page per selected person. Since not everyone may want or need a copy, the manager can pick specific people if they choose. Hoping this clears up exactly what I'm trying to accomplish. I should add that the department data is loaded from another workbook. When loaded, it inserts page breaks after each row.

    Really appreciate your patience.

    Thanks so much,

    Dax

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Print (Excel 2002, SP3)

    Try this version. It doesn't select cells, but prints the rows corresponding to the selected employees in a single loop.

    Private Sub OKButton_Click()
    ' This boolean is for debugging purposes.
    ' Set to TRUE for messageboxes for each color status.
    ' Set to FALSE to print.
    Dim De###### As Boolean
    Dim r As Long
    Dim colorL As Integer
    Dim colorM As Integer
    Dim colorN As Integer

    De###### = False

    ' This is where it finds out the row range from the listbox selections
    For r = 0 To ListBox1.ListCount - 1
    ' Code examines selected property of the ListBox to see if a box is selected.
    If ListBox1.Selected® Then
    ' Reset colors
    Range("L6:N6").Font.ColorIndex = 3
    ' Hide column headers if no data
    If Range("L" & (r + 8)) = "" Then Range("L6").Font.ColorIndex = 2
    If Range("M" & (r + 8)) = "" Then Range("M6").Font.ColorIndex = 2
    If Range("N" & (r + 8)) = "" Then Range("N6").Font.ColorIndex = 2
    If De###### = True Then
    ' For debugging
    colorL = Range("L6").Font.ColorIndex
    colorM = Range("M6").Font.ColorIndex
    colorN = Range("N6").Font.ColorIndex
    MsgBox (colorL & " " & colorM & " " & colorN)
    Else
    ' For production
    Rows(r + 8).PrintOut Copies:=1, Collate:=True
    End If
    End If
    Next r
    ' Reset the colors
    Range("L6:N6").Font.ColorIndex = 3

    ' Unload GetData form
    Unload Me
    End Sub

  7. #7
    New Lounger
    Join Date
    Feb 2007
    Location
    Cincinnati, Ohio, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Print (Excel 2002, SP3)

    Hans, this worked perfectly! You're the best! <img src=/S/basket.gif border=0 alt=basket width=58 height=52>
    A great way to resolve my first post.

    Dax2

Posting Permissions

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