Cell is Not Recognized as Filled If Filled Using VBA

[antman2988]

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:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub CheckBox76_Click()
If ActiveSheet.Shapes("Check Box 76").ControlFormat.Value =1Then
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)
EndIf
EndSub

</code>Code to check if cell is filled in and restrict printing if it is.

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">PrivateSub Workbook_BeforePrint(Cancel AsBoolean)
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
ForEach rCell In rRng.Cells
If rCell.Interior.Color = vbYellow Then
Cancel =True
MsgBox "Please fill in highlighted cells before printing."
EndIf
Next rCell
EndIf
EndSub</code>

 

[My Aswer Is This]

What type checkbox is this?
Activex or Form

 

[My Aswer Is This]

I would think it would be like this:
If CheckBox76.Value = True Then

Not sure why your using this:
If ActiveSheet.Shapes("Check Box 76").ControlFormat.Value = 1 Then

What is Control Format

 

[Fluff]

The beforePrint will only stop the printing if the "Assign_FI$Cal_Project_Code" sheet is active.
Is that what you wanted?

 

[mikerickson]

I would think it would be like this:
If CheckBox76.Value = True Then

Not sure why your using this:
If ActiveSheet.Shapes("Check Box 76").ControlFormat.Value = 1 Then

What is Control Format

The ControlFormat is a property of a Shape and is used to get the values from forms menu controls.

If ActiveSheet.Shapes("Check Box 76").ControlFormat.Value = [COLOR="#FF0000"]xlOn[/COLOR] Then

tests if a forms menu checkbox is checked. (Note: xlOff = -4146 not 0.)

 

[My Aswer Is This]

Well it's not my question but I'm still confused.

Are we saying the user has created a Shape and Named The shape Checkbox76?

And I don't understand this:

The ControlFormat is a property of a Shape and is used to get the values from forms menu controls.

I do not know what this means:

get the values from forms menu controls.

What is a form in this situation?

Is this a Userform
Or a Form control



Why would someone create a shape and name it Checkbox76

I always think of a checkbox as a activex or Userform checkbox.​

_{^{<strike>
</strike>}}​

 

[mikerickson]

The user took a checkbox from the Forms Menu (not an ActiveX) and put it on a worksheet.

To use controls from the Forms Menu, one uses the .ControlFormat property. (Controls from the Forms menu are shapes (with some other stuff))
An ActiveX control would have its own properties, but when working with controls from the Forms menu, one has to use the .ControlFormat for that shape.

One clue is the spaces in Check Box 76. Excel's automatic naming routine puts a space in the names of controls (or rectangles or ovals or ...) but does not insert spaces in the names of ActiveX controls.

I'm still waiting for the OP to explain the situation more fully. The title talks about cells, are these cells linked to the controls...