Creating 'Select All' Checkboxes using Macros in Excel

Question

Hi all!

I'm making an attendance tracker in Excel using the improved checkbox functionality.

I've got most of the sheet set up but am stuck getting my 'Select All' functionality working perfectly. I followed a YouTube tutorial which showed me how to create a 'Select All' box for the column below it using VBA code while still allowing modification afterwards. For example, if all but one box needed to be ticked, it allows me to hit the 'Select All' box, then simply untick the box I didn't need, rather than manually clicking all the other boxes. Fantastic!

My problem now is that I have many columns of checkboxes, each which need their own 'Select All' box to control only that column.

I tried the obvious solution of just copying the entire code and pasting it below and changing the references, however that didn't work. I also tried adding another range inside the brackets next to the first range, but that didn't work either.

This is my first time using macros in Excel so I'm not sure what the correct terminology is, what the correct format for writing or editing this code is, or how to describe my issue in a search engine to get an answer.

I've created a simplified version of my worksheet and have shared it via OneDrive here. I don't think those macros work online so I've also taken a quick video showing how currently only the first column works. As you can see, I need a seperate 'Select All' box at the top of each column which only controls that column and works indepently of every other column.

Video: https://gyazo.com/135d91434ebbb76cffffbc75ca02adb0

Ideally, I'm looking for a solution that can be extended easily. At the moment I just have two weeks in the workbook, but there's potential to expand it up to 11 weeks if it works so I'm hoping I dont need to go through and manually enter all the ranges.

And finally, I'll have this sheet saved on my OneDrive and need to be able to edit it and use the 'Select All' fucntionality on my phone - do macros work on mobile, or do I need to look at an alternative?

Thank you for any assistance! :)

Answer 1

Hi Sonia Zanker,

Welcome to Microsoft Community.

Open your workbook, right-click on the worksheet name > View Code.

Paste the following code into the code editing area:

Private Sub Worksheet_Change(ByVal Target As Range) 

    Dim cell As Range 

    Dim rng As Range 

    Dim copyRange As Variant 

    Dim i As Integer 

    copyRange = Array(7, 8, 10, 11, 12, 13, 15, 16, 17, 18, 19, 20, 21, 22, 24, 26, 27) 

    If Not Intersect(Target, Rows(5)) Is Nothing Then 

        For Each cell In Intersect(Target, Rows(5)) 

            If VarType(cell.Value) = vbBoolean Then 

                For i = LBound(copyRange) To UBound(copyRange) 

                    Cells(copyRange(i), cell.Column).Value = cell.Value 

                Next i 

            End If 

        Next cell 

    End If 

End Sub

Please remember to save the workbook file as an Excel macro-enabled workbook.

I cannot upload videos or GIFs here or on third-party websites, but I have tested the script I wrote, and it should work properly.

Select All:

Unselect All:

When writing the code, I designed it to detect the values in the cells of the 5th row. Therefore, it should not be limited to working only for 2 weeks or 11 weeks. In theory, it can extend to the maximum number of columns that Excel can accommodate. You are free to extend the dates according to the original format as much as you like.

Let me explain a few things: 

As you may know, VBA operates based on desktop applications, so macros do not work in browsers (OneDrive.com), nor can they run on mobile phones. This "select all" macro is only supported in desktop applications. Unfortunately, you may need to find a solution that meets your needs on mobile phones.

About the idea of selecting all checkboxes on a mobile phone, another tip I can share is that the spacebar can quickly check/uncheck checkboxes within a selected range. This shortcut works across desktop applications, browsers, and mobile platforms.

I hope the macro I shared works for you and that the idea of selecting all on a mobile phone provides some useful direction. If you have any other questions or want to share more context, please let me know in your reply!

Best Regards,

Thomas C - MSFT | Microsoft Community Support Specialist

Answer 2

Hi Thomas,

Thank you so much, your new code works a treat!

I tried using the spacebar shortcut on my Android phone, it seems a little hit and miss to get the cells selected and get the keyboard up without editing any single cell (when I select the cells, it goes into editing mode for the first one and I'd only typing a 'space' after the 'TRUE' or 'FALSE' already in the cell. I've found pressing 'Enter' on my keyboard first and then 'Space' seems to work.)

I'll see how the others users of the spreadsheet get on with it :)

Answer 3

Hi Sonia

I'm glad I could help. If the macro I shared was useful to you, please select "Yes" below my reply. This will highlight the thread and help other users who want to implement the "select all" macro in Excel find the answer. Thank you for your cooperation.

Have a nice day!