Reset parts of worksheet automatically, but not others

[MDCK]

First, ignore the colored portions. This is how I kept track of what I had completed in the form as I created it. Clients attend class once a week and would be marked present on the corresponding day of class. This increases column #3 "classes attended" to "1". What I would like is for my individual classes sheet to reset every Monday (as the date of the report on top) but for the "5groups" worksheet to keep count of the classes attended. Does that make sense? Please, any imput would REALLY be appreciated.

[Leith Ross]

Hello MDCK,

This was not as difficult as I first thought. What I did was create five named ranges "Monday0930am", "Monday0100pm", "Tuesday0600pm", "Thursday0600pm", and "Saturday0100am". Each range encompasses all the columns and row in the block for that day. The CheckBoxes on each worksheet call the same macro. The macro takes two arguments: the name of named range to search for Client in, and the name of the CheckBox. The attached workbook contains all these changes and has been tested.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[MDCK]

Thank you very much. I have encountered a couple of problems when I tested it. On the Monday930 sheet, when I checked the box for Client2, I received an error message stating "Compile error: Argument not optional" and highlighted was the section "Private Sub CheckBox2_Click()". When I went back to my 5groups sheet, the classes attended number did not increase for this client. I place check marks in all of the other boxes and none increased as they did before. When I changed my system date to a week ahead, closed the program and reopened it (to get the new report date) my individual classes did not reset the boxes to be unchecked. I really wish I understood macros so I wouldn't have to pester others with my projects. In my original sheet, when the boxes were checked the classes attended increased, but I couln't figure out how to get those "attendance sheets" to reset so I can use them again next week. With us being a non-profit organization, we lack the funds to hire someone to do this for us, therefore leaving me awake until 2am trying to figure this out and eventually asking you guys for help when I just can't do it. Did you encounter these issues?

[Leith Ross]

Hello MDCK,

The last change I made didn't save properly. It has been fixed.

Sincerely,
Leith Ross

[MDCK]

I tried it again and now I am not receiving the error message. When I change the system date and reopen the file, my attendance sheets are not reseting to being unchecked and if I uncheck them manually, it removes it from the classes attended tally. Any input will and has been greatly appreciated.

[Leith Ross]

Hello MDCK,

I set the macro to either add or subtract 1 from the class attendance count. If he box is checked the count is incremented by 1, and if unchecked decrement by 1, unless the attendance is zero. I missed the part about the date originally. What is supposed to happen with the date change?

Sincerely,
Leith Ross

[MDCK]

I had the sheet to already add 1 to the classes attended when the box was checked. What I wish the form would do is "Every Monday all attendance sheets reset to unchecked, and on the 5 groups sheet the number of classes remains" meaning that I can go back one month and see that one client attended 3 classes so far and that another attended 4. That way, I can see how many classes they have left. Like I said, my original sheet can already add +1 to the classes attended column, I just need that number to "stick" and the attendance sheets to reset to all boxes unchecked so that my instructors can mark who is present for that weeks class. The date of reset is the date reflected on the top, which I have set to be "every Monday". I hope I am doing better at explaining myself. What do you think? Can this be done?

[Leith Ross]

Hello MDCK,

I think it is possible. I will need to make some changes to the workbook macro and I'll post it for your review.

Sincerely,
Leith Ross

[MDCK]

Leith,

You are an angel, thank you.

[MDCK]

I also changed the title of my thread since it has not been solved yet.

[Leith Ross]

Hello MDCK,

I have made the changes. The old macro will now just increment the attendance count when checked. However, If you check, uncheck, and check the box again then the attendance will be incremented by 1 again. When the workbook is opened, the new macro compares the today's date with the date the last time the workbook was modified. If today is Monday and the last day the workbook was modified isn't Monday then the check boxes are reset.

Sincerely,
Leith Ross

[MDCK]

Oh this is almost perfect!! It does exactly what I want it to do in the end. On Monday 930 class, the checkboxes are not adding in my 5groups sheet and it did before. I couldn't get the sheets to reset (meaning the checkboxes) but should I just add to what you have a "reset" button? Please let me know your thoughts. Thank you!

[MDCK]

I just noticed that it works ONLY where "Client" is typed. How can I make all the boxes act the same? Meaning I will have listed about 13 to 14 different names per class. I only listed those right now because I was doing counts and such.

[Leith Ross]

Hello MDCK,

Please post your current workbook, and I will review it and make the needed changes.

Sincerely,
Leith Ross

[MDCK]

I used the same one you worked on and posted for me. All I did was add a name where it was blank.

[Leith Ross]

Hello MDCK,

I will use the version I posted previously and make the needed changes to that one.

Sincerely,
Leith Ross

[Leith Ross]

Hello MDCK,

I am a little confused by what you want. A few posts back you talk about the number of classes being tracked...

What I wish the form would do is "Every Monday all attendance sheets reset to unchecked, and on the 5 groups sheet the number of classes remains" meaning that I can go back one month and see that one client attended 3 classes so far and that another attended 4. That way, I can see how many classes they have left.

You changed the "5 Groups" sheet to track classes attended and classes remaining. Can you clarify what I should do?

Sincerely,
Leith Ross

[MDCK]

Ok I found the issue. It works perfectly. The problem was that a name is required for the check box addition to work! I'm having trouble figuring out the resetting. I keep changing my system date to mimic what would happen and I can't get it to reset. I'm still trying though. If I can't get it to work, can I just use reset button without messing up your work? What I ultimately wish is that the attendance sheet checkboxes clear so I can use them again next week. I think (please correct me if I'm wrong) that the easiest way is for a simple 'reset' button on each attendance sheet that the intructor would click on at the beginning of next weeks class. Am I making sense?

[MDCK]

Ok I did it!!!! I'm so proud of myself. I added a reset button to the form that clears the checkboxes and it doesn't mess with my totals. Here it is for you to look at to make sure I didn't mess with your macros. My only concern is that I don't know how to change your macro so that the line that indicates a reset does not occur on Monday anymore. Please let me know what I need to do to mark this solved. Thank you so very much.

[MDCK]

Ok, continuing on with my exploration. I opened the project explorer and removed the reset_Monday_macro. Was this the right thing to do? It all seems to be working fine so far, but just in case I saved this under a new file. Once I confirm that my changes are A-OK, I can mark this solved!!!!

[Leith Ross]

Hello MDCK,

Nice work on the Reset buttons! The Monday reset macro simply checks if today's date is a Monday and if the day the file was last modified isn't Monday. If true, the form is reset. If you remove the macro, you will have to accomplish this some other way, provided you still want to do this.

Sincerely,
Leith Ross