Forum Discussion

Anthony Smith's avatar
Anthony Smith
Brass Contributor
Aug 16, 2018

Cell is Not Recognized as Filled If Filled Using VBA

I have a cell that is highlighted yellow if a checkbox is checked.

I want to restrict printing if this cell is still highlighted, but my macro is not working.

My code to highlight (fill) the cell based on whether a checkbox is checked is as follows:

Sub CheckBox76_Click()
If ActiveSheet.Shapes("Check Box 76").ControlFormat.Value = 1 Then    
Range
("G54").Interior.Color = vbYellow MsgBox ("Please enter the CFDA number in the highlighted field (G54).") Else
Range
("G54").Interior.Color = RGB(221, 235, 247) End If End Sub

Code to check if cell is filled in and restrict printing if it is.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rRng As Range
Dim rCell As Range
Set rRng = ActiveWorkbook.Sheets("Assign_FI$Cal_Project_Code").UsedRange
If ActiveSheet.Name = "Assign_FI$Cal_Project_Code" Then For Each rCell In rRng.Cells If rCell.Interior.Color = vbYellow Then
Cancel
= True
MsgBox
"Please fill in highlighted cells before printing." End If Next rCell
End If
End Sub
  • Please make sure that the Workbook_BeforePrint Sub is in ThisWorkbook code module as the below screenshot.

     

     

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    Hi Anthony,

     

    I've tested this code but it works!

    Please tell me what the error type you got?

      • Haytham Amairah's avatar
        Haytham Amairah
        Silver Contributor

        Please make sure that the Workbook_BeforePrint Sub is in ThisWorkbook code module as the below screenshot.

         

         

Resources