Microsoft Excel Check List Compilation

Written by

A compilation of variations and enhancements of the interactive Microsoft Excel Check List Template

Clearly and Simply is supposed to be a blog on data visualization and data analysis. Hence, it is a bit embarrassing to admit that the most popular blog post ever is totally off topic. Believe it or not, it is the Microsoft Excel Check List Template published in August 2012.

Most hits on any given day, second most comments (176 so far), most downloads.

This is probably the case because I somehow managed to be shown on Google’s first page if you search for “Excel Checklist” or “Checklist Template”. Very popular searches, I guess.

Anyway. People seem to like this interactive check list and asked for various modifications and enhancements of the template. I already posted a few variations, but since they are buried in the avalanche of comments on the original post, I thought a compilation of the frequently asked enhancements could be helpful.

Today’s post provides the original check list and 14 of the most interesting modifications and enhancements: time and user stamped check lists, more checkboxes per item, check list with 3 possible states of an item and some more.

As always, the Excel workbooks including the unprotected VBA code are provided for free download.

01 – The Original Check List

Here is the mother of all battles: the original interactive check list published in the article Microsoft Excel Check List Template:

Download Check List Original (Microsoft Excel 2007-2013, 27.9K)

Double click in the last column to check and uncheck items and double click in the topic header row to expand or collapse all items of this topic.

02 – Check Boxes Left

Same functionality with the check boxes left to the text:

Download Check List Boxes Left (Microsoft Excel 2007-2013, 27.3K)

03 – 3 Check Box States

Toggling between 3 different states of an item: checked, unchecked and crossed out. Crossed out items will not be considered in the calculation of the completion rate:

Download Check List 3 States (Microsoft Excel 2007-2013, 28.1K)

04 – Time Stamp

Time stamp each checked item in an additional column. Unchecking an item will delete the stamp, so only the checking activities will be tracked:

Download Check List Time Stamped (Microsoft Excel 2007-2013, 28K)

05 – Time and User Stamp

Additionally to the time stamp (no. 4), each checked item will be stamped with the application user name and the Windows user name:

Download Check List Time and User Stamped (Microsoft Excel 2007-2013, 28.4K)

06 – Collapse Checked Topic

Each topic will automatically be collapsed as soon as all items of this topic have been checked:

Download Check List Collapse Checked Topic (Microsoft Excel 2007-2013, 27.7K)

07 – Option Button Check List

Instead of one column indicating if the item is checked or unchecked, this check list has two columns with mimicked option buttons, indicating if “yes” or “no” is selected:

Download Check List Option Buttons (Microsoft Excel 2007-2013, 24.7K)

08 – More Columns / Check Boxes

More than one checkbox per item, completion rates per check column and an overall completion rate:

Download Check List More Check Boxes (Microsoft Excel 2007-2013, 29.7K)

09 – More Check Boxes Time Stamped

A combination of the check lists nos. 4 and 8: more checkboxes per item including time stamps:

Download Check List More Check Boxes Time Stamped (Microsoft Excel 2007-2013, 31.6K)

10 – More Check Boxes with 3 States

A combination of the check lists nos. 3 and 8: more checkboxes and 3 states (checked, unchecked, crossed out):

Download Check List More Check Boxes 3 States (Microsoft Excel 2007-2013, 30K)

11 – Select Topic per Option Button

An additional cell to select / unselect a topic with a mimicked option button. If a topic is unselected, the topic will automatically be collapsed and all items of this topic will not be considered in the calculation of the completion rate:

Download Check List Select Topic per Option Button (Microsoft Excel 2007-2013, 28.3K)

12 – Select Relevant Items

An additional column to select which items are relevant. Only relevant items will be considered in the calculation of the completion rate:

Download Check List Select Relevant Items (Microsoft Excel 2007-2013, 28.3K)

13 – Expand and Collapse all Topics by One Click

Expand and collapse all topics by one click on a command button at the top of the list:

Download Check List Expand and Collapse all Topics (Microsoft Excel 2007-2013, 31.4K)

14 – Horizontally Consecutive Check List

A check list with more than one column coercing the user to check the boxes per item consecutively from left to right (otherwise an error message will appear):

Download Check List Consecutive per Item (Microsoft Excel 2007-2013, 31.4K)

15 – Vertically Consecutive Check List

A check list with one column coercing the user to check the boxes consecutively top down (otherwise an error message will appear):

Download Check List Consecutive Items (Microsoft Excel 2007-2013, 24.7K)

Many more combinations of the techniques used above would be possible, but I think this compilation of 15 workbooks (and the unprotected VBA code) should give you a jump start and enable you to create your own customized interactive checklist.

Anyway, that’s it with check lists here. I promise. I will get back to data analysis and data visualization topics soon.

Stay tuned.

Comments

128 responses to “Microsoft Excel Check List Compilation”

January 28, 2015

TK

Hi, I am afraid the download links are not working anymore?
Couldn’t download with IE nor Firefox… no data is transferred.
Can you please check?
TK

Reply
January 28, 2015

Robert

TK,
I double checked and the links are still working for me, in IE and Firefox. Please try again.

Reply
January 28, 2015

Einar

Hi Robert,
Is it possible to combine #05 Time stamp and user with #13 Expand/close box ? I tried to to it but fails when i copy it over in the VBA script.
also is it possible to set a completed time stamp for topic ?
I mean when G6 is R, could F6 be time stamped to ?

Reply
January 28, 2015

Robert

Einar,
yes this is possible. All you have to do is to copy the subs ExpandAll and Collapse All from workbook 13 into workbook 5, insert two command buttons and assign the two macros.
As per your second request: yes this is possible and it shouldn’t be too complicated. You would have to add a few lines of code creating a stamp for the topic in the subs ChangeTopicStatus and AutomaticSetTopicStatus.
Unfortunately I do not have a ready to use workbook for you. As I mentioned in the article, there are countless possible combinations of the techniques and I do not have the time to create them all. Have a look at the VBA code, I am sure you will figure out how you can do it on your own.

