ExcelBanter - Excel Checkbox Help

On Aug 9, 9:59*am, Dave Peterson wrote:
You could add a checkbox from the Forms toolbar and assign the linked cell to E1
(say).

Then put this formula in E1:
=counta(a1:d1)=4

But don't click on the checkbox yourself. *You'll break the formula in E1 and
then the checkbox won't be "tied to" those other 4 cells.

wrote:

Ok, I am trying to make a checkbox look for data in a range of cells,
lets say A1, B1, C1, D1. *If all the cells contains data then put a
check in the box, else leave it unchecked. *The data that will be in
the cells are dates. *I appreciate any help.

--

Dave Peterson

Is there anyway I can make my code smaller?

I'm also trying to put a for loop in to keep track of how many total
entries as well.
For i = 2 To 28
Set curCell = Worksheets("Sheet1").Cells(i, 1)
If curCell.Value < "" Then total = total + 1
Next i

And I want to display the total in a cell on the sheet. When I try
doing that, excel just hangs....Below is my code taht I would like to
make smaller.

Sub Worksheet_Change(ByVal Target As Range)

' A counter variable to keep track of total completed
Dim counter As Integer
counter = 0

' Series of conditional statements to verify the status is complete
' All desired cells must have data in them

If Range("A2") < "" And _
Range("B2") < "" And _
Range("C2") < "" And _
Range("D2") < "" And _
Range("E2") < "" And _
Range("F2") < "" And _
Range("G2") < "" And _
Range("H2") < "" And _
Range("I2") < "" And _
Range("J2") < "" And _
Range("K2") < "" And _
Range("L2") < "" And _
Range("M2") < "" And _
Range("N2") < "" And _
Range("O2") < "" Then
CheckBox1.Value = True
counter = counter + 1
Else
CheckBox1.Value = False
End If

If Range("A3") < "" And _
Range("B3") < "" And _
Range("C3") < "" And _
Range("D3") < "" And _
Range("E3") < "" And _
Range("F3") < "" And _
Range("G3") < "" And _
Range("H3") < "" And _
Range("I3") < "" And _
Range("J3") < "" And _
Range("K3") < "" And _
Range("L3") < "" And _
Range("M3") < "" And _
Range("N3") < "" And _
Range("O3") < "" Then
CheckBox2.Value = True
counter = counter + 1
Else
CheckBox2.Value = False
End If

If Range("A4") < "" And _
Range("B4") < "" And _
Range("C4") < "" And _
Range("D4") < "" And _
Range("E4") < "" And _
Range("F4") < "" And _
Range("G4") < "" And _
Range("H4") < "" And _
Range("I4") < "" And _
Range("J4") < "" And _
Range("K4") < "" And _
Range("L4") < "" And _
Range("M4") < "" And _
Range("N4") < "" And _
Range("O4") < "" Then
CheckBox3.Value = True
counter = counter + 1
Else
CheckBox3.Value = False
End If

If Range("A5") < "" And _
Range("B5") < "" And _
Range("C5") < "" And _
Range("D5") < "" And _
Range("E5") < "" And _
Range("F5") < "" And _
Range("G5") < "" And _
Range("H5") < "" And _
Range("I5") < "" And _
Range("J5") < "" And _
Range("K5") < "" And _
Range("L5") < "" And _
Range("M5") < "" And _
Range("N5") < "" And _
Range("O5") < "" Then
CheckBox4.Value = True
counter = counter + 1
Else
CheckBox4.Value = False
End If

If Range("A6") < "" And _
Range("B6") < "" And _
Range("C6") < "" And _
Range("D6") < "" And _
Range("E6") < "" And _
Range("F6") < "" And _
Range("G6") < "" And _
Range("H6") < "" And _
Range("I6") < "" And _
Range("J6") < "" And _
Range("K6") < "" And _
Range("L6") < "" And _
Range("M6") < "" And _
Range("N6") < "" And _
Range("O6") < "" Then
CheckBox5.Value = True
counter = counter + 1
Else
CheckBox5.Value = False
End If

