Forum Discussion
Anthony Smith
Aug 16, 2018Brass Contributor
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 AmairahSilver Contributor
Hi Anthony,
I've tested this code but it works!
Please tell me what the error type you got?
- Anthony SmithBrass ContributorThe issue I'm having is that I'm still able to print.
- Haytham AmairahSilver Contributor
Please make sure that the Workbook_BeforePrint Sub is in ThisWorkbook code module as the below screenshot.