[Solved] Controls (Control Toolbox): VBA: Evaluating Checkbo

[elopez]

Hello,
I have added about 10 or 15 check boxes on a sheet and my basic goal is to have the procedure evaluate the sheet to see if the all checkboxes are checked if they are I want it to exit the sub but if they are not I want it to bring up one message box that notifies me that I need to review the list to see what items off the list I need to take care of. I started by writing the code below but it seems to be getting caught up on dim the Checkbox.

Private Sub Warning()
Dim myCandidate
Set myCandidate = Range("Rcandnam")
Dim Chbx As xlCheckbox
Dim Ws As Worksheet
Set Ws = Worksheets("Template3") 'Documents
For Each Chbx In Ws
If Chbx = False Then
MsgBox myCandidate & " has only a few weeks before departue and" & vbCrLf _
& " they still have portions of the Document Packet to fill out." & vbCrLf _
& "Check the Documents tab and encourage your candidate to turn" & vbCrLf _
& " the outstanding documents in as soon as possible.", 48, "Documents Still Outstanding"
End If
Next Chbx
End Sub

[Andy Pope]

Hi elopez,

You should be able to modify the attached example to suit your needs.

Cheers
Andy

[elopez]

Andy,
Thanks. This really helps. I got it all in and it works but I would like for the CheckBox caption to show instead of its default name. I tried adjusting the strMsg to strMsg= strMsg & Chbx.Caption & vbCrLf rather than just
strMsg = strMsg & Chbx.Name & vbCrLf but that doesn't seem to work. Do you have any suggestions?
Thanks

[Andy Pope]

Hi elopez,

The Caption property requires the Object object, so try;

strMsg = strMsg & Chbx.Object.Caption & vbCrLf

Cheers
Andy

[elopez]

Andy,
It works great, thanks a bunch.
Thanks again,
elopez