I've been using this code and it's been working fine up until today in 2003, 2007 & 2010.

I need a code to stop the worksheet (only 1 in the book) from printing if certain cells are not completed (b3:b6,e3:e5,h3,l3,n5:n6). It highlighted the cells red and a message box appears also telling the user which cells are not completed.

I added a new code to the workbook today which clears certain cells (E3:E5) if one of them is amended using their dependnat drop down boxes, so I'm wondering if that coding is causing issues with this one as they do refer to some of the same cells? Although saying that, when I take out the new coding, the "ThisWorkbook.print" issue is still occuring.

Any help or advice would be appreciated.

Thanks.


Option Explicit
 

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.Unprotect Password:="password"
    Dim Start As Boolean
    Dim Rng1 As Range
     
    Dim Prompt As String, RngStr As String
    Dim cell As Range
     'set your ranges here
     'Rng1 is on sheet "Expense Claim Form" and cells b3:b6, e3:e5, h3, l3, n5:n6
    Set Rng1 = Sheets("Expense Claim Form").Range("b3:b6,e3:e5,h3,l3,n5:n6")
     'message is returned if there are blank cells
    Prompt = "Please check your data ensuring all required " & _
    "cells are complete." & vbCrLf & "You will not be able " & _
    "to print the workbook until the form has been filled " & _
    "out completely. " & vbCrLf & vbCrLf & _
    "The following cells are incomplete and have been highlighted red:" _
    & vbCrLf & vbCrLf
    Start = True
     'highlights the blank cells
    For Each cell In Rng1
        If cell.Value = vbNullString Then
            cell.Interior.ColorIndex = 3 '** color red
            If Start Then RngStr = RngStr & cell.Parent.Name & vbCrLf
            Start = False
            RngStr = RngStr & cell.Address(False, False) & ", "
        Else
            cell.Interior.ColorIndex = 0 '** no color
        End If
    Next
    If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 2)
    Start = True
        If RngStr <> "" Then
        MsgBox Prompt & RngStr, vbCritical, "Incomplete Data"
        Cancel = True
    Else
         'saves the changes before closing
        ThisWorkbook.Print
        Cancel = False
    End If
     
    Set Rng1 = Nothing
ActiveSheet.Protect Password:="password"
       
End Sub