If Range("A7") < "" And _
Range("B7") < "" And _
Range("C7") < "" And _
Range("D7") < "" And _
Range("E7") < "" And _
Range("F7") < "" And _
Range("G7") < "" And _
Range("H7") < "" And _
Range("I7") < "" And _
Range("J7") < "" And _
Range("K7") < "" And _
Range("L7") < "" And _
Range("M7") < "" And _
Range("N7") < "" And _
Range("O7") < "" Then
CheckBox6.Value = True
counter = counter + 1
Else
CheckBox6.Value = False
End If

If Range("A8") < "" And _
Range("B8") < "" And _
Range("C8") < "" And _
Range("D8") < "" And _
Range("E8") < "" And _
Range("F8") < "" And _
Range("G8") < "" And _
Range("H8") < "" And _
Range("I8") < "" And _
Range("J8") < "" And _
Range("K8") < "" And _
Range("L8") < "" And _
Range("M8") < "" And _
Range("N8") < "" And _
Range("O8") < "" Then
CheckBox7.Value = True
counter = counter + 1
Else
CheckBox7.Value = False
End If

If Range("A9") < "" And _
Range("B9") < "" And _
Range("C9") < "" And _
Range("D9") < "" And _
Range("E9") < "" And _
Range("F9") < "" And _
Range("G9") < "" And _
Range("H9") < "" And _
Range("I9") < "" And _
Range("J9") < "" And _
Range("K9") < "" And _
Range("L9") < "" And _
Range("M9") < "" And _
Range("N9") < "" And _
Range("O9") < "" Then
CheckBox8.Value = True
counter = counter + 1
Else
CheckBox8.Value = False
End If

If Range("A10") < "" And _
Range("B10") < "" And _
Range("C10") < "" And _
Range("D10") < "" And _
Range("E10") < "" And _
Range("F10") < "" And _
Range("G10") < "" And _
Range("H10") < "" And _
Range("I10") < "" And _
Range("J10") < "" And _
Range("K10") < "" And _
Range("L10") < "" And _
Range("M10") < "" And _
Range("N10") < "" And _
Range("O10") < "" Then
CheckBox9.Value = True
counter = counter + 1
Else
CheckBox9.Value = False
End If

If Range("A11") < "" And _
Range("B11") < "" And _
Range("C11") < "" And _
Range("D11") < "" And _
Range("E11") < "" And _
Range("F11") < "" And _
Range("G11") < "" And _
Range("H11") < "" And _
Range("I11") < "" And _
Range("J11") < "" And _
Range("K11") < "" And _
Range("L11") < "" And _
Range("M11") < "" And _
Range("N11") < "" And _
Range("O11") < "" Then
CheckBox10.Value = True
counter = counter + 1
Else
CheckBox10.Value = False
End If

If Range("A12") < "" And _
Range("B12") < "" And _
Range("C12") < "" And _
Range("D12") < "" And _
Range("E12") < "" And _
Range("F12") < "" And _
Range("G12") < "" And _
Range("H12") < "" And _
Range("I12") < "" And _
Range("J12") < "" And _
Range("K12") < "" And _
Range("L12") < "" And _
Range("M12") < "" And _
Range("N12") < "" And _
Range("O12") < "" Then
CheckBox11.Value = True
counter = counter + 1
Else
CheckBox11.Value = False
End If

If Range("A13") < "" And _
Range("B13") < "" And _
Range("C13") < "" And _
Range("D13") < "" And _
Range("E13") < "" And _
Range("F13") < "" And _
Range("G13") < "" And _
Range("H13") < "" And _
Range("I13") < "" And _
Range("J13") < "" And _
Range("K13") < "" And _
Range("L13") < "" And _
Range("M13") < "" And _
Range("N13") < "" And _
Range("O13") < "" Then
CheckBox12.Value = True
counter = counter + 1
Else
CheckBox12.Value = False
End If

