A Microsoft Excel template for a structured Checklist with the option to check and uncheck by double clicking
Unfortunately my blogging activities slowed down to a crawl during the last few months and I left you waiting for new posts far too long already.
Be assured that this site is not dead. I will revive the blog during the next weeks and I am already working on a couple of new articles.
For one of the planned next posts I needed a checklist template. There are tons of Microsoft Excel and Microsoft Word examples available for free.
However, I couldn’t find a checklist template I really liked. Formats are always easy to change, but I was particularly looking for a checklist providing a convenient way to change the status of the checklist items. Furthermore it should be easy to use and easy to maintain. Some of the templates I found simply expect to type in an X (or something similar) to check an item, some are working with data validation lists, some have form control checkboxes. The one that came closest to what I was looking for is provided by my good friend Daniel Ferry, the Excel Hero here: Excel Dynamic Checkmark. Already pretty close to what I was after, but since it did not fulfill all of my requirements, I decided to create my own.
Agreed, today’s post is a bit off topic regarding the focus of this blog. It has nothing to do with data analysis, data visualization or dashboards. However, a nice Excel checklist template is always a useful thing to have in the toolbox.
With today’s article I am trying to kill two birds with one stone: to show a sign of life and to share my little checklist template with you.
The Idea and the Features
Creating a checklist in Excel is a piece of cake and for a basic version you do not need one single formula, let alone VBA.
Having said this, I always like some interactivity in my tools and templates.
Interaction with a checklist? You guessed it: an intuitive and convenient way to change the status of a checklist item, like double clicking:
- Double clicking the status column of a checklist item sets its status to checked, another double click sets it back to unchecked
- Double clicking on a topic sets the entire topic to checked, i.e. all items belonging to this topic. Double clicking again sets the entire topic back to unchecked
- If some, but not all items of a topic are checked, the status of the topic automatically turns into “mixed”, indicated by a box-in-box symbol (see main topic 2 in the screenshot above)
Double clicking on a topic header expands or collapses all items of this topic. This comes in handy especially when you are working with a large list. Agreed, you could also do this by hiding / unhiding or grouping / ungrouping rows, but the double clicking option is more convenient from my point of view.
The display of a completion rate and some conditional formatting tops off the look and feel of this little checklist template:
- Checked topics and items are shaded off with a grey font color
- The cell at the top right of the checklist shows the completion rate in percent. The fill color of the cell is red if less than 75% of all items are checked, green if all items are checked and yellow if the completion rate is between 75 and 100 percent.
The Implementation
I won’t go into the details here. Except for the very simple standard conditional formatting, the main job is done by a couple of small VBA routines (little more than 100 lines of code) based on the event driven sub Worksheet_BeforeDoubleClick.
If you are interested, have a look for yourself (download link see below).
How to use this template for your own checklists
Here is the good news: you do not have to understand, let alone change the VBA if you want to use this checklist. Download the template (see download link below) and you are (almost) good to go.
For sure you will have to make some changes of the template for your own checklist. Here is what you need to do for the changes you will probably want to make:
- More or less columns
Just insert as many additional columns as you need between the first (#) and the last column (status). If you don’t need e.g. the description, simply delete this column. All you have to keep is the first and the last column as they are
- More or less checklist items
If you need less checklist items in one or several topics, simply delete the rows. If you need more checklist items for a topic, insert rows and copy down the format from the row above. Finally make sure the numbering in the first column meets the defined convention (the number of the topic followed by a period and the number of the checklist item).
- 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.
- Formats
You don’t like the look and feel? No problem. Change the formats to whatever you like. Fill or border color, font and font size, borders, row height or column width. No matter what. The interactive feature will not be affected. Just make sure to keep the font type “Wingdings2” in the status column in order to display the empty or checked boxes.
Important:
Please make sure that your entries in the first column (#) follow the convention “topic number followed by a period followed by the item number”, e.g. 1.1, 1.2, etc. This is important because the VBA code uses the period to separate the topics from the items.
I am pretty sure this isn’t everything you could wish for, but I think it should cover the most important changes to turn this template into your own checklist.
The Download Link
Download the checklist template here:
Download Check List Template – (Excel 2003 workbook, 74K)
As mentioned in the introduction, this post is just the starting point of reviving my blog activities. So please stay tuned, more things will hopefully come soon.
Update on Tuesday, 20th of January, 2015
This post is one of the most popular articles here on Clearly and Simply and many people have asked for various modifications and enhancements of the template provided above.
I implemented and posted most of them, but since the download links are buried in the avalanche of comments below, I pulled together a compilation of the most interesting modifications and enhancements in this follow-up post:
Comments
225 responses to “Microsoft Excel Check List Template”
This looks very handy, but I can’t open it.
This is really useful. After reading The Checklist Manifesto by Atul Gawande, I have become quite a fan of this tool. I have posted one or two checklists on my blog – Checklist for Management Reports and Report objects [ charts and table ]. It is time to revisit the checklists – the updated version will be on your template.
Welcome back. I look forward to the forthcoming articles
Terry,
I double checked and it works for me. Maybe a security setting of your Internet browser or a firewall issue? Did you try to right click on the link and save it to your computer before opening?
Anyway, I will send the file to you by email in a minute.
Very nice and useful tool! It is quite adaptable, especially with the well commented code.
Your solutions always inspire, so (hopefully not drifting too much off topic) here is a little idea. If you wanted more detailed info on the status and completion rate, little bullet charts or bars (e.g. by means of Sparklines) could easily be added in each topic header and as an overall measure.
..and this, in turn, sparks off another idea: weights for each item in case they are not equal – this, of course, also applies to the current completion rate which would become more “precise”.
Good to have you back Robert!
Ulrik,
many thanks for your comment and your kind words.
Great feedback (as usual). Actually I was thinking about adding more visualizations to the check list but then I decided to keep it simple in the first version. I really like your idea of adding a bullet graph to each topic and one for the overall completion rate at the top of the list. This goes directly to my list of ideas for future posts.
Thank you!
very useful…i use something similar but it use it to check my work but i like the concept of this…and welcome back 🙂
look forward to some more great excel work!
Clear and simple, I’m going to use this template to keep track of my daily excercises. Looking forward to your upcoming articles!
Hi-
I really like this template. However, I am struggling with creating new topics. I have copied and pasted the new topic. But the double click function on the status boxes and the double click function on the topic header are disabled. How do I fix that. Also, how do I expand, myChecklist to cover the entire checklist?
Thank you.
Stewart,
thanks for your comment.
All you have to do is to expand the named range myChecklist to cover the entire checklist. Go to the name manager, click on the name myChecklist and change the reference from $B$6:$E$64 to e.g. $B$6:$E$76 (if you added one topic with 10 items). That’s all. You do not have to change anything in the code.
Another option for adding new topics / items is to insert new rows somewhere in the middle of the existing checklist and copy topic and items to the new rows. This way, the named range will be expanded automatically.
Let me know if you have any further questions or issues.
Nice Technique
Thanx for sharing
Hui…
Great tool. Good looking too. Thanks.
Hi Robert.
You did send it. I got it. Thanks again. I still can’t open it but now I can save it and it worked this time.
You’re probably right about the firewall. They’re pretty strict around here.
Great idea.
Very cool checklist. I am having trouble moving excel sheet to new workbook without it giving me an error in the VBA code and the Completion Rate goes to “#NAME?”. What do I need to make sure is in place when I copy and past worksheet to new workbook?
Ryan,
you have to
1. copy the entire VBA code to your new workbook (the module and the code in the sheet),
2. create or adjust the named range “myCheckList” in your new workbook,
3. save the new file as an Excel workbook with macros and finally
4. enter the cell with the completion rate and press enter to force the UDF to be calculated
Let me know if this does not solve your issue.
That worked! Thanks for the quick response.
This is really a great post an informative one!I really appreciate the effort you have put to make this handy post!!Hope to see more useful stuffs from you!! http://www.webdesignbizz.com/
What if you want to add multiple Completion rate columns? For example I have a release with 5 packages and I want to check the status on all the packages on the same page rather than individual sheets. Sorry not a whizz kid at all on Excel but this is perfect for what I need.
Kath,
thanks for your comment.
I am not sure, but it looks as if you want to have a completion rate for each topic, right?
In the template posted above I am using a UDF (user defined function) in VBA to calculate the overall completion rate, but you can also get what you want with a simple worksheet formula.
For the template posted above: Insert the following formula in cell G6:
=COUNTIF(E7:E16,”R”)/COUNTA(E7:E16)
This will calculate the completion rate for the first topic. Use the same formula for each topic header, i.e. just copy this formula to cells G18, G30, etc.
Does this answer your question?
Thanks Robert. About to give it a go. 🙂 Appreciate your help and sharing.
Hi – me again. Brilliant idea but… not quite what I needed. What I would like is the same functionality of the status column in any additional status columns that I add in after that. But without them linking to Column E, if that makes any kind of sense. And some more feedback for you. Some of the guys at work were looking over my shoulder at your checklist. They really like the double click function and the percentage completion rate. To quote them ” Very sexy”. LOL.
Kath,
I am not sure I understand what you are looking for. Do you want to have more than one check box per item in the list? And probably the item shall only be checked (i.e. shaded off with a grey font color) if all of the boxes of this item are checked and the topic checkbox would be checked only if all checkboxes of all items are checked.
Is this what you are looking for? If so: this is possible, but you have to change parts of the VBA code and the Conditional Formatting on the worksheet.
Hi Robert, Yes sounds like you have it. And its okay I know I was being a pain in the butt and suspected that it would require a change in the coding. Thanks so much for responding to my queries and especially for putting this template up for the rest of us to use. 🙂
Kath,
not at all.
I just wanted to make sure I understood you correctly before making any changes.
Have a look at this workbook:
Check List Template with more than one Check Box per Item
Is this what you are looking for?
Hey Robert, spot on! You are a star. Thanks very much appreciate this.
Hi Robert,
Really good idea!
But I realized a problem with it. It does not work with my hungarian local settings and hungarian Excel.
Seems the character you use in VBA for the open check mark is not the same as on the Excel sheet. On the sheet I can see pound sign but in VBA you use zloty-sign.
Really strange, so I checked 3 times.
If I replace the characters, it works correctly.
If you need any screenshots or tests, I would be glad to help you.
Cheers,
Kris
Kris,
many thanks for your comment. Interesting observation. Actually, of course I used the pound sign in the VBA too, but apparently your Hungarian VBA transforms this into the zloty sign.
Honestly, I came across a lot of international version issues already, but I never heard of this one before. And I do not have a clue why this happens.
Anyway, have a look at this version of the checklist template:
Download Check List Alternative Version (ASCII Codes)
In this workbook I used the ASCII values (as constants in the VBA) and the VBA function CHR.
Does this one work for you?
Thanks again for the heads up.
Hi Robert,
Neither the ASCII version works 🙁
But we internationalized using the AscW and ChrW functions which works with Unicode characters too.
I am sending you the file in e-mail, hope it will work for you.
Cheers,
Kris
Kris and Gábor,
thanks for figuring this out. Excellent idea! Your version works like a charm:
Download Check List Alternative Version Unicode (Kris and Gábor)
Many thanks for sharing!
Hi, first of all ,thank you for share this amazing tools. I am looking for a two column checkbox for item, (a yes/no one) and i wonder how to link both in order not to allow a double cheking (a yes and a no in the same item), it is difficult? (i´m a low leve user ;))
Gonzalo,
thanks for your comment. Sure, this is possible. Here is a simplified version using “option buttons” in 2 columns:
check_list_option_buttons (80.0K)
It is simplified because I took out the part of the code handling the status of the topics. However, although being simplified, I hope this example will point you into the right direction.
thank you for you quick answer, it´s what i´m looking for, now i try to tuned for my use.
In other point (sorry to bother you) i try to add more topics and i follow your instructions,(renumber the items, etc) but when i try to “Enlarge the named formula “myCheckList” to cover the entire checklist” expanding the blue-highlighted to the new topics i didnt get a “cloned” format topics.
Thank you in advance
Gonzalo,
sorry, but I don’t understand which issue you are encountering. Changing the range reference of “myCheckList” in the Excel Name Manager shouldn’t be a problem. What do you mean by “I didn’t get a cloned format topics”?
Thank you, it was my fautl. I tried to change it in a “brute force” way. Now it´s working perfectly.Cheers.
Thanks so much for this post. It’s great. Minor question, if I wanted to have the items crossed out instead of grayed out, how difficult would that be to change?
Whenever I change the rule to strikethrough the items when the box is checked it will do it, but the box will also become a struckthrough R. Is there a way to change the rule to strikethrough the items, but still keep it as a checked box when double clicked?
Johnny,
just delete the Conditional Formatting in column E.
Robert:
Nice template! Well done. Small request.
How about additional columns of checkboxes that are independent of each other and not counted against the Overall Completion Status? They would serve as just double click functioning checkboxes.
Example:
Three independent columns for “Does Not Meet”, “Meets” and “Exceeds” and then your original Status (retitled “Certified”)column that counts towards overall completion?
Thanks, Gary
Gary,
sure, this is possible. If you take the example I provided in my reply to Kath, you simply have to change the UDF CompletionRate. The completion rate has to consider only the first column (certified), not the others. The VBA code would look like this:
For lngRowCount = 1 To rngCheckList.Rows.Count
If IsItem(rngCheckList(lngRowCount, 1)) Then
lngItems = lngItems + 1
If rngCheckList(lngRowCount, 4) = C_DONE Then
lngCheckedItems = lngCheckedItems + 1
End If
End If
Next lngRowCount
Does this answer your question?
Great stuff Robert – it looks great and I am going to use this as a basis for my checklist!
One question: How can I the tick cell to change colour when ticked? I usually use Conditional Formatting but I cant copy the tick to use in “containing specific text” field?
Ideally I would also like to add a “unable to complete” (I am adding an extra “notes” cell next to the checklist items for further details) – I was thinking unticked was blank – ticket was green and unable to complete was orange… but how could I do this keeping your simple and classy design?
Chriss,
actually there is already Conditional Formatting to shade off the font color of the entire item after is checked. You do not need the “containing specific text” rule, simply use Cell value equal to “R”.
For the “unable to complete” requirement, you can use another CF rule: a formula to determine which cells to format. The formula would look like =G7=”unable to complete” if your notes are in column G.
Please be advised that you have to uncheck “Stop if true” for the CF rules (only available in Excel 2007/2010) to make all of them work.
Very useful
Thanks for sharing 🙂
Hi. Really working to automate my life. I love your checklists and look forward to following your work. Question, what if you want to put the check boxes to the left of the template rather than on the far right? Often I have lists with many columns of information associated with a task and clients want to be able to scan down the left and see what remains open and then scan toward the right of those items to see associated information.
John,
thanks for your comment and kind words.
It is possible to have the check boxes in the first column of the list or wherever you want to have them. Having said that, the existing VBA code expects a check list with the topic / item numbers in the first and the check boxes in the last column. To achieve what you are after, you have to adjust the code wherever it refers to a column of a range or the second dimension of the VBA array. No rocket science, just some laborious work and testing.
Cheers Robert for reply.
I dont think I made myself clear – what I am looking for is a way to mark the box in some other way (not a tick) for “unable to complete”
Any suggestions of a way to do this?
Chriss,
something like this?
Download Check List Template 3 States (77.5K)
Yes exactly like that!
What character is the X so I can add some conditional formatting?
Many thanks again – you are very quick and amazingly helpful 🙂
Chriss,
you are welcome.
The X in the box is a “Q” in Wingdings2. I already used it in the Conditional Formatting to shade off those items in grey font color, too. Have a look.
right on. I’ll tamper with it, break it, cry a little, then figure it out. LOL. I appreciate your talents. Do you allow for donations or sell templates you have created? Provide a reseller program?
John,
have a look at this workbook:
Download Check List Template Boxes Left (77.0K)
As for your second question: no, I do neither sell templates nor have a reseller program. If you are looking for something special or have an idea for an interesting template, please let me know. I am always looking for new blog post ideas. No promise, though…
Hi Robert, this is a fantastic checklist – many thanks for developing. I have amended the “Check List Template with more than Check Box per Item” for my own purposes & it is working well. However, I would ideally like to incorporate 3 states (same as your “Checklist Template 3 States” spreadsheet) into this spreadsheet. I have looked into the VBA code of your “Checklist Template 3 States” spreadsheet where you have defined a Public Const C_NA = “Q” for an “X” check as a third option. I have tried copying the relevant code into the “Check List Template with more than Check Box per Item” spreadsheet but can’t seem to get it to work correctly. Any chance of updating the “Check List Template with more than Check Box per Item” to allow for 3 states?
Many thanks,
Anthony
Anthony,
here you go:
Download Check List Template more Check Boxes 3 States (82.0K)
Wow! So glad Google led me to this page. I’m going to be using the the simpler version of your checklist sheet to track my progress in learning some advanced software. I read thru comments and may end up using the multi-status / state versions as I progress as they look pretty amazing.
THANKS!
Scott
P.S., it’s working in Excel 2013 just fine.
Thanks Robert – you are a legend. I’ve noticed that the macros don’t work when I protect the sheet (I want to protect the description of the checklist items so other people cannot overwrite). Do you know of any solutions?
Hi Robert – think I have solved the above. I had data validation set on the checkbox cells to R, Q, £ & the in-cell dropdown selection ticked. Double clicking on the cells didn’t seem to work when the sheet was protected (if I turned protection off then back on manually it did work, but on closing & re-opening spreadsheet it wouldn’t work again). By removing the in-cell dropdown the macro is now working so double clicking the cells works even when the sheet is protected. However, this raises another problem. I dropped into the worksheet a little macro to add a comment when the box = “Q” (i.e. cross boxed as not being complete) – see code below. This works if Q is typed into the checkbox or selected from a data validation list, but it won’t run when double clicking the cell to change it the crossbox. Any ideas?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Str As String
Dim cmt As Comment
On Error Resume Next
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range(“myCheckList”)) Is Nothing Then
If UCase(Target.Value) = “Q” Then
Str = InputBox(“Please insert comment why task not undertaken”)
If Str = “” Then Str = “NO REASON GIVEN, EDIT COMMENT!”
With Target
.AddComment
.Comment.Text Text:=Application.UserName & Format(Date, ” dd-mm-yy”) & “@” & Format(Time, “hh:mm”) & ” ~” & Chr(10) & Str
End With
Set cmt = Target.Comment
With cmt.Shape.TextFrame.Characters.Font
.Name = “Arial”
.FontStyle = “Regular”
.Size = 12
End With
With cmt.Shape.TextFrame
.AutoSize = True
End With
End If
End If
End Sub
Anthony,
in order to keep the VBA 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 the sheet is protected and the cells are locked (Format|Protection|Locked is checked), the code does not work, because it can’t access the cells. If you format the cells as unlocked, users can overwrite the input of cells although the worksheet itself is protected.
In other words: to get what you want, you have to format the cells as Locked and to add the statement
ActiveSheet.Unprotect
at the beginning of the sub Worksheet_BeforeDoubleClick
and
ActiveSheet.Protect
at the the end of Worksheet_BeforeDoubleClick.
Anthony,
as for the whole sheet protection issue, please read my reply to your other question.
The code you inserted to add a comment works fine for me (even if the sheet is protected) as long as you unprotect the sheet in the VBA code at the beginning and protect it again at the end of the code.
Very useful, looked all over and this is the best checklist I’ve found.
Only problem is i’m trying to use this for a movie checklist and I would like to know how can i go about alphabetizing the list. The part underneath the topic headings. Sorry new to excel.
Daniel,
thanks for the comment and sorry for the late reply.
I am not sure I understand what you mean by “alphabetizing the list”. Do you mean you want to sort the item names ascending or descending? If so, simply select the cells you want to sort (not the entire rows), click on Sort&Filter on the Home Ribbon and select Sort A to Z or Sort Z to A.
Or am I misunderstanding you?
I love this template. I added a couple columns in the middle, but when I double click a check box, only some of the cells get grayed out.
How can I update the cells that get included in the conditional formatting when double clicking a check box?
Chris,
thanks for your comment.
Actually, if you added columns in the middle, the conditional formatting should automatically be in the new columns, too. Anyway: simply copy the formatting to the new cells by e.g. Copy|Paste Special|Formats and it should work.
Checklists are great – and thanks for your work! However sometimes you need something a bit more sophisticated to track your activity. I’ve created a fairly simple yet sophisticated Excel Project Planning Template which enables you to create a project planning document without relying on charts but still gaining the visual aesthetics associated with a gantt chart. The example is freely downloadable and offers advanced features such as non-working, weekend and holidays and is very simple to modify to suit your own project. For more information see http://www.mlynn.org/2012/05/excel-project-planning-spreadsheet/
Enjoy!
That worked perfectly.
One more thing – when all check boxes are selected my total complete box is only 96%. Do I have to change something to make it total 100%?
Thanks again!
Chris,
did you add new rows at the end of the checklist? If so, you have to adjust the named range myCheckList to cover the entire range.
If this doesn’t help, here is another idea: simply select the cell with the completion rate, press F2 and then press Enter to rerun the UDF.
If none of them works, please send me your checklist by email and I will have a look.
Hi, Great template but I cant seem to add any more topic section. I’m using excel 2007. Any ideas please. Alan
Alan,
see the section “how to use this template for your own checklists” in the article. If you add additional topics at the end of the list, you have to enlarge the named range “myCheckList”.
This spreadsheet is brilliant! This was exactly what I was searching for.
However, I require checklists for daily, weekly, monthly, quarterly, half yearly and annually. And I need them all in one worksheet, though it can be in different tabs.
I tried integrating the sheet you made for Kathy into the original sheet but it was giving me some VB errors. I did try to fix it on my own, but that didn’t work out since I really dont know VB.
Anyways if you do have the time and could help me out, I would like a sheet where I have multiple status columns, each with their own completion rate. This way I can group them together for each week and open the week that I want whenever required.
Nikhil,
if you need more than one column, you do not have to integrate something into the original workbook. Simply download the workbook I posted for Kath in my reply to her comment.
If you need more than one checklist in your workbook, simply duplicate the sheet and the checklists should work right away.
If you want to have individual completion rates, you need another user defined function. The code would be similar to the existing UDF: pass the number of the status column to the UDF and do not loop through all status columns but only through all rows of the specified column.
I hope this will point you into the right direction.
Thank you Robert, I was experimenting with the original sheet instead of working with the one you made for Kath. Thanks for pointing me to the right direction.
Once again thanks a lot for making this and sharing this with us all. Really appreciate it!
Hi, Finally managed to add more topics but cannot set up the individual percentage calculation cell for each topic section.
Any help would be greatly appreviated.
Thanks, Alan.
Alan,
you can either write an additional UDF calculating the completion rate per topic or simply use a cell formula like this:
=COUNTIF($E$7:$E$16,”R”)/COUNTA($B$7:$B$16)
Robert, Thanks for your extremely! quick response. I used the formula for topic 1 completion rate and it works fine. I then added the same formula (adjusted for the appropriate cells) into topic No:2 completion rate cell and when I check an item it changes to a number and the formula disappears. Can you tell me what I’m doing wrong?
Thanks again, Alan.
Alan,
for reasons of simplicity and performance, the code always reads the entire checklist into a VBA array, changes the values and writes back the array (i.e. the entire checklist) to the worksheet. In other words: the code overwrites all formulas within the named range “myCheckList” by their values. Simply add the topic completion rate formulas in the column right to the checklist and you should be alright.
Hi, Thanks again for the quick reply. I have put the formula to the right of the column and of course that now shows the rate. I don’t know if it looks ok. being “outside” the original column, but it’s there. Anyway, thanks a million. Alan.
Alan,
thanks for the feedback. I understand, it may not be perfect to have the completion rate right to the checklist. You can still adjust the VBA code to either update only the status column when writing the values back to the worksheet or even do the topic completion rate calculation within the VBA. The VBA code is open in the template I posted for download, so please feel free to adjust it to anything you need.
Hi Robert, Thanks again for your response. I will have a go at what you have explained. Thanks again. Alan.
Thanks Robert for the checklist template. I found it veery interactive and simple to use. I’ve picked your template to create a Use Case checklist for my project and found to be vry useful. Thanks again for your good work.
Regards,
Alok
Great work! Thanks for sharing.
Hi Robert. I am in love with your checklist templates, well done! Thank you very much for sharing your work with us.
I have a request. Is it possible to make the checklist so that each column contains its own completion rate? For Example, a completion rate for Column “Status 1”, another completion rate for Column “Status 2”, another completion rate for Column “Status 3”, and so on.
Thank you very much!
AllieAmy,
thanks for your comment.
Have a look at this workbook:
Check List More Completion Rates (119.5K)
Wow! This is amazing! It works like a charm. Thank you so much for the quick response. Keep up your great work!
This is a terrific checklist! I was merely looking for a template for ideas but love the calculated completion rate. I actually deleted two topics (so have only 3) and when each is topic is completed I get a completion rate of 77% (33 task items). Is there a way to bump this to 100% or how is each task percentage calculated? Any feedback would be great as I’m no VB or Excel guru
Thanks
Mike
Mike,
thanks for your comment. I am sorry, but I can’t reproduce your issue. If I am deleting 2 entire topics, the checklist is still working like a charm for me, including the calculation of the completion rate.
The calculation of the completion rate is done by VBA (User Defined Function). The code counts the number of checked items and divides it by the total number of items, i.e. the number of rows with a separator (decimal point) in the first column of the list.
If you can’t find the root cause for your problem, you can send me your checklist by email (email-link at the top of the blog) and I will have a look.
Amazing Macro, has helped me in many situations.
I have been working on modifying it and been unsuccessful. I noticed some modifications you have made and what I am trying to do is, So on the box that you double click that populate the entire boxes and vise versa. I need two columns that have there different percentages and the overall percentage of the 2 columns. But on those two columns I need it so it has its individual boxes that will only populate the entire boxes only in that column.
Chris,
thanks for your comment. I am sorry, I don’t get it. I understand that you need 2 different completion rates for 2 columns and a total completion rate for all columns. What I do not understand is your requirement “on those two columns I need it so it has its individual boxes that will only populate the entire boxes only in that column”. I am sorry, I don’t understand what you are after. Can you explain in more detail (preferably by email, email-link see above)?
Your work is really amazing, is helping a lot!
A small matter, it is possible to add a feature to expand and collapse all topics?
my knowledge of vba is very small if you could add that would be perfect.
In my case i have many sub groups and compact form is very good for an overview.
Thanks
Thiago,
thanks for your comment. Sure, this is possible. Have a look here:
Download check_list_template_expand_collapse_all.xls (78.5K)
Robert,
This is the checklist macro I have been searching for! I am planning on configuring it for my needs but I am looking a status report for my execs to see where we are according to plan. Have you made any progress on the reporting functionally that you mentioned in the comment on Aug 29th, 2012? Or do you have any ideas on where I can look to find examples of using a template like yours to produce a high-level status report?
Best,
Jenny
Jenny,
thanks for your comment. I assume you are talking about Ulrik’s suggestion of adding bullet graphs to visualize the completion rates, right?
Unfortunately no, I haven’t done anything in this direction and I do not know of a site providing such a template.
However, adding bullet graphs or something similar wouldn’t be too complicated. Matt Grams had a great guest post on this blog explaining how to create bullet graphs:
Bullet Graphs for Excel: A Simple Way?
Another option would be Fabrice Rimlinger’s Sparklines for XL Add-In to create bullet graphs:
Sparklines for Excel
There are other resources how to create bullet grpahs in Excel, but I hope these 2 links will get you started.
Robert,
I had to sign in and tell you that this spreadsheet is amazing. I’ve been looking forever for advice, instructions… anything to help me cater to my needs with this spreadsheet. to find something this simple and easy… i knew it had to exist and you made it possible. All these questions and tips above and I was able to manipulate your excellent work to suit my very simple electronic checklist. I can’t say enough about how helpful this was. Thank you so much.
That said, I do have one question. Is there a way that you know of that, say, when you double-click the check box to have it automatically do a time/date stamp in an adjacent cell? this would be tremendously helpful for my particular scenario.
Thanks!
Bee,
many thanks for your comment and your very kind words. I am happy to hear you found the template helpful.
With regards to the time stamp: this is no problem at all. Have a look at this version of the checklist:
Download Check List Time Stamped (79.5K)
Let me know if you have any questions.
Robert,
That is wonderful. Not being very good with VBA, is it possible that you could concoct a specific checklist template for me? I’m having trouble with editing your work and combining all the ideas to suit exactly what I need.
If I may be so bold, I need a conglomeration of a lot of the examples that you’d made for other readers that I can’t seem to make work together.
Essentially, I have multiple topics that I would like to analyze laterally, not vertically. Each topic has three subsets which will require at least five check boxes and will need completion ratios for each subset of five check boxes, as well as an overall completion rate for each topic.
Ideally, the timestamp would occur under each check box.
I hope that I’m making sense. I would love to have your knowledge and not have to bother you about it.
I’ll be looking into the ExcelHero program in the future, definitely.
Thanks so much in advance.
Bee,
I’d suggest you send me the workbook you are having so far by email (email-link see at the top of the blog) and I will try to fix what has to be fixed.
Robert,
Believe it or not, I got what I needed accomplished with the exception of one point, I just can’t get the repeated topics to function properly. Along the lines of what Alan said previously, the percentage works initially, and then the function is removed after one click. I really don’t understand how that was resolved.
thanks much,
Bee
Bee,
as I tried to explain in my reply to Alan, the VBA code reads the entire checklist into a VBA array, manipulates the checkboxes of items and topics in this VBA array and then writes back the entire array to the worksheet. If you have formulas within your checklist, the last step overwrites those with the values of the VBA array. This is how I designed the code. If you want to keep the formulas, you need to change the VBA code and write e.g. only the check box column back to the sheet.
I am trying to remove the grey out thing. I can not find where to do this. Can you tell me where in the VBA this is changed as there in no formatting on the worksheet that I see.
Josh,
this is done by Conditional Formatting. Simply remove it (Home Tab, Conditional Formatting) and the “grey out thing” will disappear.
Ohhhhh Okay got it thanks a lot! This checklist has worked wonders!
Robert,
Thanks so much for posting these checklist templates. These are the best checklists I have been able to find anywhere. They are not quite what I need. I would like to have a checkbox on the Topic line trigger the hide/show of the Topic Items rather than double-clicking on the Topic name. Sort of an expanding checklist based on the status of the Topic line checkbox. I hope this makes sense. Thanks again for your great work.
Andre,
have a look here:
Check List Template Collapse Checked Topic (78.0K)
Something like this?
Hi Robert,
Yes, that is very close. The behaviour I need is a bit of the opposite. When the main Topic line checkbox is “ticked” then the Topic Line entries would be shown. The template that you sent through does the reverse… it hides the line entries when the Topic line checkbox is ticked. Thanks so much for your reply and template.
Andre,
this would be possible, of course. However, this requires reversing the basic logic of topics and items in the checklist:
At the moment, a topic is checked, if all its items are checked and vice versa. If a few but not all items are checked, the according topic has a “mixed” status. If you check a topic, all items of this topic are checked and vice versa.
With this basic logic your idea would not work, because unchecking a topic would collapse the items and you cannot access them anymore. Thus, you have to reverse the logic, i.e. checking a topic would uncheck and expand all items. Unchecking a topic would check and collapse the items.
It may be only me, but I find this pretty confusing. If I see a checked topic, I expect all items to be checked, too.
As I said, you can implement your idea based on the existing VBA code, but I would not recommend.
Hi Robert,
I suppose I am looking to do something a bit differently. What I have in mind is a sort of procedural checklist where the Topic level would be a yes/no question. If the answer is yes then the Topic would expand to display its items as requirements based on the yes. If the answer is no then the user would move onto the next Topic/Question.
It may be that this would be confusing and I would need to rethink things.
Andre,
how about adding “option buttons” to the topics and expanding / collapsing the items based on the status of the option buttons? They are not real (i.e. form control) option buttons, but rather characters for a full and an empty cycle from Wingdings2. Have a look here:
Check List Template Topic Option Buttons (74.5K)
If you do not like the option buttons, you could easily change the VBA to use “yes” and “no” or any other text or symbol you may prefer.
How about this?
Hi Robert,
That is getting much closer. I have managed to change the option buttons to use “yes” and “no” but my VBA abilities are limited to about that. I would like to be able to put the option button (yes/no toggle) in its own cell/column.
Is it possible to alter the way the status checkboxes work? What would be ideal for me is a “no” in the option button column of the Topic line results in a ticked checkbox and no expansion of items. If the option button column of the Topic lin is a “yes” then the items expand (as in your last template) and the Topic line and item lines are all in an unchecked state with the Topic level Status checkbox only being ticked when ALL the item line Status checkboxes are ticked and being in a partial ticked state when some items lines are ticked but not all of them.
I hope this makes sense.
Thanks so much for you help. I already know a lot more about how VBA and excel work as a result.
Cheers,
André
Andre,
have a look here:
Check List Template Topic Yes No (81.0K)
Hi Robert,
That is brilliant! Thanks so much for you help.
Cheers,
André
Hi Robert,
Is it possible to show different sets of Items depending on the state of the Yes-No in the Topic line?
In other words, a “Yes” in the Topic line shows Items 1-5 (Items 6-10 are hidden)and a “No” in the Topic line hides Items 1-5 but shows items 6-10.
I could probably find a way to work around this but am thinking that this functionality would be pretty cool. 🙂
Thanks again,
Andre
Andre,
almost everything is possible with VBA.
However, to implement your suggestion, you have to either hardcode some definitions in the VBA code or to provide additional cells on the worksheet to define what should happen, like which items shall be shown if there is a “yes” in the topic line and which are shown if there is a “no”.
I have to be honest with you: I do not really see the advantage of the feature you suggested. Wouldn’t it be easier and more straight forward to simply divide the topic into several topics (one topic for items 1 to 5 and an additional topic for 6 to 10)?
Hi Robert,
I agree it would be easier to us separate Topics wherever possible…
I am able to work out most of the checklist with separate Topics. Some sections of my checklist should result in different “Items” based on the Yes/No of the Topic and I have not been able to come up with a better solution. Still working on it.
Would a hardcoded definition be something like: If Yes show Items 1.1 to 1.9, If No show Items 1.10 to 1.19?
Andre,
yes, something like this, but I wouldn’t recommend hardcoding this in VBA, since you would always have to change the code if e.g. the number of items changes. I would rather use another column on the checklist to define for each item how it should be treated, i.e. displayed if the topic is “yes” or displayed if the topic is “no”.
Hi Robert,
Using another column makes a lot of sense. It would be much more flexible.
Cheers,
André
Hi Robert ! I love this checklist
I have one question … it defaults to Light Grey when checked off complete – how do I change that to a different color?
I tried in the conditional formatting , the color changes but now the check mark does not work anymore , when I doubleclick complete is just shows R rather than checkmarks
Any tips?
Suzanne,
thanks for your comment and your kind words.
I can reproduce the issue you are describing. Believe it or not: it isn’t my fault, it is an Excel issue. The checkboxes are no real checkboxes, they are mimicked by using the appropriate symbols of the font Wingdings 2 (“R” looks like a checked box in Wingdings 2).
The Excel issue is the following: if you are changing only the font color in an existing Conditional Formatting, Excel is resetting the font type (to Cambria in my case, I am not sure if this is the same in your installation). That’s why you see the “R”: the checkbox column isn’t formatted with font type Wingdings 2 anymore. Don’t ask me why Excel is doing this. One of Microsoft’s secrets, I guess.
The only workaround I see is simply removing the existing Conditional Formatting and recreating it using the font color you prefer for checked items. I know, this is kind of brute force, but it should only take a few seconds to do this. The good news: you do not have to change anything in the VBA code.
Hi Robert,
how to get the particular check gets highlighted at a particular time. i have a seperate column with the timings. Suppose if the particular check to be done at 09:00 AM, it should get highlighted. By Default it should compare the system time and gets highlighted. is this possible?
Need A report button at the top to send email. what ever the checks have completed it should send those only in the email.
Maddy,
the highlighting of the items is done by standard Conditional Formatting.
You can add a conditional format rule of the type “use a formula to determine which cells to format”. Let’s say the due dates/times are in column D. The CF formula would then be e.g. =$D10<=NOW().
Maddy,
you can send the entire workbook by using Excel’s standard functionality (File | Share | Email).
If you want to send only the items the user checked after opening the workbook, you need additional VBA code. The code would permanently track all user actions to determine which items have been checked and – after the user clicked on the button – create another workbook or a plain text with only newly checked items and send this to a predefined or user defined email address. This is possible, of course, but it requires some VBA development.
Thanks Robert, will check what ever you have said. I get around 1000 emails in my mail box. i need to check the particular 100 mails with subject. i am trying to place all the subject of the 100 mails in the excel and run a macro at the specific times to check all the emails has come or not. or else any other idea to do it through excel?
could you please append it in the checklist excel sheet and give it to me. i am new to VBA. tried but it is showing run time error.
Maddy,
first you need a VBA sub to import emails from Outlook into your Excel workbook. There are dozens of websites out there showing how to implement this. Jimmy Pena, for one, is providing the code snippets and even an example workbook for download:
Export Outlook Emails to Excel
You can take this code pretty much as it is. All you have to change is the target range (i.e. the cells on your worksheet the outlook data shall be written to) and maybe an IF-clause to import only those emails with a particular subject. Alternatively, you could import all your email and use Excel’s Autofilter to display only the relevant emails.
Maddy,
I am always trying to help my readers with problems they may have with my workbooks posted for download. I am also providing smaller enhancements in the comments section from time to time. However, I do not have the time to oblige all individual additional requirements readers may derive from my example workbooks.
Having said that, there are a lot of other websites and blogs out there providing the code how to send emails from Excel. Have a look at Microsoft Excel MVP Ron de Bruin’s Excel Automation site, for instance. Ron provides an excellent collection how to send emails directly from Excel:
Mail from Excel with Outlook
Robert,
This is really good. is it possible to get stamped in a column that which person has stamped using windows authentication? if i open and click the particular row it should show my name. if another person opens and click next row it should show that person name in the next row.
Maddy,
replace the VBA function Now() in the sub “ChangeTopicStatus” of the example workbook posted in my comment above by
– Application.UserName to get the Excel username or
– Environ(“USERNAME”) to get the Windows username
Hi Robert it is not working as you specified
Maddy,
oops. My bad. You also have to replace the function Now() in the Worksheet_BeforeDoubleClick sub. Sorry for the confusion.
Thanks Robert it is working. Tried to get the time stamped and Names in the immediate columns. But the if statement is not taking the values. By default it is taking the last condition and giving the value for Username and not for time stamped. can u advise?
Maddy,
I am sorry, I do not understand what you mean by “immediate columns” and “taking the last condition”. If you just want to have the list user and time stamped, have a look at this template:
Check List Template Time and User Stamped
Robert,
Is it possible to remove or dissable the checkbox that sellects all the rows below an specific section.
Thanks for the help
John,
sure, no sweat. Only two simple steps necessary:
1. Delete the content in the cells with the “checkboxes” for all topics (“£” or “R” or “©”)
2. Go to the VBE, select the sheet module “Sheet 1 (check_list)” and delete the following lines of code:
If blnIsTopic Then
Call ChangeTopicStatus
Else
Call AutomaticSetTopicStatus
End If
After completing the checklist and one wants to record the results, is it possible to save the results and print a report consisting of the options checked in the check boxes.
Carl,
you can print the checklist as it is with Excel’s standard print functionality.
If you want to save the checklist in a final version and avoid future changes, you could save it as an Excel Workbook instead of an Excel Macro-Enabled Workbook (Excel versions 2007 or later) or delete the VBA code (Excel version 2003 and earlier) and the double-click functionality will not be available anymore.
Having said that, it sounds as if you are using the checklist as a form in a survey filled out by different people and you want to consolidate the results in one report, right? In this case, you could either manually copy the check status column of all forms into one report workbook or write a VBA sub to import all results of all workbooks into this report workbook.
Robert,
Thank you for these wonderful checklists. I like the one where you get the time and user stamps. I have a problem though. Would you be able to add a code wherein the user would not be able to place a check on the next items in the list unless the one preceding it has been checked?
I tried adding this code but it does not seem to work.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
If Intersect(Target, Range(“D1:D123”)) Is Nothing And WorksheetFunction.CountA(Range(“D1:123”)) <> 5 Then
Application.EnableEvents = False
MsgBox “You must complete D1:D123”, vbCritical
For i = 1 To 123
If Range(“D” & i) = “” Then
Range(“D” & i).Select
Exit For
End If
Next i
Application.EnableEvents = True
End If
End Sub
I’m trying to add this to the first checklist that you have.
THanks!
Danny
Danny,
have a look at this:
Download Check List Consecutive (80.0K)
This template is only a starting point and has some weaknesses, like
1. it requires all precedent items to be checked already, i.e. all items of all precedent topics, not only the topic the current item belongs to
2. still let the user uncheck any checked item, not only the last one
3. let the user check entire topics although items of precedent topics aren’t checked yet
In a nutshell: it isn’t perfect, but it should point you into the right direction.
It’s clearly and simply amazing tools! your work is very helpful! it really made my night 🙂 thanks a lot.
Robert, This is GREAT!! Thanks you for the ease in making this available. Just a quick question…is there a way to link the check box column with another column to be labled “date completed” so when you double check the box and the check mark comes up then it will automatically fill the column directly to the left of the check mark column “date completed”, make sense?
Thanks again!
Best,
John
John,
thanks for your comment and question. Actually, this question has been asked before and my answer and example workbook are buried in the avalanche of comments on this post. Look out for Bee’s question (June 21, 2013, 12:53). In my reply to Bee, I am providing a download link to a workbook, which should exactly do what you are looking for.
Robert,
This is also GREAT!! I do apologize as I had some issues at first and went through the comments first and figured out the answer before asking you so I guess I missed this one. Thanks again for all of your help it is greatly appreciated!
John
John,
absolutely no need to apologize. This article has 138 comments so far and it is very easy to overlook something in this avalanche of messages. Truth be told, I had to search for several minutes, too, before I found the question of Bee and my example timestamp workbook. So, no worries please.
Robert,
I’ve downloaded the file and opened it in Excel 2003 (SP3). Everything opens up just fine but when I try to double click the check option nothing happens (except selecting the cell).
I will try this file (because this would really be handy for my workplace) at home (Excel 2007 and above).
Thank you for the template and I look forward to the millions of things Excel can do (or should have done in the first place).
Yoh
Yoh,
are you sure you enabled the macros? This is the only issue I can think of. Thousands of people downloaded this file and had no problem with it. I guess your security level is set to “disable all macros without warning” and without enabling macros the whole check list can’t work.
Wow! This template is exactly what I need!! I handle a very large list of documents for which I have several checklist documents, but I’ve been looking for one that had several topics, description and most of all, the double click that shows the completion rate!
I was able to add checklist items within a topic and also additional columns…. but when I tried adding a complete new topic, I copied and changed the numbers and in the formula as well, but the double-click on the check mark didn’t work! 🙁 What am I doing wrong or missing?
Claudia,
did you also expand the named range “myChecklist” to cover the new topic?
Hello!
I thought that I was expanding it, but I just noticed that I was doing it wrong. I saw in previous posts that a Stewart had this same problem, and saw your answer, tried it, and got it!!
Thank you SO much.
May I ask where do I change MyCheckList reference to accommodate additional topic created.
KC,
in the Name Manager of Excel (Formulas|Name Manager or CTRL-F5).
KC,
I just noticed the typo in my comment above:
the shortcut for the Name Manager is CTRL-F3, not F5.
Sorry for the confusion.
Hi Robert,
I am using your checklist and wanted to make the row of items that are not completed yet have a light yellow background fill color and then, when the check box is checked, then, have no fill. Is that easy to do?
Danielle,
yes, that’s easy to do. The template provided above shades off the checked items in a light grey font color if an item is checked. This isn’t done in the VBA code, it is done by simple Conditional Formatting. To get what you want, you have to format the cells in a light yellow fill color and change the existing Conditional Formatting to set the fill color to none, if the item is checked.
I have followed your directions exactly when inserting another topic or checklist but the check box then becomes none interactive. how do I keep the check boxes so when i double click they still fill?
Greta,
if the name “myCheckList” covers the entire check list, the numbering in the first column is correct and the check boxes are in the last column, the checklist should work. It is hard to tell from a distance what’s the problem in your case.
If you want to, you can send me your checklist by email and I will have a look.
How do you adjust the range for “myCheckList?”
Oz,
in the Name Manager of Excel (Formulas|Name Manager or CTRL-F3).
my completion rate stopped working and just stays at 0% even when i check the boxes. How do I fix that?
Greta,
try this: select the cell with the completion rate, press F2 and press Enter to force the UDF to run again. If this does not solve the problem, you can send me your checklist by email and I will have a look.
Hi Robert,
Is it possible to modify the completion calculation if one or more is not applicable. I am trying to make a checklist that will be applicable for machineries, however there is a big chance that each machine will not need all topic to be checked.
Arya,
yes this is possible.
First you need an extra column in the checklist to specify whether the item shall be included in the completion rate (True) or not (False). Let’s say this new column is left to the last column, i.e. the one with the “check boxes”.
In the VBE you have to change one line of code of the function CompletionRate:
Replace
If IsItem(rngCheckList(lngRowCount, 1)) Then
by
If IsItem(rngCheckList(lngRowCount, 1)) And rngCheckList(lngRowCount, lngColumns – 1) Then
This should do the trick.
Hi,
How do I “Enlarge the named formula “myCheckList” to cover the entire checklist” when adding additional topics? I have copied an entire block from above and pasted below but the progress does not show up for the added topics and the checkboxes doesn’t work properly.
Also, is it possible to create subgroups such as 2.1.1? I’m new to all this VBA macro stuff so I don’t know what I am doing yet. Thanks in advance!
Henry,
Question 1:
Open the Name Manager (Formula Tab | Name Manager), select myChecklist, change the cell range in the “Refers To” input box, click Close and confirm with Yes.
Question 2:
This is possible, but it would require some considerable changes in the code, since we would have to distinguish between 3 hierarchy levels instead of only 2. Not impossible, of course, but some VBA coding work necessary.
I will put this on my list of ideas and maybe I will publish an update some later day including the various suggestions posted in the comment section already. No promise, though.
Thanks for your help and quick response. I should have expanded all the comments before posting a question which would have answered my questions.
New question:
You applied “conditional formatting” to change the font color to grey when the checkbox is selected. I converted it back to black, but now the checkbox says “R” instead of the actual checkbox/checkmark. How can I fix that?
Hopefully, I am not asking too much.
Henry,
this is an Excel issue. Have a look at my answer to Suzanne’s comment above (September 11, 2013). The comment describes how to change the Conditional Formatting. If you just want to get rid of it, you can simply delete the CF and it should work right away.
Robert,
Everything works now! Thanks for your help.
I was reading through other people’s suggestions and could not implement Arya’s request of a second column to determine whether a certain task is included in the completion rate (July 22, 2014 – post above my request.)
I changed Public Function CompletionRate to include the modified code but it didn’t do anything. Do I need to compile it for the changes to work?
Henry,
no need to compile anything in VBA. Just go to the cell with the Completion Rate, press F2 to edit the cell and press Enter to force Excel to recalculate the function. This should solve the issue.
Hi Robert,
I wasn’t able to get Arya’s request working. I highlighted the Status column, right click and insert a new column to the left. Then I copied the Status info into the blank column, but I cannot double-click any of the boxes anymore. I also modified the VBA to include the following code:
If IsItem(rngCheckList(lngRowCount, 1)) And rngCheckList(lngRowCount, lngColumns – 1) Then
But that did not update the function. What am I missing? Would it be easier for me to email you my excel file?
Henry,
yes, please send it by email and I will have a look.
Hi Robert,
I am experimenting with the check_list_template_time_and_user_stamped that you made, and was wondering if it would be possible for you to add a button that adds a new section. So if you clicked the button, it would add a section 6 with 10 subsections and so forth. Is this possible?
Thanks for your time,
Erin
Erin,
sure, this would be possible, but it requires some extra VBA coding and I do not have the time to do this. Having said that, it is not that much effort to add extra sections manually.
Hi Robert,
I spent 3/4 of the day working with the speadsheets and never found how to get in and alter code. One of your templates is perfect, just wondering if you could make one modification?
check_list_template_more_sheets_more_completion_rates.xls works awesome. There is a master checkbox for each topic which is great for clearing all checkboxes. Is it possible to have a master checkbox that controls only one column? For example, a master checkbox that controls all checkboxes for Topic 1 Status 1. Then another that does Topic 1 Status 2.
I have a to do list for a certain series of procedural steps to take in Column C. Then status 1, 2, 3 etc are for separate clients I am working for. When I finish with a client I wanted to clear the checkboxes for that client only. TIA
Eric,
have a look here:
Download Check List All per Column
Thanks so much Robert.
I have Excel 2003 with the compatibility pack installed, yet cannot open this xlsm file. Will your changes work if you save it as a xls?
PS. Do you have a donation area on your website that I couldn’t find?
Eric,
the changes work with Excel 2003, too. I will send you the XLS version by email in a minute. No donation area here. Thanks for offering, though.
Robert you are amazing you responded to everyone’s request promptly and accurately. Thank you for all you have done.
Robert,
Thank you for the template. Really appreciate you freely sharing your work openly. God bless
Robert,
Great Blog, I am sure I am being stupid, but, I have your Check list template, with several columns, is there any way that you can change how many status you have, for example check item 1.1 might have 3 checks, item 1.2 might have 4, item 1.3 might only have 1.
I have deleted the ones I dont need but then the completion rate wont work.
Any help would be greatly appreciated.
regards
tim
Tim,
actually it isn’t only the completion rate which does not work if the items have a different number of checkboxes. Checking / unchecking all items of a topic, the change of the status of the topic after checking / unchecking an item and also the shading off of the items if all checkboxes are checked will not work.
The code is assuming that each item has the same number of checkboxes and uses the fixed number of columns with checkboxes for various calculations. Changing the code and the conditional formatting is no rocket science, but unfortunately I do not have the time to do this at the moment. I am sorry.
Robert,
Many thanks for sharing the checklist(s). This is what I was looking for couple of my projects.
Jaydeep
Hi Mr. Robert,
I need to have to column on my checklist (“status” and “NA”). Since my checklist has 10 sections and about 300 items, we would like to allow our users to check the ones that do not apply to their projects. However, the completion percentage on top should calculate combination of both selection (status and NA columns). Would that be possible for you to modify your wonderful template to accommodate for this feature.
Greatly appreciate your help on this very useful template.
Regards,
Saeed
Saeed,
I am not sure I understand your request correctly, but you may want to have a look at the compilation of check lists I recently published in this follow-up post:
Microsoft Excel Check List Compilation
Have a look at check list no 12. I think this could be what you are looking for.
Hello,
I am trying to make a ‘list’ of the things that are check marked from a check list. As of right now, I have a check list set up with true/false. I have tried to make an IF function to bring them into a different document/sheet, but it brings the false ones with them. I only want the true and without the gaps of the false.
Jen,
you have several options to filter a list:
1. The easiest way would be to use Excel’s filter function (HOME tab, Sort&Filter) and filter the list to only show the items with TRUE.
2. You use an array formula which filters data from one list in another list based on a criteria.
Let’s say you have the items of your checklist in range A1:A20 and the TRUE/FALSE values in B1:B2. Array enter (CTRL-SHIFT-ENTER) the following formula in C1 and copy it down to C20:
=IFERROR(INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20,ROW($A$1:$A$20)-ROW($A$1)+1),ROWS($A$1:$A1))),””)
You will then get in column C a list of those items only which have TRUE in column B.
3. Third option would be a small VBA sub to filter the list and write it back to the worksheet.
Thank you so much Robert! This just made my week. I have been trying to get something to work and have been internet searching for days. Thank you again!
Hi,
Thank you for the checklist. I started with the basic checklist on the first page. Then I wanted to expand and collapse all topics so I copied and pasted the expand/collapse from the above download. the button works great. However when I close my original checklist the above checklist pops up and I must save or don’t save then I can close it. Is there a way to fix this?
Yvonne,
I am sorry, I don’t get it. What do you mean by “the above checklist pops up”? What exactly did you copy from the workbook posted above? The entire code or just the command button? Can you send me your workbook by email?
Hi Robert! Your Ceck list tool has been absolutely fantastic and I have been trying to incorporate another coloumn to this checklist, just before the check boxes, with email reminders. However, they dont seem to work. Could you help me with this?
Deepthi,
if you are inserting columns left to the last column (the checkboxes), it should work right away without any further changes. However, please be advised that the code always overwrites the entire content of the checklist with the values. If you have formulas inside the checklist, they will be overwritten every time the code runs.
Having said that: it sounds as if you want to automatically send email reminders to email addresses specified in a column inside the checklist. This is possible, but it goes far beyond the very simple code for this checklist and would require quite some extra coding.
Hi Robert,
Thank you so much for the quick response! Yes, I have been trying to code for the excel sheet as such and have been able to figure it out as per the checklist range. Would it be possible to set it for the the specified range as such? Or is it possible that the code could be made flexible so that I can insert the code that I have for email reminders(naming rows & coloumns instead of named ranges)
Thank you so much once again for all the help!
Deepthi,
I am sorry, I do not understand what you mean by “set it for the specified range as such”. The named range myCheckList covers the entire checklist and is used in the VBA code as a reference to the range on the worksheet in order to check if the current row is a topic or item, to check the current status and to set the new status depending on where the user double clicked.
You can change the code to refer to specified rows and columns instead of the named range, but why would you want to do this?
If you want to include extra code for sending email notifications, you can simply add this to the existing code. Let’s say you want to send an email notification to someone as soon as an item is checked: you would insert this new code right after the checklist code sets the status of this item to done.
Thanks Robert, but I intend to send an email reminder for the activity to be completed. As you had suggested I had tried putting the deadlines/ due dates after the checkbox columns, andset email reminders for a day before the due date (via vba) however, this does not seem to work for your checklist. Could you please let me know if there is something that i should be modifying in your code so that the email reminder module works too?
Deepthi,
with the existing code you can’t have anything right to the checkbox column. The code expects to find the checkboxes in the last column of the list.
If your problem is the fact that the checklist overwrites formulas, you can overcome this by replacing the following line
Range(“myCheckList”) = varData
by something like this:
Dim i As Integer
For i = 1 To UBound(varData, 2)
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.
But again: the checkboxes have to be in the last column of the list.
Hi Robert, I modified the code as you had suggested. But my macros for email reminders do not seem to function. Could I mail it to you so that you can have a look?
Sub mailMe()
Dim myApp As Outlook.Application, mymail As Outlook.MailItem
Dim mydate As Date
Dim lr As Long
Dim i As Integer
lr = Sheets(“Checklist_A”).Cells(Rows.Count, 1).End(xlUp).Row
For i = 13 To lr
mydate = Cells(i, 5).Value
If mydate – Date = 1 Then
Set myApp = New Outlook.Application
Set mymail = myApp.CreateItem(olMailItem)
mymail.To = Cells(6, 4).Value
With mymail
.Subject = “AdBoard Checklist Reminder”
.Body = “Deadline for an activity” & ” – ” & Cells(i, 2) & “: ” & Cells(i, 3) & vbCrLf & “Kindly complete the activity by ” & Cells(x, 6) & “. ” & “Ignore if already completed.” & vbCrLf & “XXX”
‘.Display
.Send
End With
End If
Next
Set myApp = Nothing
Set mymail = Nothing
End Sub
This is the code that I wanted to implement
Deepthi,
sure, you will find an email me link at the top of the blog.
Robert,
Hope I was in clear in my email.. though I guess the the last line I meant was the Body of the message & not the subject. 🙂
Thank you so much Robert.. for the modified excel sheet with reminders. That is exactly what I wanted! 🙂 🙂 🙂
Thanks Robert for this checklist. It has been so helpful to me where I work with software quality assurance. I have one question though. I was looking through your macro to see how you changed the color of the completion rate. I could not find how that completion box turned green when it hit 100%. I would like it to turn green at 90%. Can you offer me any tips? Thanks!
Casper,
the cell coloring of the completion rate is done by simple Conditional Formatting with hard coded values of 75% and 100%. No VBA involved.
Simply change the rules of the Conditional Formatting as you like.
Robert,
This checklist template rocks. I am rather novice with Excel, and am trying as a summary log for internal audits. However, I am having a problem with adding new topics as I need about 20. I extended the MyChecklist name range for number of rows, I needed, and was able to cut/paste a new topic, rename, etc. But the new topic I copied seems to be dependent upon the one I copied from. If I check off the old topic section, it checks off the new one derived from the cut/paste. Also, if I collapse the new topic, it collapses the other and so forth. Is there a special way to cut and paste the topics to prevent this?
Brandon,
sounds as if you did not change the numbering in the first column. Please have a look at the section “Important” just above the download link. Each topic needs its own unique number, each item of this topic has the topic number followed by a period, followed by the item number. If you just copy one entire topic and do not change the numbering, the code will collapse all topics with this topic number. Just make sure to follow the convention and to have unique topic numbers.
Hola! I really appreciate your work here! I am a begginer and I am trying to add more than 5 list points, but when I copy and paste the “formulas” are not working :-(. The same when I just want to add the user is not applying…
Rocio,
you have to expand the named range “myChecklist” to cover all topics and items of your checklist. Go to the Name Manager (FORMULAS tab), select myChecklist and expand the “Refers To” range. Furthermore, you have to take care of the “naming convention” in the first column. The items are “numbered” by the topic number followed by a period followed by the number of the item. See the section “How to use this template for your own checklists” of the article.
As per your last sentence: I am sorry, I do not understand what you mean by “to add the user is not applying”.
Hi this was very helpful.. could you also help me with same form but I want to print only the checked boxes (don’t need the unchecked responses)
Also if I change the conditional formatting in col E to highlight the entire row if checked.. the checkbox turns into the letter R.. how do I retain the checkbox.
Thanks
Hitika,
the checkboxes are no “real” checkboxes. The cells contain the letter “R” or “£” and are formatted with font type Wingdings2. With this font type an “R” looks like a checked box and “£” looks like an unchecked box. If you are changing the Conditional Formatting of the columns with the “checkboxes”, you have to make sure the Conditional Formatting rule formats the cell with font type Wingdings2 (instead of e.g. Automatic).
If you want to print only the rows with checked boxes, you would need an extra VBA sub. This sub would loop through the list, hide all rows with unchecked boxes, print the sheet and then unhide the rows again.
i WANT TO INSERT A CROSS MARK IN THE CHECK BOX TO INDICATE “NOT APPLICABLE” AND STILL GET IT CALCULATED TOWARDS THE COMPLETION RATE. ANY SOLUTION?
Asha,
have a look at the workbook #2 in the follow-up post mentioned at the end of the post above. This workbook excludes the n/a checked items from the calculation of the completion rate.
If you want to change this, you have to adjust the function CompletionRate. The function has 2 nested IF clauses within the For Next loop.
Delete
And rngCheckList(lngRowCount, lngColumns) <> C_NA
from the condition of the first (outer) IF
Add
Or rngCheckList(lngRowCount, lngColumns) = C_NA
to the condition of the second (inner) IF.
This should do the trick.
No offense, but I would also recommend having a look here: http://www.netlingo.com/word/shouting.php
Hi!
Your templates are very helpful!
I like this one however, can you help me as i would also need subtopics for a certain topic, say 1.1.1 wherein there’s a checkbox and i don’t need to have a checkbox for Topic 1.1.
Can this be incorporated in Check List Template Time and User Stamped checklist?
Thanks a lot!
Monica,
you can simply delete the checkboxes of the items in level 1.1 etc., change the numbering in the first column for your items as needed (1.1.1., 1.1.2) and the check list will still work.
What you have to change is the code of the user defined function CompletionRate and the code of the sub ChangeTopicStatus, which will insert the checkbox again for all items of the topic (i.e. level 2 and 3 items). You would have to adjust the code to check if an item is a level 3 item (e.g. 1.1.1) and insert the checkboxes only for these items and skip the level 2 items (e.g. 1.1).
Hi Robert,
Just awesome work dear.
Question though, is there a way to change the box to a certain color when ticked? I’m thinking of traffic light system if you know what I mean.
Thanks in advance for your advice.
Omar
Omar,
you can do this using Conditional Formatting. The template posted above does this already by shading off the entire row of a checked item with a grey font color. You could add another Conditional Formatting rule changing the fill color to e.g. green when the box is checked (i.e. the cell value is R).
Hello team,
Everytime I add an extra topic along with check item and add at the last. I tried to cover within the completion rate formula but it changes the topic to 2 and items to 1.11, 1.12 and doesnt allow to include in the percentage formula
Please help.
Thanks
Sumit.
Sumit,
the code imports all data of the checklist into a VBA array and writes the entire list back to the sheet. The code doesn’t change the numbering. If the numbering in the first columns changes, I would assume this is caused by the number format of the cell. If you insert a text like “1.1”, Excel tries to convert this into a date. Maybe this is the root cause of your problem. To avoid this, simply format the first column as Text and it should work.
Having said that, I do not see how this could be caused by the completion rate, because the completion rate UDF does not change anything on the sheet (except for the result of the completion rate).
Hello,
I am wondering how you save the checklist as a webpage and keep the functionality of the checkboxes and dropdown lists??
Unfortunately, you can’t. The functionality of double clicking to check and uncheck the checkboxes is implemented in Visual Basic for Applications, the programming language coming with Microsoft Office. I do not see a way to transfer this VBA code to a webpage.
You can certainly create a similar functionality on a webpage using HTML, but you can’t use the code provided in the workbook above.
ok, thanks
I tried to install this on my iPad but it’s Read-Only.
Anything I’m doing wrong?
Timothy,
I do not have an iPad (I am using a Microsoft Surface), so I can’t tell you for sure. My wild guess would be that the Office apps for iPad and iPhone may not support VBA (or only in specific versions). I never owned a Mac or an iPad, so I am sorry, but I can’t help you here.
Hi. I am a primitive user of Excel. When searching for Excel Checklist teplates in google I inevitably came upon yours. From the description it looks really great but I am afraid I have no idea about how to “load?” the files in an Excel sheet? (due to my complete lack of knowledge on the subject). No idea if this is somehing anyone could easily do. If it happened to be simple I was wondering if anyone would share some brief indications about where to start. Many thanks!
Luis,
since version 2007, Microsoft Excel files are actually zipped folders including XML and other files. If your Internet browser opens Windows Explorer when clicking on the download link, right click on the link instead and select “Save Target As” to download. If you are using Microsoft’s Internet Explorer, the IE will change the file extension from .xlsm to .zip during download. Simply change the file extension back to .xlsm and you can open the workbook with Excel by double clicking on the file.
A huge thanks Robert! I was trying to make sense of the files inside the .zip and was completely lost. I have started to enjoy your tenplates already. Thanks a lot!
Hello Robert,
how to add new argument to checkList?
Thank you.
Giuseppe,
please see the section “How to use this template for your own checklists” at the end of the article.
Great tool. In the checkbox area I want to have another checkbox. However, once I add a developer checkbox, how can I link it to the completion and the table itself?
I also tried creating another column and paste column E to it and adjusted the name manager to include that column but after doing that, column E became useless. Need help thanks.
zav,
have a look at this follow up post:
Microsoft Excel Check List Compilation
The article provides 14 variations of the basic checklist, including templates with more than one check column.
Awesome post, Robert! Having the right checklist that you can not just conveniently use but also love and connect to is important in order for us to become even more productive. Having all the necessary steps incorporated into your template also makes you an efficient worker.
Your readers can also check out this article: https://www.process.st/checklist-template-word/. It also has a simple and easy-to-edit Microsoft Word Template, plus a Process management tool that they might find very useful for their marketing processes.
Robert,
I’m looking to combine this checklist i.e. collapse only checked topics with the checklist#06.
once all the topics are checked, it collapse auto.
Thanks!
Ahmed,
all checklist versions posted in the comments and in the article with the checklist compilation are more or less slight variations of the code provided in the posted above, i.e. they are all working along the same basic idea and only a few lines of code are changed or new (compared to the original version).
If you want to have the functionality of two or more of the different versions in one workbook, you have to identify the differences in the code and combine the codes in one workbook. All workbooks and codes are open, i.e. without password protection.