Excel Final Touchups - If-Then Checkboxes - Odd lines

Where are you trying to add the check boxes (or do you mean the boxes in columns AN:AP)? You want a comment when the box is checked on. What would you like to happen if the box is checked off? If I could figure out a way to "deactivate" the check box after it is checked so it cannot be unchecked would you like this?

It looks like part of the solution is to revise the code for the target area that causes the macro to run.

You are probably correct that the conditional formatting is clobbering the dotted dividers in the cell, but if you insist on using one of the "canned" formats, that's what you will get. I don't think there is a fix for this.

As for the dashed lines, I'll have to do some experimentation. If it is possible, it will be a custom solution.

I will work the other issues and when I get it working, I'll explain about the target area.

I have another observation. The data, as you have it laid out is fixed. How do you plan to add / delete employees? So I am making three suggestions.
1. Move the totals row to the top of the spreadsheet - this way no matter how many lines of data you have, they won't overwrite or "push" the total line. The data will be "free" to extend as far down as it has to go.
2. Change the data to an Excel Table instead of just a range. Excel tables have many advantages, but the big two here are:
- a. Tables know how big they are. So any formulas that reference them will grow or contract with the data.
- b. Tables automatically copy down formulas, formats, data validations, etc. as new data is added.
3. The correct way to make a new month is to copy the sheet and rename it - this assures that the code goes along with the sheet.

This should get you started with tables. Once you try them, you'll get hooked. http://www.utteraccess.com/wiki/Tables_in_Excel.

Glad you worked that out.

Here is the prototype. Obviously, it doesn't have your new conditional formatting, so you will have to apply it.

I dropped the checkboxes. They are exceptionally difficult to work with. First of all, they do not "reproduce" so if you add new people, you have to add new check boxes. Secondly, there are two ways to work with checkboxes: (1) you use their name or (2) you use the cell they are linked to. The second method is somewhat doable, but you would have to link each checkbox to a cell in the table.

I replaced the checkboxes with simple drop-down Yes/No

I moved the totals to the top and I also added a row to enter the date and year. These are used in conjunction with the dates in row 4. By knowing the the month and year and day, I can calculate weekends and use conditional formatting to show them.

So, here is how you can use the sheet.
1. Make a copy of the template sheet and rename it.
2. Enter the month name in cell A1 and year in Cell B1
3. Hide rows for dates that don't exist, like February 29. 30 and 31.

Clone a sheet and make a month, or play with February. Delete February and re-create it when you are ready for the "real" February.

You can hide the template sheet after you've cloned all your months.

In answer to another question you may have: it will take some VBA comb through the cells and pick out the comments for a report.

Oh yes, I forgot to tell you about the range.

I defined two named ranges:
Date_Range =OFFSET(Template!$G$5,0,0,COUNTA(Template!$A:$A)-3,31)
Warning_Range =OFFSET(Template!$AL$5,0,0,COUNTA(Template!$A:$A)-3,3)

This wiki explains named ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges but essentially the date range is columns G:AK, Row 5 down to the end and the warning range is columns AL:AN from row 5 down to the end.

Then there is the change event code on the template sheet:

Please Login or Register  to view this content.

If you try to change more than one cell at at time (such as a mass copy and paste), the code will fail. This line of code says, if you are changing more than one cell, exit the subroutine, don't try to process the code. You can still do the copy/paste, but nothing else will happen.

Then we have two sections of code: one to deal with the attendance and the other to deal with the warnings.

Please Login or Register  to view this content.

This code says, look at the target (the cell whose contents you just changed). If it is in the range Date_Range, then continue with the code. The intersect command gives the intersection of two or more ranges. If the ranges don't touch each other (such as the changed cell is outside the specified range) then their intersection is nothing. The NOT negates this meaning that do the code if the target is in the range.

The second block of code does the same thing except for the warning columns.