Reset parts of worksheet automatically, but not others

**Leith Ross** · 12-02-2008, 01:36 AM

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