Reply
February 4, 2015

Dexter

Im sorry, I am a beginner here.
Im working on the Original Check list and wanted to add a few more Topics. I have increased the table size, but the completion rate is not affected.
How do I increase the table range in =COMPLETIONRATE(myCheckList).
Thanks.
D

Reply
February 4, 2015

Robert

Dexter,
you have to adjust the “refers to” of the named range “myCheckList”. Go to the Name Manager and make sure “myCheckList” covers your entire list. If it is still not working, select the cell with the completion rate, press F2 and return and it should work.

Reply
February 4, 2015

Norm

Thanks, Robert, the worksheets are terrific and the code is easy to follow. For the benefit of others, no need to “unzip” simply change the extension from .zip to .xlsm. Using Win 7, Excel 2013.

Reply
February 4, 2015

Robert

Norm,
this is an annoying issue and many readers had problems with other download links in other articles, too. So, thanks for pointing this out again.
One minor remark: as far as I know only Microsoft’s Internet Explorer changes the extension to .zip when downloading. If you right click and select “save target as” in Firefox or Google Chrome, you download an .xlsm file and can open it by double clicking without the need of changing the file extension.

Reply
February 13, 2015

Brian

I would like to combine # 4 with # 12, I’ve tried copying and pasting, expanding the named range. When I do i get errors, ie checkboxes don’t work, the font changes.
What else would i need to copy/do, I’m not very familiar with VBA stuff
Thanks

Reply
February 13, 2015

Robert

Brian,
send me your workbook by email and I will have a look.

Reply
February 13, 2015

Brian

Robert, thanks for the quick reply.
What I am trying to do is combine checklist 04 with checklist 12. When I copy the column from either sheet to the other one I am getting error, either nothing works or the font changes and the totals don’t work. I have expanded the named range.
Can you explain what I would have to move/copy from one worksheet to the other in order to make it work. I have a little VBA knowledge, if possible I would prefer that you advise what I would need to do, rather than you just make the sheet up, that way I can expand my VBA knowledge
thanks

Reply
February 13, 2015

Robert

Brian,
use the template #12 as the master and open both workbooks.
1. Insert a column between D and E in template #12
2. Go to the VBE
3. Search for “Now()” in the module modCheckList of workbook #4 and copy the entire IF THEN ELSE clause which contains “Now()”
4. Go to the module modChecklist of #12 and insert the IF clause at exactly the same position as it is in #4
5. Change the -1 parameters (all of them) in this IF clause to -2 (since the time stamp is now 2 columns left to the check box column)
6. Go to the sheet object in workbook #4
7. Search for “Now()” again and copy the IF clause around it
8. Go to workbook #12 and insert the IF clause at the same place. Again change -1 to -2
9. Four lines above this new IF clause, insert the following line:
Target.Offset(0, -2).ClearContents
This should do the trick.

Reply
February 15, 2015

Brian

Thanks, I’ll give that a try

Reply
February 17, 2015

Brian

Robert, how do I post code or a sheet for you to see?

Reply
February 18, 2015

Robert

Brian,
if it is just a smaller code snippet, you can post it here in a comment. If it is more code or the entre workbook, you can upload it to e.g. your Dropbox or OneDrive and post a link to the file here or – as already suggested – you send it to me by email (email link at the top of the blog).

Reply
February 21, 2015

Jodi

This is great! Thank you! How would I update the code for a single click rather than double? 2nd question – I’m using excel 2010 and the overall completion rate and the individual completion rate doesn’t auto update, it only will when I hit f2 + enter or i hit the save button – is this normal? I find it annoying, I’d like for it to automatically update the completion rate as I check or uncheck the boxes.

Reply
February 21, 2015

Robert

