Print a label w/ info, given a PASS status per prior columns

[HobbesCaveer]

Greetings. I've Excel 2016 spreadsheet that I created that captures the following information per these columns:A = Operator number

B = Serial number of part being tested
C = Date / Time (this is auto-filled once the Serial No. is scanned into prior field)
D-F = test set-up confirmations
G-AD = test result cells (24 different test parameters; each one manually entered by the Operator)
AE = this column takes all data results if PASS or FAIL per columns G-AD; if all cells are good, then "PASS" is automatically filled here... if just one cell is out of spec, then "FAIL" is auto-generated here​

What I'd like is the ability then for the Operator to click their mouse on the cell in column AE, and if there is a result of "PASS", then a small label be printed that contains the Serial Number and "PASS" on it.

Is such or anything similar to this label printing possible? If so, I'd appreciate any suggestions / directions on how to make this work.

PS: if its not possible for Excel (or Word mailmerge?) to grab the Serial Number from column B, I can live with just the ability of the label to have "PASS" on it when the Operator clicks that cell in column AE.

thanks!

 

[pbornemeier]

Re: How do I print a label w/ info, given a PASS status per prior columns

The first code will automatically trigger if PASS shows up in column AE.
The second required that the user double-click on a cell in column AE that contains "PASS"

Put either or both of these code blocks on the codepage of the worksheet that contains the data.
When triggered as described above this code will copy the value in column B of that row and the word PASS to cell A1 of Sheet2 and print Sheet2!A1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("AE:AE"), Target) Is Nothing Then
        If Intersect(Range("AE:AE"), Target).Cells.Count > 1 Then
            MsgBox "More than one cell changed in column AE:" & vbLf & _
                "    " & Intersect(Range("AE:AE"), Target).Address
        Else
            If Target.Value = "PASS" Then
                Application.DisplayAlerts = False 'To prevent "Print Single Cell?" question
                Worksheets("Sheet2").PageSetup.PrintArea = "$A$1"
                Application.DisplayAlerts = True
                Worksheets("Sheet2").Range("A1").Value = Cells(Target.Row, "B").Value & " PASS"
                Worksheets("Sheet2").PrintOut Copies:=1, Collate:=True, _
                    IgnorePrintAreas:=False
            Else
        End If
    End If

End Sub

When a single cell in column AE contains "PASS" and is double-clicked this code will copy the value in column B of that row and the word PASS to cell A1 of Sheet2 and print that cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Range("AE:AE"), Target) Is Nothing Then
        If Intersect(Range("AE:AE"), Target).Cells.Count > 1 Then
            MsgBox "More than one cell changed in column AE:" & vbLf & _
                "    " & Intersect(Range("AE:AE"), Target).Address
        Else
            If Target.Value = "PASS" Then
                Application.DisplayAlerts = False 'To prevent "Print Single Cell?" question
                Worksheets("Sheet2").PageSetup.PrintArea = "$A$1"
                Application.DisplayAlerts = True
                Worksheets("Sheet2").Range("A1").Value = Cells(Target.Row, "B").Value & " PASS"
                Worksheets("Sheet2").PrintOut Copies:=1, Collate:=True, _
                    IgnorePrintAreas:=False
            Else
        End If
    End If

End Sub

If your printer is not configured to print single labels, this could be quite wasteful of label paper.