Count Check box in Excel 2019

Page 1 of 2 12 LastLast

  1. Posts : 35
    Windows 10
       #1

    Count Check box in Excel 2019


    Hi guys is it possible to total a column of check boxes in excel 2019

    I can create the check boxes but i am not able to counts the one that are selected as a total at the bottom of the column please

    Regards
      My Computer


  2. Posts : 13,904
    Win10 Version 22H2 Pro and Home, Win11 Pro and Home
       #2

    It may not be possible as Excel works with numbers, don't know how it could differentiate a box between filled or not. Maybe a database instead?
      My Computers


  3. Posts : 2,177
    Windows 10 Pro 64-bit v22H2
       #3

    You easily do it with VBA code. I have tested this with Excel 2019 myself but will leave it up to you to develop the code for your own project.

    Insert an ActiveX Checkbox
    The Value property can be checked to see if has been checked

    For example, for a CheckBox named CheckBox1:

    CheckBox1.Value = True ' the checkbox is checked

    CheckBox1.Value = False ' the checkbox is not checked

    For your reference:

    How to Start Using VBA in Excel 2019
    How to Start Using VBA in Excel 2019 | UniversalClass

    Check Box
    Excel VBA Check Box - Easy Excel Macros

    VBA CheckBox
    ActiveX CheckBox Control VBA - Explained with Examples

    The Complete Guide to Excel VBA ActiveX Checkboxes
    The Complete Guide to Excel VBA ActiveX Checkboxes - wellsr.com
      My Computers


  4. Posts : 11,247
    Windows / Linux : Arch Linux
       #4

    Hi there @Blake0202
    You will probably have to enable Activex on EXCEL to make that work -- Activex is a bit of a security nightmare but if you are careful it should do what you want.

    https://support.microsoft.com/en-us/...e-b0b8898743ed

    Cheers
    jimbo
      My Computer


  5. Posts : 35
    Windows 10
    Thread Starter
       #5

    Hi guys

    Thank you for the advice but i think VBA is well above my capability's. so i think i will have to leave it there, but thanks for taking the time to advise me

    Regards
      My Computer


  6. Posts : 16,786
    Windows 10 Home x64 Version 22H2 Build 19045.4170
       #6

    Blake,

    Whilst adding a checkbox is actually using coding, you can do what you want without consciously delving into VBA or even seeing it.

    Add a check box, option, or Toggle button (ActiveX controls) - MSSupport

    Add the checkbox as you did before [using the Developer tab, Insert, Checkbox]
    then right-click on the checkbox & select Format control,
    then select the Control tab,
    then enter [or browse to] a cell reference in its Cell link field.
    [Don't worry about Excel automatically changing cell B2 into cell $B$2. It means the same as far as you are concerned at the moment.]
    Count Check box in Excel 2019-create-checkbox.jpg Excel 2007 version diagram

    As you set & unset the checkbox, the value of the cell changes between True & False

    You can count the number of True entries using a CountIf formula. For example,
    =COUNTIF(B1:B14,TRUE)
    You can get help for formulas in the Formulas tab as well as online - see Excel functions (alphabetical) - OfficeSupport

    I use Excel 2007.
    - Everything I have written & the links I have given you apply to Excel 2019 as well. The diagram might well look a bit different.
    - My Excel Help is built-in but I gave you online links because I don't know if you have built-in Excel Help or not. The info should be the same either way.

    Denis
    Last edited by Try3; 30 May 2021 at 02:52.
      My Computer


  7. Posts : 35
    Windows 10
    Thread Starter
       #7

    Hi Try3

    Thank you for the advice that works a treat just what i wanted

    But now i have another problem i did a test run following your advice and it worked fine, but i need approx. 100 check boxes in a column so i made one check box and then clicked on the cross and dragged the box down about 50 lines and all was ok
    I then did the same for the rest of the rows but whilst doing this my spreadsheet stopped responding and and i had to close it but now when i have reloaded it and start again i am not able to click on the cross and drag down to make more check boxes and any check boxes i do make will not delete even if i delete the whole line.
    The only way i can delete them is to Press Ctrl+G > Special > Objects > OK. then press the Delete key

    Some thing has gone and i dont know what can you help please
      My Computer


  8. Posts : 16,786
    Windows 10 Home x64 Version 22H2 Build 19045.4170
       #8

    When you copy checkboxes, you have to set up their Cell link fields individually afterwards to point to different cells. It's not the same as dragging cells where formulas can get adjusted automatically.
    - "I then did the same for the rest of the rows" was not needed, achieved nothing and probably caused the problem.

    I think "my spreadsheet stopped responding" was because every copied cell was controlled by the same checkbox, the first one, and it got confused.
    - There is no need to copy the cells [Cell link fields does what you need] and it does not achieve anything anyway.

    You do not delete checkboxes by deleting lines. They are not part of any line even though their referenced calls are.
    - You can right-click on each one, press Escape to get out of the context menu that will have appeared and then press Delete.
    - You can, as you know, use the shortcut keys you mentioned.
    - You can also use Home tab, Find & select, Select objects then select each one in turn by clicking on one then Ctrl-clicking on each of the others.


    Denis
      My Computer


  9. Posts : 35
    Windows 10
    Thread Starter
       #9

    Ok I now understand what I was doing wrong. Thank for explaining
    I have managed to do what I set out to do.
    Best regards
      My Computer


  10. Posts : 16,786
    Windows 10 Home x64 Version 22H2 Build 19045.4170
       #10

    Blake,

    Good.

    Oh, and welcome to TenForums.
    It's really worth making time to browse through the Tutorial index - there's a shortcut to it at the top of every page.
    - At the foot of the Tutorial index is a shortcut to download it as a spreadsheet.
    - I download a new copy each month so I can benefit from Excel's filtering capabilities when I search for topics of interest.
    - Tutorials are also listed by category at Tutorials - there's also a shortcut to that at the top of every page.
    - Both tutorial pages are searchable.
    - You can also search for TenForumsTutorials in many general search engines, such as Google, by adding site:tenforums.com/tutorials after your search term. For example, taskbar toolbars site:tenforums.com/tutorials



    Denis
      My Computer


 

  Related Discussions
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd
All times are GMT -5. The time now is 23:41.
Find Us




Windows 10 Forums