If Range("A14") < "" And _
Range("B14") < "" And _
Range("C14") < "" And _
Range("D14") < "" And _
Range("E14") < "" And _
Range("F14") < "" And _
Range("G14") < "" And _
Range("H14") < "" And _
Range("I14") < "" And _
Range("J14") < "" And _
Range("K14") < "" And _
Range("L14") < "" And _
Range("M14") < "" And _
Range("N14") < "" And _
Range("O14") < "" Then
CheckBox13.Value = True
counter = counter + 1
Else
CheckBox13.Value = False
End If

If Range("A15") < "" And _
Range("B15") < "" And _
Range("C15") < "" And _
Range("D15") < "" And _
Range("E15") < "" And _
Range("F15") < "" And _
Range("G15") < "" And _
Range("H15") < "" And _
Range("I15") < "" And _
Range("J15") < "" And _
Range("K15") < "" And _
Range("L15") < "" And _
Range("M15") < "" And _
Range("N15") < "" And _
Range("O15") < "" Then
CheckBox14.Value = True
counter = counter + 1
Else
CheckBox14.Value = False
End If

If Range("A16") < "" And _
Range("B16") < "" And _
Range("C16") < "" And _
Range("D16") < "" And _
Range("E16") < "" And _
Range("F16") < "" And _
Range("G16") < "" And _
Range("H16") < "" And _
Range("I16") < "" And _
Range("J16") < "" And _
Range("K16") < "" And _
Range("L16") < "" And _
Range("M16") < "" And _
Range("N16") < "" And _
Range("O16") < "" Then
CheckBox15.Value = True
counter = counter + 1
Else
CheckBox15.Value = False
End If

If Range("A17") < "" And _
Range("B17") < "" And _
Range("C17") < "" And _
Range("D17") < "" And _
Range("E17") < "" And _
Range("F17") < "" And _
Range("G17") < "" And _
Range("H17") < "" And _
Range("I17") < "" And _
Range("J17") < "" And _
Range("K17") < "" And _
Range("L17") < "" And _
Range("M17") < "" And _
Range("N17") < "" And _
Range("O17") < "" Then
CheckBox16.Value = True
counter = counter + 1
Else
CheckBox16.Value = False
End If

If Range("A18") < "" And _
Range("B18") < "" And _
Range("C18") < "" And _
Range("D18") < "" And _
Range("E18") < "" And _
Range("F18") < "" And _
Range("G18") < "" And _
Range("H18") < "" And _
Range("I18") < "" And _
Range("J18") < "" And _
Range("K18") < "" And _
Range("L18") < "" And _
Range("M18") < "" And _
Range("N18") < "" And _
Range("O18") < "" Then
CheckBox17.Value = True
counter = counter + 1
Else
CheckBox17.Value = False
End If

If Range("A19") < "" And _
Range("B19") < "" And _
Range("C19") < "" And _
Range("D19") < "" And _
Range("E19") < "" And _
Range("F19") < "" And _
Range("G19") < "" And _
Range("H19") < "" And _
Range("I19") < "" And _
Range("J19") < "" And _
Range("K19") < "" And _
Range("L19") < "" And _
Range("M19") < "" And _
Range("N19") < "" And _
Range("O19") < "" Then
CheckBox18.Value = True
counter = counter + 1
Else
CheckBox18.Value = False
End If

If Range("A20") < "" And _
Range("B20") < "" And _
Range("C20") < "" And _
Range("D20") < "" And _
Range("E20") < "" And _
Range("F20") < "" And _
Range("G20") < "" And _
Range("H20") < "" And _
Range("I20") < "" And _
Range("J20") < "" And _
Range("K20") < "" And _
Range("L20") < "" And _
Range("M20") < "" And _
Range("N20") < "" And _
Range("O20") < "" Then
CheckBox19.Value = True
counter = counter + 1
Else
CheckBox19.Value = False
End If

If Range("A21") < "" And _
Range("B21") < "" And _
Range("C21") < "" And _
Range("D21") < "" And _
Range("E21") < "" And _
Range("F21") < "" And _
Range("G21") < "" And _
Range("H21") < "" And _
Range("I21") < "" And _
Range("J21") < "" And _
Range("K21") < "" And _
Range("L21") < "" And _
Range("M21") < "" And _
Range("N21") < "" And _
Range("O21") < "" Then
CheckBox20.Value = True
counter = counter + 1
Else
CheckBox20.Value = False
End If

