Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check box counting
Good day,
I have a sheet with multiple check boxes. I would like checkbox1-checkbox5 to sum in cell M10. For example if checkbox2 and checkbox4 are checked then the value in cell M10 = 2 Also I am going to be copying this worksheet over and over so it hase to be worksheet specific. Any help would be appreciated |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check box counting
do you really need code? If the linked cells are F1:F5 then you could put this
=COUNTIF(F1:F5,"TRUE") into a cell which would sum the boxes that are checked for you. "jtfalk" wrote: Good day, I have a sheet with multiple check boxes. I would like checkbox1-checkbox5 to sum in cell M10. For example if checkbox2 and checkbox4 are checked then the value in cell M10 = 2 Also I am going to be copying this worksheet over and over so it hase to be worksheet specific. Any help would be appreciated |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check box counting
--One way is to link the checkbox to each cell and in cell M1 have a formula
to addup these cells.. --If you are looking for a macro...try the below Sub Macro() Dim ws As Worksheet Set ws = Sheets("Sheet1") For intTemp = 1 To 4 varTemp = varTemp + -(ws.OLEObjects("CheckBox" & intTemp).Object.Value) Next Range("M1") = varTemp End Sub If this post helps click Yes --------------- Jacob Skaria "jtfalk" wrote: Good day, I have a sheet with multiple check boxes. I would like checkbox1-checkbox5 to sum in cell M10. For example if checkbox2 and checkbox4 are checked then the value in cell M10 = 2 Also I am going to be copying this worksheet over and over so it hase to be worksheet specific. Any help would be appreciated |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check box counting
--You can link each checkbox to a cell and then have a formula in cell M1 to
add up the linked cells --If you are looking for a macro Sub Macro() Dim ws As Worksheet Set ws = Sheets("Sheet1") For intTemp = 1 To 4 varTemp = varTemp + -(ws.OLEObjects("CheckBox" & intTemp).Object.Value) Next Range("M1") = varTemp End Sub If this post helps click Yes --------------- Jacob Skaria "jtfalk" wrote: Good day, I have a sheet with multiple check boxes. I would like checkbox1-checkbox5 to sum in cell M10. For example if checkbox2 and checkbox4 are checked then the value in cell M10 = 2 Also I am going to be copying this worksheet over and over so it hase to be worksheet specific. Any help would be appreciated |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check box counting
Okay but how do you reference a checkbox?
Not through the name. i tried COUNTIF(CheckBox1,TRUE) "Patrick Molloy" wrote: do you really need code? If the linked cells are F1:F5 then you could put this =COUNTIF(F1:F5,"TRUE") into a cell which would sum the boxes that are checked for you. "jtfalk" wrote: Good day, I have a sheet with multiple check boxes. I would like checkbox1-checkbox5 to sum in cell M10. For example if checkbox2 and checkbox4 are checked then the value in cell M10 = 2 Also I am going to be copying this worksheet over and over so it hase to be worksheet specific. Any help would be appreciated |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check box counting
This macro will visit each worksheet and put the count you want for that
worksheet in M10 for any worksheets that have CheckBoxes on them... Sub CountButtons() Dim CB As OLEObject, WS As Worksheet, Total As Variant, Found As Boolean For Each WS In Worksheets Total = 0 Found = False For Each CB In WS.OLEObjects If CB.progID Like "*CheckBox*" Then Found = True If InStr(1, CB.Name, "checkbox", vbTextCompare) Then If Right(CB.Name, 1) <= 5 Then If CB.Object.Value Then Total = Total + 1 End If End If End If Next If Found Then WS.Range("M10").Value = Total Next End Sub -- Rick (MVP - Excel) "jtfalk" wrote in message ... Good day, I have a sheet with multiple check boxes. I would like checkbox1-checkbox5 to sum in cell M10. For example if checkbox2 and checkbox4 are checked then the value in cell M10 = 2 Also I am going to be copying this worksheet over and over so it hase to be worksheet specific. Any help would be appreciated |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check box counting
Put this code in the sheet code module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) For i = 1 To 5 If Me.OLEObjects("CheckBox" & i).Object = "True" Then x = x + 1 End If Next Range("M10") = x End Sub When you copy the sheet the code goes with it. "jtfalk" wrote in message ... Good day, I have a sheet with multiple check boxes. I would like checkbox1-checkbox5 to sum in cell M10. For example if checkbox2 and checkbox4 are checked then the value in cell M10 = 2 Also I am going to be copying this worksheet over and over so it hase to be worksheet specific. Any help would be appreciated |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check box counting
In design mode right click each checkbox and from the properties window set
the linked cell to a cell F1....Do the same for all cells and then apply this formula in M1 If this post helps click Yes --------------- Jacob Skaria "jtfalk" wrote: Okay but how do you reference a checkbox? Not through the name. i tried COUNTIF(CheckBox1,TRUE) "Patrick Molloy" wrote: do you really need code? If the linked cells are F1:F5 then you could put this =COUNTIF(F1:F5,"TRUE") into a cell which would sum the boxes that are checked for you. "jtfalk" wrote: Good day, I have a sheet with multiple check boxes. I would like checkbox1-checkbox5 to sum in cell M10. For example if checkbox2 and checkbox4 are checked then the value in cell M10 = 2 Also I am going to be copying this worksheet over and over so it hase to be worksheet specific. Any help would be appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting patient check in | Excel Worksheet Functions | |||
Increase size of a Forms Check Box (click on to enter check mark) | Excel Discussion (Misc queries) | |||
Check if Conditional Format is True or False / Check cell Color | Excel Worksheet Functions | |||
counting check boxes | Excel Worksheet Functions | |||
counting check boxes | Excel Worksheet Functions |