Jodi,
1. To check the boxes by simple clicking (i.e. selecting a checkbox) instead of double clicking, you have to use the event sub Worksheet_SelectionChange instead of Worksheet_BeforeDoubleClick in the worksheet object. The code inside the sub is the same, you only have to delete the line Cancel = True at the end of the sub.
2. I can’t reproduce this in the workbook I posted for download (workbook #8). I am not sure what is going on in your workbook, but I would assume you set the Calculation Options on the FORMULA tab to manual. If so, set the calculation to “Automatic” and it should work.

Reply
February 22, 2015

Jodi

Thanks for the quick response, I’ll try this out and get back to you! Much appreciated!!!

Reply
February 22, 2015

Jodi

Worked like a charm for both questions – wow, I didnt know that calculation option was even an option, i was racking my brain wondering why my calculations weren’t automatic anymore, they were at one point!! Also, I tried to move the worksheet into my workbook and it didnt work, i got Compile Error variable not defined. Any ideas how to get that into my workbook instead of moving all my tabs into your workbook? 🙂 Thank you thank you!!!

Reply
February 22, 2015

Robert

Jodi,
to transfer the checklist from one of my templates into another workbook, you have to conduct the following steps:
1. Copy the entire worksheet check_list to your workbook. Don’t copy the cell range. Rather click on the sheet and drag the entire sheet to your workbook. This way, the worksheet object code and the named range myCheckList will be transferred to your workbook in one go.
2. Open my template again (it was closed after step 1, since you moved the worksheet to your workbook) and go to the VBE (ALT-F11). In the Project Explorer (the window at top left of the VBE) click on the module modChecklist of my template and drag and drop it to the VBA project of your workbook.
3. Save your workbook as a macro-enabled workbook (.xlsm).
4. Close your workbook and open it again. It could be that Excel turned the calculation option to “manual” again. In this case you already know what to do…

Reply
February 23, 2015

Jodi

Thank you, i originally didn’t do the copying of the modChecklist!! One last thing (hopefully) 🙂 – On that same workbook (checklist time & user stamped), I created a copy of that tab. I wanted to put a formula in the 2nd tab (i already have the formula & it works) that says “if cell is checked and title is not blank then aggregate whatever is in that cell to the 2nd tab same area, my problem is whenever i stick a formula into there and then click the checkbox it deletes my formula’s. i want to have a Master list and an Sub List – if Master list items are checked aggregate only those checked into Sub List and check off what is completed in Sublist. it clears my formula everytime i check the box (to mark complete) Make sense? Hopefully, there’s an answer to this 🙂

Reply
February 24, 2015

Robert

Jodi,
this is not a bug, it is the approach I used in the code. In order to keep the implementation as simple and fast as possible, the code reads the entire checklist into a VBA array, makes the changes in the array and writes the entire checklist back to the worksheet in one go. If you have a formula inside the named range myCheckList, the code will overwrite the formula and replace it by the value.
The easiest way to solve this would be to keep the formulas outside of myCheckList, but I think this isn’t possible in your case, since you are trying to aggregate items by formulas, i.e. you need the formulas inside the checklist. I.e. you have to change the part of the code which writes the checklist back to the sheet. You would then write back only the column(s) which changed (time stamp and check boxes), not the entire list.

Reply
March 1, 2015

ptarlow

Thank you for this checklist.
First time using one in Excel
Basic question:
How do I enlarge the named formula?
Referring to:
More topics
Copy one entire topic (the topic header and the check items) and insert it at the end of the checklist. Change the numbering
in the first column for the new topic and its items. Enlarge the named formula “myCheckList” to cover the entire checklist.

Reply
March 1, 2015

Robert

ptarlow,
go to Excel’s Name Manager (on the FORMULA tab or click Ctrl-F3). Select myCheckList, go to the “Refers To” section at the bottom of the Name Manager and select the range of your check list. Click close and yes.

Reply
March 6, 2015

Poorna

If i add more rows below the topic, the expand and collapse is not taking the new rows into consideration. How do I fix this?

Reply
March 6, 2015

Poorna

Please ignore this.. I figures it out. Thanks for the templates .. Kudos

Reply
March 10, 2015

Denise

Curious if the “yes” column can be turned green and the “no” column red for Checklist 07?

Reply
March 11, 2015

Robert

Denise,
you can do this with simple Conditional Formatting: select “Format only cells that contain” and “Cell value equal to ž” and set the font or fill color as you like. No VBA required.

Reply
March 18, 2015

Macan

Hi, when i uploaded it into google docs, why the feature didnt work anymore? is there any way to fix it?

Reply
March 18, 2015

Robert

Macan,
the features are implemented in VBA (Visual Basic for Applications), Microsoft’s programming language for Office applications. As is, they work in Excel only.
It may be possible to rebuild the same functionality in Google Docs with Google’s Apps Script, but I do not know. I am not familiar with Apps Script.

Reply
March 23, 2015

Ramesh P Elaidam

Hi,
The double click does not work for me. Am I doing something wrong? When I double click the completion rate (Red Cell) changes from “0” to “#Name?
I am a novice at this so please excuse if it is something very simple that I have overlooked.

Reply
March 23, 2015

Robert

Ramesh,
I assume you did not enable macros. The double click functionality and the completion rate calculation will only work if you enable macros.
Check the Trust Center Settings (File, Options, Trust Center, Trust Center Settings, Macro Settings). I suspect it is set to “Disable all macros without notification” in your case. Change this to “Disable all macros with notification” and close the workbook.
If you open it again, you should get a notification about the macros under the ribbon which allows you to enable macros for this workbook and the functionality should work.

Reply
April 17, 2015

Tara

This checklist is really handy, but I was wondering if it’s possible to compile the items you have checked automatically and then transfer them into say a word document?

Reply
April 17, 2015

Robert

Tara,
sure, with some extra VBA code you can export the checklist or parts of the checklist to Word, PowerPoint or another application. Here is a link to get you started:

MSDN Export from Excel to Word

The code snippet provided there exports an entire range to Word. You could build upon that and insert a loop through the items of the checklist and a condition to export only if the item is checked.

Reply
May 5, 2015

Kenji

These checklists are great! with that being said… I think I’m in way over my head!
I would like to take checklist 12 and add another column of check boxes like #9. (ie ordered/received)
Then I would like to add time stamps next to those boxes.
And finally… I would like add a lot more topic boxes.
It appears my coding skills only get me from ctrl+c to ctrl+v.
If there is an easy answer let me know… or maybe another article I can study.
Thank you! 😉

Reply
May 6, 2015

Robert

Kenji,
it is possible, but there is no easy way. You have to combine the code of the 2 checklists into one new workbook. I would recommend you take #9 as the master, look at the code, try to figure out what is different in the code of workbook #12 and integrate those differences in the code of #9. The modules have only 123 (#12) and 168 (#9) lines of code and the code is commented. Once you understood how the code works, it isn’t complicated to make the changes.
There are countless options of combining the versions posted above and I do not have the time to implement and provide them all. I am sorry.
As per your second question: if you need more topics and/or more items, you have to expand the range of the name “myChecklist” and follow the topic / item numbering convention. Have a look at the original article for the details.

Reply
May 20, 2015

Scott

Hi,
Im not very literate with VBA etc. How do i add more topics? im using sheet no.9

Reply
May 20, 2015

Robert

Scott,
you do not have to change anything in the VBA code if you need more topics and items. Simply add topics and items as you like, extend the named range “myChecklist” in the Name Manager to cover your entire checklist and finally make sure you follow the naming convention in the first column (item number = topic number followed by a period followed by the item number). Please have a look at the original article. There is a section called “How to use this template for your own checklists” which describes what you have to do.

Reply
June 17, 2015

Tom

Could you perhaps explain how to append more checkbox columns like in #08?

Reply
June 17, 2015

Tom

Forgot to mention that I actually just need additional checkbox columns, in general without completion rates. If that simplifies anything.

Reply
June 17, 2015

Tom

Well never mind. Must have copied it wrong.
In case anyone else got the same “problem”:
Create additional column anywhere between the 5 original status columns. Copy one of them to this new column.

Reply
August 6, 2015

hudsonsedge

Is it possible to have each topic rollup only those items that are not checked? I’m starting with the Collapse All code and trying to edit that as another sub for a separate button- but I’m getting stuck.

Reply
August 6, 2015

Robert

hudsonsedge,
this workbook is based is based on the original template and it collapses only checked items:

Check List – collapse only checked items

I hope this will point you into the right direction.

Reply
August 6, 2015

hudsonsedge

Oops, forgot about that one. Thanks!!

Reply
August 6, 2015

Robert

hudonsedge,
you didn’t miss anything. I changed the code of the original template to meet your requirement.

Reply
August 23, 2015

william.jennings@yahoo.com

Professor,
I copied the sheet and would like it to work in a similar manner. not working.

Reply
August 23, 2015

Robert

William,
it does work, if you copy the entire sheet instead of inserting a new one and copying the cells to the new sheet.
If you insert a new sheet, you also have to define the name myCheckList on this new sheet and copy the code from the original worksheet object to the worksheet object of the inserted sheet.

Reply
August 25, 2015

Kathy

Hi Robert,
When I tried to download any of the files, there are no Excel files…just .xlm and other types that are not workable files. How do I access the actual worksheet? I saw the comment Norm made about changing the .zip extension to .xlsm, but I don’t see a way to do that.
Kathy

Reply
August 25, 2015

Kathy

Robert,
Nevermind…I downloaded it in Chrome and it worked!
Kathy

Reply
September 8, 2015

Chris

Hi,
I’m looking to create a checklist like number 5 but with a secondary check ie, at a supervisor level. Is this possible/how owuld i do it?
Thanks

Reply
September 8, 2015

Robert

Chris,
check list #9 has several columns and time stamps. I suggest you download that version and delete the columns you do not need (i.e. the last 6). Then have a look at the code of #5 how the user stamps are created and adjust the worksheet and code of checklist #9 accordingly.

Reply
September 11, 2015

Operi

Hi Poorna,
How did you resolve it? I have the same issue as I want to add more topic on the excel form.
Thanks a lot in advance.
Cheers,
G

Reply
September 11, 2015

Robert

Operi,
you have to adjust the name “myChecklist”. Go to the Name Manager in Excel and make sure the “Refers to” range of “myChecklist” covers all of your rows, including the new ones.

Reply
October 2, 2015

Karen

These are PERFECT!! I scoured the web trying to find a simple but not-too-simple checklist with no luck (except for a headache from glaring at the screen), until I found these! Thank you so very much for sharing all your work!

Reply
October 11, 2015

Jason

Hi Robert,
I wondered if you know how the checklist workbook could be shared so that multiple users could update simultaneously. I am using the time and user stamp WB but when attemptig to share in the conventional way I am receiving an error relating to tables in the WB.
Any assistance would be very much appreciated.
thanks,
Jason

Reply
October 11, 2015

Robert

Jason,
I never used the checklist as a shared workbook allowing several users to edit it at the same time, so I do not know if the checklist is working in this situation.
Having said that, I am a bit puzzled that you receive an error relating to tables, because there are no tables in the workbook.
I am sorry, but I do not think I can help you here.

Reply
November 26, 2015

imyuinny

Hi Robert,
I was wondering whether I am twat but does this work on Mac? Coz I seem to be having a hard time ticking the cells. GGrrrrr

Reply
November 26, 2015

Robert

imyuinny,
I never tested the workbook with Excel for Mac, but I do not see why the code shouldn’t work on a Mac (provided you enabled macros, of course).

Reply
December 31, 2015

Pally

Hello Robert,
Thank you very much for this great work. I searched a long time for an automated checklist to update our server park.
I use the multiple checkbox sheet.
Maybe my question is simple but after a day of struggling I found most of the answers I needed to change the things I needed, but one thing cannot find….
Just want to add some columns between column B and C.
The message appears all the time is:
“You can only check an item, if all precedent items are checked”
I want to use precedent items checked, its important in the process. But how to “move” or edit the selection?
I know how to change the selection for “mychecklist” I did to add some more rows.
Thank you a lot.
Frank

Reply
December 31, 2015

Pally

You cannot believe it…. some minutes after the post I just found it!
Public Const C_TEXT_COLUMNS = 4 ‘ Number of columns left to the status columns
Thank you anyway for this fantastic sheet!
Frank

Reply
January 13, 2016

John

Hi Robert,
Great sheets! Can you suggest how I duplicate the last 3 rows on number 5 sheet. So I have second user stamp, time stamp and status box.
I have tried playing around with it but cant get it to work :/
Many Thanks
John

Reply
January 13, 2016

Robert

John,
checklist #9 has several status columns and time stamp columns. Use template #9 as the master, have a look at the code in #5 to see how a user stamp is added, add user stamp columns to the worksheet and add the necessary code from template #5 to the code of template #9.

Reply
February 1, 2016

Mo

Robert,
I used one of your checklist that include several worksheet for all the different processes we need to audit. I was wondering if there was a way to create a pivot table to combine all the sheets into one Master checklist and be able to manipulate which columns to include. my list has three different independent completion rate columns. I would like only the number columns, subject and the three completion rates to show on the master checklist. I am not the most proficient when it comes to excel so any help would be greatly appreciated. thank you. Mo

Reply
February 1, 2016

Robert

Mo,
I do not think a PivotTable will get you what you want.
You could either use PowerQuery or a VBA routine to read the data from the sheets and write it into one table of a new worksheet. Combining data from several sheets into one is a very common requirement and you can find a lot of freely available VBA code snippets on the internet.

Reply
April 4, 2016

Neil

1)
Can someone please advise how do I do about adding columns/Items to ’08_check_list_more_columns.xlsm’
I am an excel noob so if step-by-step directions would be deeply appreciated :=>
2)
How may I add features from other sheets such as:
a) ’12_check_list_select_relevant_items’ – Relevant Column
b) ’05_check_list_time_and_user_stamped’ – Time/user Stamps
c) ’03_check_list_3_states’
I would imagine this is too much to ask for ‘step-by-step’ guide 😀 so willing to pay a fee if any forum members here are ok?

