Excel Checkbox Help

M

mherald81

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.
 
J

Joel

You need a worksheet change function

Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
If Range("A1") <> "" And _
Range("A2") <> "" And _
Range("A3") <> "" And _
Range("A4") <> "" Then

CheckBox1.Value = True
Else
CheckBox1.Value = False
End If
End If
 
D

Dave Peterson

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.
 
M

mherald81

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.
Click to expand...

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
 
D

Dave Peterson

I think that this does what you want:

Option Explicit
Sub Worksheet_Change(ByVal Target As Range)

Dim iCtr As Long
Dim myRng As Range
Dim Counter As Long
Dim myCell As Range
Dim RngToCheck As Range
Dim myIntersect As Range
Dim myColA As Range
Dim NumberOfRows As Long

NumberOfRows = 27

Set RngToCheck = Me.Range("a2").Resize(NumberOfRows, 15)
Set myIntersect = Intersect(Target, RngToCheck)

If myIntersect Is Nothing Then
Exit Sub
End If

'just column A of the row that changed
Set myColA = Intersect(Me.Columns(1), myIntersect.EntireRow)

Counter = 0
For Each myCell In myColA.Cells
'A:O is 15 columns
Set myRng = myCell.Resize(1, 15)
Me.OLEObjects("Checkbox" & myCell.Row - 1).Object.Value _
= CBool(myRng.Cells.Count = Application.CountA(myRng))
Next myCell

Counter = 0
For iCtr = 1 To NumberOfRows
Counter = Counter + Abs(Me.OLEObjects("Checkbox" & iCtr).Object.Value)
Next iCtr

'tell excel not to look for more changes
Application.EnableEvents = False
Me.Range("Q3").Value = Counter
Application.EnableEvents = True

End Sub

It only looks at the cells that you changed--actually, just the rows that have
changed cells in columns A:O.

And it counts the number of checkboxes that are checked.

You could have counted the number of rows that are filled in A:blush:, too.

Dim iRow as long
dim Counter as long
dim myRng as range

counter = 0
for irow = 2 to 27
set myrng = .cells(irow,"A").resize(1,15)
if application.counta(myrng) = myrng.cells.count then
counter = counter + 1
end if
next irow
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cells with varying number of lines 1
Tranfer data from Excel to word (Docx) via VBA in Excel 5
simple code but it doesn't look like usual vba code 2
Time Calculations Help 4
Check a value every time excel is loaded 1
Flipping Data 4
Concatenate A1, B1, and C1 into D1 2
Data Validation and drop-down list using IF, AND and what else? 8

Top