Reset parts of worksheet automatically, but not others

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.

Sub UpdateClassCount(ByVal Rng_Name As String, ByVal ChkBox_Name As String)

  Dim ChkBox As Object
  Dim ClassCount As Range
  Dim R As Long
  Dim Rng As Range
    
    Set Rng = Range(Rng_Name)
    Set ChkBox = ActiveSheet.OLEObjects(ChkBox_Name)
    R = ChkBox.TopLeftCell.Row
    Client = Cells(R, 1)
    
      Set ClassCount = Rng.Find(What:=Client, _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
      If ClassCount Is Nothing Then Exit Sub
      
      If ClassCount > 0 Then
        C = ClassCount.Offset(0, 2).Value
          If ChkBox.Object = True Then
            C = C + 1
          Else
            C = C - 1
          End If
        ClassCount.Offset(0, 2) = C
      End If
       
End Sub

Sincerely,
Leith Ross

Hello MDCK,

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

Sincerely,
Leith Ross

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.

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

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?

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.

Hello MDCK,

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

Sincerely,
Leith Ross

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

Hello MDCK,

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

Sincerely,
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

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.