Reply
April 4, 2016

Robert

Neil,
here is the required step by step to insert more columns into template 08:
1. Insert entire columns (as many as you need) after column H, i.e. between Status Column 4 and Status Column 5
2. Copy the entire column H and paste it into to the columns inserted in step 1
3. Change the last parameter of the UDF StatusCompletionRate in row 3 for all inserted columns and the last status column. Column H has as the second parameter 7. Set the second parameter of the UDF in cell I3 to 8, in J3 to 9, in K3 to 10, etc.
4. Adjust the column headers as you like
As per your second question: this is possible, but you need to combine the VBA code of the various templates in one workbook. As you said, definitely too much to explain in a step by step. You can send me an email with your requirements and we can discuss what you need and how long this would take.

Reply
April 4, 2016

Mauricio

beginner here, just need to add rows to topics, I tried inserting but the inserted rows will not collapse like the others when double clicking topic- using Sheet 07
Great Work on this btw

Reply
April 4, 2016

Robert

Mauricio,
you have to take care of the numbering convention in the first column: the number of the topic followed by a period and the number of the checklist item. The code uses this first column to identify what is a topic and what is an item and which item belongs to which topic.

Reply
April 6, 2016

Neil

Hello Robert,
Many thanks for all your help and service to community 🙂 I was able to insert columns with your steps. And I sent you email with what all I intend to do. Thanks again and have yourself a great week ahead 😀

