Excel freezes when too many checkboxes have been created and linked to individual cells.

Hi,

I'm trying to create a user friendly file where a supervisor can follow up and check how the department managers reporting to him are fulfilling their basic tasks on a weekly basis.
For this I created a table with each department on a row repeated every week and the columns represent the different weekly tasks.
In each cell, there's a checkbox linked to another cell outside the table and a Countif-formula in the cell containing the checkbox.
When creating new rows for the next weeks and checkboxes, I select the cell and drag it down/to the side. The problem here is that all the new checkboxes are linked to the same cell as the original checkbox, so I used the macro below in order to link other cells to the new checkboxes (found this code through the power of Google).

Please Login or Register  to view this content.

My issue is that the more rows I add the longer it takes for Excel to create them, and it takes longer for the macro to run each time. And when I've created 100+ rows, Excel is loading(for a long time or completely freezes for a couple of hours) whenever I check/uncheck the boxes for the latest week but not the first weeks.

My goal is to create a User friendly file in which the supervisor can plot different charts and measure #tasks fulfilled over time between the managers, specific tasks fulfilled over time etc.

If you can help me or have a suggestion of a better way to do this it'd be very much appreciated.

A nightmare!!

Why not have a Userform where Dept and Week - both drop-down lists - can be entered, together with the task list [could be checkboxes on the Userform] to be "ticked" , and the results (raw data) are put into a simple table.

Who enters the data - the Supervisor ?

Attached solution to a similar case where the O.P. encountered limitations of checkboxes imbedded on the sheet.
The attached is self expanding - enter data in cell 'B7' will then enable checkmarks to be placed in cells under each week/fortnight/month.
If this approach is of interest I will put an example together using your data sheet (if parameters are stated).

A VERY simple input form: see sheet "Data"

The Departments / Tasks are named ranges so can easily be changed and allows the checkbox captions to be changed (hard-coded currently).

Data is added in input sequence but this is a demonstration only!

Originally Posted by newtoexcel2024

Hi Toranchan. I only need a simple excel file where approver1 can tick his checkbox once he is satisfied with the details in column F. Same goes for Approver2 and processed column.

Thank you in advance in helping!

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread