| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Checkbox Help
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Checkbox Help
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 " 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Checkbox Help
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel CheckBox | New Users to Excel | |||
| How to have Checkbox A uncheck with checked Checkbox B | Excel Discussion (Misc queries) | |||
| link a checkbox in a sheet to a checkbox on a userform? | Excel Programming | |||
| How do I link one checkbox to update another checkbox? | Excel Programming | |||
| checkbox on form reset from checkbox on sheet | Excel Programming | |||
Linear Mode