Reply
May 7, 2016

Wiz

Hi.
Just wanted to drop by and say thanks to a set of wonderful checklists made in excel.
I had a great time combining some of them!!! Keep the good work up.
Have a great excel time!!!

Reply
July 13, 2016

Evelyn

Hello,
Thank you for these great lists! I’m not a great Excel person and I’m having a bit of trouble with adding more rows to your checklist #11. I cut and pasted the above cell group with the gray header and all but the pasted cells will not “collapse” like the originals. Would you mind walking me through the process. I don’t have much experience so if you could explain it like I was an idiot that would be great. Another option, if this would work for you, is I could email you the sheet I’m working on.
Thanks,
Evelyn

Reply
July 13, 2016

Robert

Evelyn,
after you added new topics and items at the end of the checklist, you have to expand the named range myCheckList to make sure this name covers your entire list.
Here is the step-by-step:
1. Make sure your new topics and items follow the required numbering convention in the first column, i.e. a topic has a unique number (e.g. 6), the items the number of the topic followed by a period followed by another number (e.g. 6.1, 6.2, 6.3, etc.). This is crucial, because the first number is used to detect which item belongs to which topic and the period is used to distinguish between topics and items.
2. Go to the Name Manager (on the FORMULA tab of the ribbon or by pressing CTRL-F3)
3. In the upcoming Window click on myCheckList
4. Click on the range selection icon at bottom right of the window, select the entire range of your checklist and press Enter
5. Click on the Close button of the Name Manager and confirm your changes in the next window by clicking on Yes
This should do the job.

Reply
July 14, 2016

Evelyn

SO after sleeping on it and reviewing the questions and answers above I now have it working!
These are great lists! I’ve been wanting to make a master list for my business for a long time and just didn’t know how the start. These are just what I needed to tackle a big job. Thanks for all your work and generosity.
Evelyn

Reply
July 21, 2016

Phil

Hi, I am a beginner to VBA scripts, is there any way to combine #5 and #6? I thought I found the correct code segment in ChangeTopicStatus in #6 but it causes the script to break when pasted into #5.

Reply
July 21, 2016

Robert

Phil,
have a look at this workbook:
Download 05/06 collapse checked topic stamped (33.9K)

Reply
August 12, 2016

vinod

Hi Robert,
you are master on VB and excel and your templates are excellent
thanks for multiple templates
I need your help to modify 08_check_list_more_columns for my project requirements
Requirement A:
1. My requirement is to add / remove rows under each topic
2. by adding / removing rows want to keep “Individual completion rate” and “Overall completion rate” calculation according to the number of rows i add or remove
Requirement B:
1. Under each Status column i need one additional option check box to make check item on/off (similar to “07_check_list_option_buttons.xlsm”)
2. based on two check box conditions the “Completion rate” should get adjusted.
if you could provide step by step instruction it would be good for me to understand as i am novice on VB and excel formula.
Thanks in advance
Vinod

Reply
August 12, 2016

vinod

Just to add further
To add more on Requirement B: point 2 > Single “Completion rate” is required based on two conditions status for each check item
Vinod

Reply
August 12, 2016

Robert

Vinod,
Requirement A:
1. You can add or delete rows as you like, you just have to take care of the numbering convention in the first column of the checklist and – in case you add items at the end of the checklist – to make sure the named range covers the entire checklist. Please refer to my reply to Evelyn’s comment above for a step-by-step.
2. The Completion rate UDFs will always be based on the number of rows in the checklist, so there is no need to change anything here.
Requirement B:
This needs some extra coding for the additional checkboxes above each column and some changes in the UDFs. Have a look at checklist 12_check_list_select_relevant_items, which allows to decide for each item whether it is relevant and shall be included in the completion rate calculation. You would have to change the completion rate functions of the template 08 along these lines.

Reply
August 14, 2016

vinod

Hi Robert,
Thanks for your reply.
there is little success on adding extra column for your work sheet 8
later when i tried to add extra button (relevant) column from #12 to
#8 not working for me, i believe without your help i may not complete
is there a way you can help me further please ? i can send you the draft template which i worked upon..