If Range("A22") < "" And _
Range("B22") < "" And _
Range("C22") < "" And _
Range("D22") < "" And _
Range("E22") < "" And _
Range("F22") < "" And _
Range("G22") < "" And _
Range("H22") < "" And _
Range("I22") < "" And _
Range("J22") < "" And _
Range("K22") < "" And _
Range("L22") < "" And _
Range("M22") < "" And _
Range("N22") < "" And _
Range("O22") < "" Then
CheckBox21.Value = True
counter = counter + 1
Else
CheckBox21.Value = False
End If

If Range("A23") < "" And _
Range("B23") < "" And _
Range("C23") < "" And _
Range("D23") < "" And _
Range("E23") < "" And _
Range("F23") < "" And _
Range("G23") < "" And _
Range("H23") < "" And _
Range("I23") < "" And _
Range("J23") < "" And _
Range("K23") < "" And _
Range("L23") < "" And _
Range("M23") < "" And _
Range("N23") < "" And _
Range("O23") < "" Then
CheckBox22.Value = True
counter = counter + 1
Else
CheckBox22.Value = False
End If

If Range("A24") < "" And _
Range("B24") < "" And _
Range("C24") < "" And _
Range("D24") < "" And _
Range("E24") < "" And _
Range("F24") < "" And _
Range("G24") < "" And _
Range("H24") < "" And _
Range("I24") < "" And _
Range("J24") < "" And _
Range("K24") < "" And _
Range("L24") < "" And _
Range("M24") < "" And _
Range("N24") < "" And _
Range("O24") < "" Then
CheckBox23.Value = True
counter = counter + 1
Else
CheckBox23.Value = False
End If

If Range("A25") < "" And _
Range("B25") < "" And _
Range("C25") < "" And _
Range("D25") < "" And _
Range("E25") < "" And _
Range("F25") < "" And _
Range("G25") < "" And _
Range("H25") < "" And _
Range("I25") < "" And _
Range("J25") < "" And _
Range("K25") < "" And _
Range("L25") < "" And _
Range("M25") < "" And _
Range("N25") < "" And _
Range("O25") < "" Then
CheckBox24.Value = True
counter = counter + 1
Else
CheckBox24.Value = False
End If

If Range("A26") < "" And _
Range("B26") < "" And _
Range("C26") < "" And _
Range("D26") < "" And _
Range("E26") < "" And _
Range("F26") < "" And _
Range("G26") < "" And _
Range("H26") < "" And _
Range("I26") < "" And _
Range("J26") < "" And _
Range("K26") < "" And _
Range("L26") < "" And _
Range("M26") < "" And _
Range("N26") < "" And _
Range("O26") < "" Then
CheckBox25.Value = True
counter = counter + 1
Else
CheckBox25.Value = False
End If

If Range("A27") < "" And _
Range("B27") < "" And _
Range("C27") < "" And _
Range("D27") < "" And _
Range("E27") < "" And _
Range("F27") < "" And _
Range("G27") < "" And _
Range("H27") < "" And _
Range("I27") < "" And _
Range("J27") < "" And _
Range("K27") < "" And _
Range("L27") < "" And _
Range("M27") < "" And _
Range("N27") < "" And _
Range("O27") < "" Then
CheckBox26.Value = True
counter = counter + 1
Else
CheckBox26.Value = False
End If

If Range("A28") < "" And _
Range("B28") < "" And _
Range("C28") < "" And _
Range("D28") < "" And _
Range("E28") < "" And _
Range("F28") < "" And _
Range("G28") < "" And _
Range("H28") < "" And _
Range("I28") < "" And _
Range("J28") < "" And _
Range("K28") < "" And _
Range("L28") < "" And _
Range("M28") < "" And _
Range("N28") < "" And _
Range("O28") < "" Then
CheckBox27.Value = True
counter = counter + 1
Else
CheckBox27.Value = False
End If

' Place the total in Cell Q3
Cells(3, 17).Value = counter

End Sub