Reply
August 14, 2016

Robert

Vinod,
I am sorry, but I do not have the time to do the VBA development for you.
The workbook and code is open, i.e. not password protected, though. If you do not have the experience in writing VBA code, maybe you can find a colleague of yours who does?

Reply
August 19, 2016

vinod

Thanks Robert.
i take this as opportunity to learn VBA 🙂 i will let you know if it goes well……..

Reply
September 14, 2016

Nirmal

Hi,
Your work is amazing. Thank you so much. I have one slight clarification. When I copy paste a topic, say for example, I copy paste Topic 5 to create an additional table (Topic 6), checking the boxes also change the box in Topic 5. How do I overcome this? I want to make changes only for the newly created table (topic 6). It shouldn’t inherit properties from the table I copied from.
I extended the name range, deleted and created the name range to select my entire check list, still no avail.
Your help is highly appreciated.

Reply
September 14, 2016

Robert

Nirmal,
you have to take care of the numbering convention in the first column: the number of the topic followed by a period and the number of the checklist item. The code uses this first column to identify what is a topic and what is an item and which item belongs to which topic.

Reply
September 15, 2016

Nirmal

You are wonderful Robert 🙂 Love from India

Reply
September 16, 2016

Giuseppe

Hello Robert,
I don’t understand as remove control checkbox on file 14_check_list_consecutive_per_item.xlsm.
Can you help me?
Thanks,
Giuseppe.

Reply
September 16, 2016

Robert

Giuseppe,
I am sorry, I do not understand your question. What do you mean by “remove control checkbox”? What exactly do you want to remove?

Reply
October 10, 2016

Brian

Hello,
This is very useful!
05 – Time and User Stamp is doing 95% of what I need.
How would I go about repeating Column E, F and G 2 additional times without impacting how the completion rate is being calculated?

Reply
October 10, 2016

Robert

Brian,
have a look at this workbook:
Download Check List more Cols stamped (36.7K)
This is a combination of versions 5 and 10.

Reply
October 10, 2016

nareshnani211@yahoo.com

Hi Robert,
your excel are very nice..
in the Horizontally Consecutive Check List currently we have status 1 to status 5. but i want add more columns to that.how to add more columns ?please help me..

Reply
October 10, 2016

Robert

nareshnani211,
1. insert new columns between colum H and I.
2. Copy the entire column H to the inserted columns in step 1
3. Increase the second argument of the UDF myCompletionRate in the cells right to cell H3 from 7 to 8, 9, 10 etc.
This should do the job.

Reply
October 21, 2016

Matt Douglas

HI, these lists are great. I have a question. I am trying to add multiple lists in a work book. I have a list for each day of the week. I have gotten the 1st tab to work, but I am having no luck getting the rest of the tabs to work. I assume i has something to do with the Named Ranges but even if I change those to a range, the functions are not working for any other sheet but the first one. Any help would be appreciated.

Reply
October 21, 2016

Robert

Matt,
it has indeed something to do with the named range myChecklist, but also with the fact that an important part of the VBA code (the Worksheet_BeforeDoubleClick sub) is not in a module but in the worksheet object. I assume, you inserted a new sheet and copied the content of the first sheet to this new sheet. This is not sufficient. You also have to define the range name on the inserted sheet (scoped to this worksheet, not to the workbook) and copy the entire code of the worksheet object of the first sheet to the worksheet object of the inserted sheets.
The easiest way of getting this to work is not to insert new sheets and copy the content of sheet 1, but rather to make a copy of the entire worksheet 1. This way, the sheet scoped range name will automatically be created and the copied sheet will also contain the worksheet object code. Just click on the tab of the first, keep the CTRL key pressed and drag the sheet to the right and you should be alright.

Reply
December 2, 2016

Enrico

Dear Robert,
Amazing work here.
I would love to use this to keep track on consignments. But I have one question to make it perfect.
I want to use version #9 but I am missing there the user stamp next to the time stamp, like in version #5.
Is there a rather easy way to add the user stamp to version #9. Because then it would be perfect for me to use it at work.
Looking forward to your feedback.
Greetings,
Enrico

Reply
December 2, 2016

Robert

Enrico,
have a look at my reply to Brian’s comment on October 10th, 2016 above. In my answer to Brian, I included a download link to a workbook which provides what you are asking for.

Reply
December 2, 2016

Enrico

Thanks a lot 🙂

Reply
December 2, 2016

Enrico

Hi Robert,
It is perfect, exactly what I need.
However, I am trying to have only two completion rate’s. When I delete the others all work fine, but the second completion rate stays on 0%.
How can I manage it that the second individual completion rate counts as well in percentage ?
Best regards,
Enrico

Reply
December 2, 2016

Robert

Enrico,
you are absolutely right, this was actually a bug in the workbook I originally posted in my reply to Brian. It was simple to fix, though. I only forgot to adjust the second parameter passed to the UDF StatusCompletionRate. I fixed this now. Please download the workbook again and it should work.
Thanks for pointing this out.

Reply
December 2, 2016

Enrico

never mind, I managed after all. Thanks again 🙂

Reply
December 5, 2016

Mike Cousins

Greetings Robert, what a great idea this check sheet is. I have used it for multiple tasks and found it bug free. I am looking for a way to save the checked column in a separate sheet as running history tied to a particular user and date, so that periodic audits can be held. Any ideas or redirection to URL would be appreciated.

Reply
December 6, 2016

Robert

Mike,
this is possible (of course) with a few extra lines of code.
Have a look at this workbook:
Download check_list_log_file.xlsm (35.9K)
It is based on the original check list and writes all changes to a log sheet. To keep it simple, it is coming with one disadvantage, though: if the user double clicks on a topic, only the change of the topic status is logged, not the (automatic) changes of all its items. You could enhance the code to improve this (if you want), but I think this workbook should give you a jump start.
Hope this helps.

Reply
December 6, 2016

Henk

Hi Robert,
Thumbs up for the wonderfull checklists.
I would like to make use of the checklist which you sent to Brian
(Monday, October 10, 2016 at 01:22 PM).
However, in this checklist the stamp and name only appear when the checkbox is “C_DONE”. I also want the name and time stamp to appear when the checkbox is “C_NA”. Can you help me out fixing this?

Reply
December 6, 2016

Robert

Henk,
sure:
1. in the worksheet object model: in the IF clause beneath the SELECT statement, delete “Or Target.Value = C_NA” from the IF condition and add it to the ELSEIF condition
2. in the sub ChangeTopicStatus of the module, add “Or varData(lngRowCount, lngActiveCol) = C_NA” to the condition of the third (the innermost) IF clause
This should do the job.

Reply
December 6, 2016

Henk

Robert,
Thanks a lot. Got it working!
Regards
H.

Reply
February 7, 2017

Nicki

Hi Robert,
Thanks so much for providing these excellent templates. I was wondering if it’s possible to create a nested list/hierarchy with your code. i.e. Topic.Subtopic.subsubtopic.item, with the mixed/fully ticked/fully unticked status filtering up like it does at the moment with topics when all items are ticked.
(our list goes up to five levels deep eg. 2.1.5.3.1 – eek!)
My starting point is #08. I have no VBA experience, unfortunately, but I am familiar with excel.

Reply
February 9, 2017

Robert

Nicki,
my apologies for this late reply.
What you have in mind is possible in VBA, of course. The current implementation simply differentiates between topics and items using two Boolean functions (IsItem and IsTopic). The functions check whether there is a separator (a period) in the first column of the list and if there is one, it is an item, otherwise it is a topic.
For your purposes, you would have to change considerable parts of the code and not only check if there is a separator, but rather count the separators to decide which level has been checked.
You can build upon the existing implementation and implement the necessary changes, but I assume this will require quite some extra coding and testing. Unfortunately, I do not have the time to do this for you. However, the code is open (no password protection), so maybe you can find a VBA expert in your office or a freelance Excel developer to help you with the implementation.

Reply
February 13, 2017

Rikus

wow, awesome templates – wish I had the know how to do macros. I am looking to create ethical audit excel spreadsheets with questions, tick boxes and graphs for clients

Reply
March 8, 2017

Mikhail

I am struggling to be able to change priority & status kust by clicking on the relevant cell.
For example i had data validation drop down list with priorities like:
HIGH
MEDIUM
LOW
&
Status list like:
OUTSNANDIG
IN WORK
COMPLETED
ON HOLD
NOT VALID
etc.
Every status was color coded with conditional formatting so it was easy to check what is red-outstanding and what is completed-green.
In addition to this all values were calculated in separate sheet for reporting. But i am struggling now to adapt checklist to aforementioned solution.
Is it possible to change (cycle through) these values just by single (preferable) or double click to the relevant cell?
For sure i can add several columns with checklist, but it will clutter this table even more. And i don`t understand how to calculate the values in this case.
And i would like also to have name/timestampe after any status change. (Not priority)
Can you help me on that?
P.S. I sent you my file with explanations.

Reply
March 8, 2017

Robert

Mikhail,
this is possible with some extra coding. I just sent you an email with the solution.

Reply
March 9, 2017

Mikhail

Thanks a lot again, Robert!
You saved me!

Reply
May 23, 2017

Jeffrey

Hi Robert,
I am facing a problem with your checklist. I apply formulas within some of the cells. But whenever i mark a task done it replaces the formulas within these cells with just the outcome of the formula.
So basically when i change the status the formulas from cells will disappear.
Is there a way that the formulas won’t be deleted?
Hope you know the answer for me!
jeff

Reply
May 23, 2017

Robert

Jeffrey,
the code reads the entire checklist into a VBA array and makes the user-triggered changes in this array. For performance reasons, the code then writes the entire array back to the worksheet in one go and thereby overwrites the formulas by values.
If you want to keep formulas in your checklist, you have to change the VBA code to only write back the column(s) with the checkboxes:
Replace
Range(“myCheckList”) = varData
by something like this:
Dim i As Integer
For i = 1 To UBound(varData, 1)
Range(“myCheckList”).Cells(i, UBound(varData,2)) = varData(i, UBound(varData,2)
Next i
You have to do this in 2 subs (ChangeTopicStatus and AutomaticSetTopicStatus)
This way, the code writes back only the last column of the list, i.e. the checkboxes. The columns left to the checkboxes stay unchanged, i.e. formulas or links inside those cells are retained.

Reply
May 24, 2017

Jeffrey

Thanks for your reply!
It works, but now i am encountering another problem. It seems that i cannot change the status of a topic with their items.
When i want to mark all items as checked, it seems that only within topic 1 this function works. But it will only check the first 3 items of this topic.
For other topics the functionality won’t work at all.
I changed the code as you suggested in both Subs.
I think maybe the array now dont save the values when a topic is checked?

Reply
May 24, 2017

Robert

Jeffrey,
I do not think this is a problem caused by the code change.
I’d rather assume it is a problem with the numbering of topics and items in the first column: the number of the topic followed by a period and the number of the checklist item. The code uses this first column to identify what is a topic and what is an item and which item belongs to which topic. I assume you may not have followed this convention in your checklist.
Another possible root cause for your issue is the definition of the named range myCheckList. Are you sure the named range covers the entire range of your checklist?
You can also send me your workbook by email and I will have a look.

Reply
August 2, 2017

alex

Great site! I’m trying to use #5 but want the check boxes to the left as in #3 (so stamps to the far right of the sheet). Can you help with this? I was able to update the code so that checking individual boxes places the stamps in the correct location, but can’t figure out how to do the same for clicking the check-all box in the topic header.

Reply
August 2, 2017

Robert

Alex,
have a look at this workbook:
Download 05 check list stamped right (33.3K)

Reply
November 28, 2017

vinod

Download check_list_log_file.xlsm (35.9K)
I would like to make use of the checklist which you sent to Mike
(Tuesday, December 06, 2016 at 10:20 AM).
I was wondering if it is possible to add three new columns (example: E,F,G) after column D and before status click column.
and show new columns text, number or date values in log sheet.
And Log report has to show:
Topic 1 (text or numerical values), No, check names, descriptions, new columns E,F,G,Time Stamp,Changed Status to.
Thanks for your help in advance.

Reply
November 28, 2017

Robert

Vinod,
sure, this is possible:
1. Insert three columns on the check list sheet
2. Insert three columns on the log sheet
3. Go to the VBE and the worksheet object check list
4. At the end of the sub, the log table is updated. You have to insert three new lines of VBA code to write the cell values of the check list to the row of the table on the log sheet. The code would look like this then:
.DataBodyRange(.DataBodyRange.Rows.Count, 1).Value = Target.Offset(0, -6).Value
.DataBodyRange(.DataBodyRange.Rows.Count, 2).Value = Target.Offset(0, -5).Value
.DataBodyRange(.DataBodyRange.Rows.Count, 3).Value = Target.Offset(0, -4).Value
.DataBodyRange(.DataBodyRange.Rows.Count, 4).Value = Target.Offset(0, -3).Value
.DataBodyRange(.DataBodyRange.Rows.Count, 5).Value = Target.Offset(0, -2).Value
.DataBodyRange(.DataBodyRange.Rows.Count, 6).Value = Target.Offset(0, -1).Value
.DataBodyRange(.DataBodyRange.Rows.Count, 7).Value = Application.UserName
.DataBodyRange(.DataBodyRange.Rows.Count, 8).Value = Now()
.DataBodyRange(.DataBodyRange.Rows.Count, 9).Value = Target.Value
.DataBodyRange(.DataBodyRange.Rows.Count, 10).Value = dblOldCompletionRate
.DataBodyRange(.DataBodyRange.Rows.Count, 11).Value = Range(“myCompletionRate”).Value

Reply
November 29, 2017

Michal

Hi Robert,
Thanks for sharing your files. In original check_list_template.
1. How to change double click into single click ?
2. Can i ran a macro that by pressing a button i will erase all the ticked boxes ?
3. is there an option that by using Delete keyboard button i will delete the tick not the whole box ?
Much appreciate your help.
Michal

Reply
November 29, 2017

Robert

Michal,
the implementation is based on the worksheet event procedure Worksheet_BeforeDoubleClick and the code inside this sub is fired every time the user performs a double click.
There is no similar event procedure for a single click. The only option you have is to use the event procedure Worksheet_SelectionChange which is fired every time the user selects another cell or range on the sheet. This would work in general, but it comes with a major disadvantage: If you click on the already active cell, nothing is happening, because the selection (the active cell) was not changed. I.e. you have to click on another cell first and then on the desired cell again to start the code inside Worksheet_SelectionChange. Furthermore, the code would also be executed if you select a cell not by clicking on it, but by moving the cursor with the keyboard (arrows, for instance).
That’s confusing and not very user-friendly. I would stay with the Double Click approach, which is the most intuitive in my opinion.
Regarding your second question. Here is a sub to set all tasks to “open”:
Sub ResetStatus()
Dim varData As Variant
Dim lngRowCount As Long
varData = Range(“myCheckList”)
For lngRowCount = 1 To UBound(varData)
varData(lngRowCount, UBound(varData, 2)) = C_OPEN
Next lngRowCount
Range(“myCheckList”) = varData
Set varData = Nothing
End Sub
Finally, as per your third question: it is possible using the Application.OnKey Method, but I wouldn’t recommend that.

Reply
December 26, 2017

sheila

nice checklist,
how about using this template in 2 or more sheet

Reply
December 26, 2017

Robert

Sheila,
duplicate the original sheet by CTRL-clicking on the tab and dragging it to the right or left.
Simply copying the cell range to a new worksheet will not work, because copying the range will not transfer the code and the named range to the new sheet. So, either drag the tab of the worksheet to the right or left while keeping the CTRL-key pressed or right click on the tab and use the command [Move or Copy].

Reply
December 27, 2017

sheila

thanks guys,,
its work

Reply
June 6, 2018

Alyssa

Hi, Robert! Just want to ask how to add “user stamp” in the Excel #9 workbook? Thank you in advance for your response.

Reply
June 6, 2018

Robert

Alyssa,
please have a look at my reply to John’s question on January 13, 2016 at 5:41 PM.

Reply
September 21, 2019

Ahmed

this is exactly what I’m looking for, if you have accomplished it, would you please share it with us?
thanks!

Reply
September 21, 2019

Robert

Ahmed,
as I wrote in my reply to Nicki, I do not have the time to implement this request. I do not know if Nicki enhanced the VBA code to accomplish this. Even if this is the case, I doubt Nicki will revisit this article and post the solution. I am sorry.

Reply
May 8, 2021

SK

These sheets have saved my life! Thank you so much for posting.
I am very new to Excel and know nothing about code… I inserted some columns to the left of the original Check Items. I figured out how to make the Checked Values keep adding to the correct percentage at the top of that column. I want to add more check boxes columns, and can’t seem to figure out how to make them work the same as the others. Can someone please explain in simple terms? I’ve tried reading the comments thread and I think the question has been answered, but I can’t seem to figure it out.

Reply
May 8, 2021

Robert

SK,
template 08 posted above has more than one column with a checkbox (5 in total). If you need more than 5, simply insert an entire column somewhere in between the first and the fifth check box column, copy one of the existing columns and paste it into the newly inserted column. Finally adjust the second parameter of the completion rate formulas in row 3.

Reply