Master/Slave checkboxes

Hi,

I am looking for some help setting up groups of master/slave checkboxes on a worksheet.

I would like the Master checkbox to control the checked/unchecked state of the slave checkboxes. If the master checkbox is unchecked, then the slave checkboxes should be able to be checked/unchecked independently.

I have five master checkboxes named Master1, Master2, Master3, etc.

The slave checkboxes follow the naming convention Master1_Child1, Master1_Child2, Master2_Child1, Master2_Child2, etc.

There are 3 slave checkboxes that I want to associate with the Master1 checkbox, 5 checkboxes for Master2, 4 for Master3, 15 for Master4 and 23 for Master5.

Can anyone help with the necessary code?

Thanks!

Hi ko6ux and welcome to ExcelForum,

Excellent question. You didn't specify what type of CheckBoxes, so I am assuming the CheckBoxes are 'Active X' CheckBoxes on the WorkSheet. If your CheckBoxes are 'Forms CheckBoxes', then the code will have to be modified. If you need more information about the difference, see the following thread: http://www.excelforum.com/excel-prog...ml#post3880991

See the attached sample file, which uses one ClassEvent to handle all CheckBox clicks (Thank you Andy Pope). When the Master CheckBox is On, I had no way to determine the state of the other CheckBoxes. I added a Subordinate Master CheckBox for each Master CheckBox that determines the On/Off State of the other CheckBoxes.

'ThisWorkBook' Module Code (for initialization):

Option Explicit

Private Sub Workbook_Open()

  'Put the focus on 'Sheet1'
  Sheets("Sheet1").Select
  
  'Initialize the CheckBox States
  Call InitializeCheckBoxStates
  
  'Enable CheckBox Events
  Call EnableCheckBoxEvents

End Sub

Class Module 'ClassCheckBoxEvent' code:

Option Explicit

Public WithEvents myCheckBox As MSForms.CheckBox

Private Sub myCheckBox_Click()
  'This is the Active X Event Handler
  
  Dim sName As String
  Dim bValue As Boolean
  
  sName = Me.myCheckBox.Name
  bValue = Me.myCheckBox.Value
  
  Call ProcessCheckBoxEvent(sName, bValue)
  
  'MsgBox "Clicked " & Me.myCheckBox.Name & " " & Me.myCheckBox.Caption & "  " & Me.myCheckBox.Value
End Sub

Ordinary Code module code 'ModCheckBoxes':

Option Explicit

Private Const sControlTypeDESCRIPTION As String = "Active X CheckBox"
Private Const sControlType As String = "CheckBox"
Public myCheckBoxEvents As Collection

Sub ProcessCheckBoxEvent(sInputName As String, bValue As Boolean)

  Const nCheckBoxTypeOTHER = 0
  Const nCheckBoxTypeMASTER = 1
  Const nCheckBoxTypeMasterVALUE = 2
  Const nCheckBoxTypeCHILD = 3
  
  Dim i As Long
  Dim iCheckBoxType As Long
  Dim iChildIndex As Long
  Dim iMasterIndex As Long
  Dim iMaxChildCount As Long
  Dim iPos As Long
  
  Dim bEnable As Boolean
  Dim bMasterValue As Boolean
  Dim bMasterValueValue As Boolean
  
  Dim sName As String
  
  'Make a local copy of the Input Name
  sName = sInputName

  Debug.Print sName, bValue

  '''''''''''''''''''''''''''''''''''''''''''''''''''
  'Determine the Type of CheckBox Selected
  '''''''''''''''''''''''''''''''''''''''''''''''''''
  If Left(sName, Len("Master")) = "Master" Then
  
    'Strip the text 'Master' from the name
    sName = Replace(sName, "Master", "")
    
    'Get the 'Master' Index Number from the next Character (convert the string to long integer)
    iMasterIndex = CLng(Left(sName, 1))
    
    'Get the position in the string of the text 'Child' if it exists
    iPos = InStr(sName, "Child")
  
  
    If Right(sName, 1) = "V" Then
      'If the 'Last Character' in the name is 'V', then it is a 'Master Value' CheckBox
      iCheckBoxType = nCheckBoxTypeMasterVALUE
      bMasterValueValue = bValue
      
    ElseIf iPos > 0 Then
      'If the name contains 'Child', then it is a 'Child' CheckBox
      iCheckBoxType = nCheckBoxTypeCHILD
      
      'Get the Child Index
      iChildIndex = CLng(Mid(sName, iPos + Len("Child"), 9999))
    Else
      'Otherwise, it is a 'Master' CheckBox
      iCheckBoxType = nCheckBoxTypeMASTER
      bMasterValue = bValue
    End If
  
  
  Else
    'If the name DOES NOT contain 'Master' it is an OTHER CheckBox - should never occur
    iCheckBoxType = nCheckBoxTypeOTHER
  End If


  '''''''''''''''''''''''''''''''''''''''''''''''''''
  'Process the CheckBox Selected
  '''''''''''''''''''''''''''''''''''''''''''''''''''
  Select Case iCheckBoxType
  
    Case nCheckBoxTypeMASTER
    
      'Get the number of 'Child' CheckBoxes for the given 'Master' CheckBox
      iMaxChildCount = GetMaxChildCheckBoxCount(iMasterIndex)
    
      'Get the value of the 'Master' Value Checkbox
      sName = sInputName & "V"
      bMasterValueValue = ActiveSheet.OLEObjects(sName).Object.Value
    
      'If the Master is 'True' Then all values move in Unison
      'If the Master is True, then the 'Master Value' CheckBox is visible
      'If the Master is False, then the 'Master Value' CheckBox is not
      If bValue = True Then
        bEnable = False
        ActiveSheet.OLEObjects(sName).Visible = True
      Else
        bEnable = True
        ActiveSheet.OLEObjects(sName).Visible = False
      End If
      
      'If the Master is True, then the 'Child' CheckBoxes are Disabled
      'If the Master is False, then the 'Child' CheckBoxes are Enabled
      For i = 1 To iMaxChildCount
        sName = "Master" & iMasterIndex & "_Child" & i
        ActiveSheet.OLEObjects(sName).Enabled = True
        ActiveSheet.OLEObjects(sName).Object.Value = bMasterValueValue
        ActiveSheet.OLEObjects(sName).Enabled = bEnable
        Debug.Print sName, ActiveSheet.OLEObjects(sName).Enabled
      Next i
    
    Case nCheckBoxTypeMasterVALUE
    
      'Get the number of 'Child' CheckBoxes for the given 'Master' CheckBox
      iMaxChildCount = GetMaxChildCheckBoxCount(iMasterIndex)
    
      
      'If the Master Value Value is True , then the 'Child' CheckBoxes are all 'True'
      'If the Master Value Value is False, then the 'Child' CheckBoxes are all 'False'
      For i = 1 To iMaxChildCount
        sName = "Master" & iMasterIndex & "_Child" & i
        ActiveSheet.OLEObjects(sName).Enabled = True
        ActiveSheet.OLEObjects(sName).Object.Value = bValue
        ActiveSheet.OLEObjects(sName).Enabled = bEnable
        Debug.Print sName, ActiveSheet.OLEObjects(sName).Enabled
      Next i
    
    Case nCheckBoxTypeCHILD
      'Do nothing at this time
      Debug.Print "Child CheckBox Clicked: Master = " & iMasterIndex & "   Child = " & iChildIndex
    
  End Select
'GetMaxChildCheckBoxCount
End Sub

Sub InitializeCheckBoxStates()
  'This sets the states of the 'Active X' CheckBoxes

  Dim i As Long
  Dim iMasterIndex As Long
  Dim iMaxChildCount As Long
  
  Dim bEnable As Boolean
  Dim bValue As Boolean
  Dim bMasterValue As Boolean
  Dim bMasterValueValue As Boolean
  
  Dim sName As String
  
  'Process Each Master Index
  For iMasterIndex = 1 To 5
  
    'Get the number of 'Child' CheckBoxes for the given 'Master' CheckBox
    iMaxChildCount = GetMaxChildCheckBoxCount(iMasterIndex)
    
    'Get the Values
    sName = "Master" & iMasterIndex
    bMasterValue = ActiveSheet.OLEObjects(sName).Object.Value
  
    'Get the value of the 'Master' Value Checkbox
    sName = sName & "V"
    bMasterValueValue = ActiveSheet.OLEObjects(sName).Object.Value

    'If the Master is 'True' Then all values move in Unison
    'If the Master is True, then the 'Master Value' CheckBox is visible
    'If the Master is False, then the 'Master Value' CheckBox is not
    If bMasterValue = True Then
      bEnable = False
      ActiveSheet.OLEObjects(sName).Visible = True
      If bMasterValueValue = True Then
        bValue = True
      Else
        bValue = False
      End If
    Else
      bEnable = True
      ActiveSheet.OLEObjects(sName).Visible = False
    End If

    'Enable or disable the 'Child' CheckBoxes based on the Value of the 'Master' CheckBox and the 'Master Value' CheckBox
    For i = 1 To iMaxChildCount
      sName = "Master" & iMasterIndex & "_Child" & i
      If bMasterValue = True Then
        ActiveSheet.OLEObjects(sName).Enabled = True
        ActiveSheet.OLEObjects(sName).Object.Value = bValue
      End If
      ActiveSheet.OLEObjects(sName).Enabled = bEnable
      'Debug.Print sName, ActiveSheet.OLEObjects(sName).Enabled
    Next i

  Next iMasterIndex
  
End Sub

Sub CreateMasterChildCheckBoxes()
  'This Creates 'Active X' Controls

  Const xCheckBoxHeight = 12
  Const xCheckBoxWidth = 12
  
  Dim r As Range
  Dim TempButt As OLEObject
  
  Dim iColumnOffset As Long
  Dim iCount As Long
  Dim iChildIndex As Long
  Dim iMasterIndex As Long
  Dim iMaxChildCount As Long
  Dim iRowOffset As Long
    
  Dim xHeight As Double
  Dim xLeft As Double
  Dim xTop As Double
  Dim xWidth As Double
  
  '''''''''''''''''''''''''''''''''''''''''''''
  'Create CheckBoxes
  '''''''''''''''''''''''''''''''''''''''''''''
  'First Control will be at the 'Top Left' of the following cell
  Set r = ActiveSheet.Range("B19")
  
  For iMasterIndex = 1 To 5
  
    iRowOffset = 0
    iColumnOffset = iMasterIndex - 1
    Set r = ActiveSheet.Range("B19").Offset(iRowOffset, iColumnOffset)
  
    '''''''''''''''''''''''''''''''''''''''''''''
    'Create Master Control CheckBox
    '''''''''''''''''''''''''''''''''''''''''''''
    
    'Get the dimensions of the active cell
    xHeight = r.Height
    xLeft = r.Left
    xTop = r.Top
    xWidth = r.Width

    'Calculate starting point for the new 'Active X Check Box'
    xLeft = xLeft + (xWidth - xCheckBoxWidth) / 2#
    xTop = xTop + (xHeight - xCheckBoxHeight) / 2#

  
    'Create the Active X CheckBox
    Set TempButt = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
                                              Link:=False, _
                                              DisplayAsIcon:=False, _
                                              Left:=xLeft, _
                                              Top:=xTop, _
                                              Width:=xWidth, _
                                              Height:=xHeight)
                                              
    'Create the 'Caption' for each CheckBox
    TempButt.Object.Caption = ""
    
    'Rename the CheckBox to 'MasterX'
    TempButt.Name = "Master" & iMasterIndex
        
    'Increment the Control Created Count
    iCount = iCount + 1
    
    
    '''''''''''''''''''''''''''''''''''''''''''''
    'Create Master Value CheckBox
    '''''''''''''''''''''''''''''''''''''''''''''
    
    iRowOffset = 2
    Set r = ActiveSheet.Range("B19").Offset(iRowOffset, iColumnOffset)
    
    'Get the dimensions of the active cell
    xHeight = r.Height
    xLeft = r.Left
    xTop = r.Top
    xWidth = r.Width

    'Calculate starting point for the new 'Active X Check Box'
    xLeft = xLeft + (xWidth - xCheckBoxWidth) / 2#
    xTop = xTop + (xHeight - xCheckBoxHeight) / 2#

  
    'Create the Active X CheckBox
    Set TempButt = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
                                              Link:=False, _
                                              DisplayAsIcon:=False, _
                                              Left:=xLeft, _
                                              Top:=xTop, _
                                              Width:=xWidth, _
                                              Height:=xHeight)
                                              
    'Create the 'Caption' for each CheckBox
    TempButt.Object.Caption = ""
    
    'Rename the CheckBox to 'MasterX'
    TempButt.Name = "Master" & iMasterIndex & "V"
        
    'Increment the Control Created Count
    iCount = iCount + 1
    
    
    '''''''''''''''''''''''''''''''''''''''''''''
    'Create Child CheckBoxes
    '''''''''''''''''''''''''''''''''''''''''''''
    
    'Get the number of 'Child' CheckBoxes for the given 'Master' CheckBox
    iMaxChildCount = GetMaxChildCheckBoxCount(iMasterIndex)
    
    'Add one to the offset to account for the unused line
    iRowOffset = iRowOffset + 1
    
    For iChildIndex = 1 To iMaxChildCount
    
      iRowOffset = iRowOffset + 1
      Set r = ActiveSheet.Range("B19").Offset(iRowOffset, iColumnOffset)
    
      'Get the dimensions of the active cell
      xHeight = r.Height
      xLeft = r.Left
      xTop = r.Top
      xWidth = r.Width

      'Calculate starting point for the new 'Active X Check Box'
      xLeft = xLeft + (xWidth - xCheckBoxWidth) / 2#
      xTop = xTop + (xHeight - xCheckBoxHeight) / 2#

  
      'Create the Active X CheckBox
      Set TempButt = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
                                                Link:=False, _
                                                DisplayAsIcon:=False, _
                                                Left:=xLeft, _
                                                Top:=xTop, _
                                                Width:=xWidth, _
                                                Height:=xHeight)
                                              
      'Create the 'Caption' for each CheckBox
      TempButt.Object.Caption = ""
    
      'Rename the CheckBox to 'MasterX_ChildY'
      TempButt.Name = "Master" & iMasterIndex & "_Child" & iChildIndex
    
      'Increment the Control Created Count
      iCount = iCount + 1
    
    Next iChildIndex
        
  Next iMasterIndex
  
  Debug.Print iCount & Format(sControlTypeDESCRIPTION, " @") & " Control(s) were Created."
  
End Sub

Sub EnableCheckBoxEvents()
  'This Enables Active X Events (Active X controls must already exist) for CheckBoxes that Start with 'Master'

  Dim ChkBoxEvents As ClassCheckBoxEvent
  Dim myObject As OLEObject
  
  Dim iCount As Long
  Dim iIndex As Long
  
  Dim sName As String
    
  'Define the Event Collection
  Set myCheckBoxEvents = New Collection
    
  'Create the Events
  For Each myObject In ActiveSheet.OLEObjects
    
    iIndex = iIndex + 1
  
    If TypeName(myObject.Object) = sControlType Then
      sName = myObject.Name
      
      If Left(sName, Len("Master")) = "Master" Then
        Set ChkBoxEvents = New ClassCheckBoxEvent
        Set ChkBoxEvents.myCheckBox = ActiveSheet.OLEObjects(iIndex).Object
        myCheckBoxEvents.Add ChkBoxEvents, CStr(myCheckBoxEvents.Count + 1)
        iCount = iCount + 1
      End If
    End If
      
  Next myObject
  
  Debug.Print iCount & Format(sControlTypeDESCRIPTION, " @") & " Control Event(s) were Enabled."
        
End Sub

Function GetMaxChildCheckBoxCount(iMasterIndex As Long) As Long
  'This returns the maximum number of 'Child CheckBoxes' for a given 'Master' CheckBox
  
    Dim iMaxChildCount As Long

    Select Case iMasterIndex
      Case 1
        iMaxChildCount = 3
        
      Case 2
        iMaxChildCount = 5
        
      Case 3
        iMaxChildCount = 4
        
      Case 4
        iMaxChildCount = 15
        
      Case 5
        iMaxChildCount = 23
    End Select
    
    'Assign the return value
    GetMaxChildCheckBoxCount = iMaxChildCount
End Function

Lewis

I see said the blind carpenter as he picked up his hammer and saw. My original post made things a lot more complicated than they should have been. The 'Subordinate Master CheckBox' scheme that i dreamed up works, but is completely unnecessary. See the attached workbook and the complete code that follows.

Lewis


'ThisWorkBook' Module Code (for initialization):

Option Explicit

Private Sub Workbook_Open()

  'Put the focus on 'Sheet1'
  Sheets("Sheet1").Select
  
  'Enable CheckBox Events
  Call EnableCheckBoxEvents

End Sub

Class Module 'ClassCheckBoxEvent' code:

Option Explicit

'This code is based on code from Andy Pope (Thank you Andy)
'http://www.excelforum.com/excel-programming-vba-macros/1021112-call-userform-from-a-variable-number-of-activex-command-buttons.html

Public WithEvents myCheckBox As MSForms.CheckBox

Private Sub myCheckBox_Click()
  'This is the Active X Event Handler
  
  Dim sName As String
  Dim bValue As Boolean
  
  sName = Me.myCheckBox.Name
  bValue = Me.myCheckBox.Value
  
  Call ProcessCheckBoxEvent(sName, bValue)
  
  'MsgBox "Clicked " & Me.myCheckBox.Name & " " & Me.myCheckBox.Caption & "  " & Me.myCheckBox.Value
End Sub

Ordinary Code module code 'ModCheckBoxes':

Option Explicit

'This code is based on code from Andy Pope (Thank you Andy)
'http://www.excelforum.com/excel-programming-vba-macros/1021112-call-userform-from-a-variable-number-of-activex-command-buttons.html

Private Const sControlTypeDESCRIPTION As String = "Active X CheckBox"
Private Const sControlType As String = "CheckBox"
Public myCheckBoxEvents As Collection

Sub ProcessCheckBoxEvent(sInputName As String, bValue As Boolean)

  Const nCheckBoxTypeOTHER = 0
  Const nCheckBoxTypeMASTER = 1
  Const nCheckBoxTypeMasterVALUE = 2
  Const nCheckBoxTypeCHILD = 3
  
  Dim i As Long
  Dim iCheckBoxType As Long
  Dim iChildIndex As Long
  Dim iMasterIndex As Long
  Dim iMaxChildCount As Long
  Dim iPos As Long
  
  Dim bEnable As Boolean
  Dim bMasterValue As Boolean
  
  Dim sName As String
  
  'Make a local copy of the Input Name
  sName = sInputName

  'Debug.Print sName, bValue

  '''''''''''''''''''''''''''''''''''''''''''''''''''
  'Determine the Type of CheckBox Selected
  '''''''''''''''''''''''''''''''''''''''''''''''''''
  If Left(sName, Len("Master")) = "Master" Then
  
    'Strip the text 'Master' from the name
    sName = Replace(sName, "Master", "")
    
    'Get the 'Master' Index Number from the next Character (convert the string to long integer)
    iMasterIndex = CLng(Left(sName, 1))
    
    'Get the 'Master' CheckBox value
    bMasterValue = ActiveSheet.OLEObjects("Master" & iMasterIndex).Object.Value
      
    'Get the position in the string of the text 'Child' if it exists
    iPos = InStr(sName, "Child")
  
  
    If iPos > 0 Then
      'If the name contains 'Child', then it is a 'Child' CheckBox
      iCheckBoxType = nCheckBoxTypeCHILD
      
      'Get the Child Index
      iChildIndex = CLng(Mid(sName, iPos + Len("Child"), 9999))
    Else
      'Otherwise, it is a 'Master' CheckBox
      iCheckBoxType = nCheckBoxTypeMASTER
      bMasterValue = bValue
    End If
  
  
  Else
    'If the name DOES NOT contain 'Master' it is an OTHER CheckBox - should never occur
    iCheckBoxType = nCheckBoxTypeOTHER
  End If


  '''''''''''''''''''''''''''''''''''''''''''''''''''
  'Process the CheckBox Selected
  '''''''''''''''''''''''''''''''''''''''''''''''''''
  Select Case iCheckBoxType
  
    Case nCheckBoxTypeMASTER
      'Do nothing at this time
    
    Case nCheckBoxTypeCHILD
      'Debug.Print "Child CheckBox Clicked: Master = " & iMasterIndex & "   Child = " & iChildIndex
      
      'Get the number of 'Child' CheckBoxes for the given 'Master' CheckBox
      iMaxChildCount = GetMaxChildCheckBoxCount(iMasterIndex)
      
      'If the Master Value is True  , then the 'Child' CheckBoxes are all 'True'
      'If the Master Value is False, then the 'Child' CheckBoxes are all 'False'
      If bMasterValue = True Then
        For i = 1 To iMaxChildCount
          sName = "Master" & iMasterIndex & "_Child" & i
          ActiveSheet.OLEObjects(sName).Object.Value = bValue
        Next i
      End If
  
  End Select

End Sub

Sub InitializeCheckBoxStates()
  'This sets the states of the 'Active X' CheckBoxes

  Dim i As Long
  Dim iMasterIndex As Long
  Dim iMaxChildCount As Long
  
  Dim bEnable As Boolean
  Dim bValue As Boolean
  Dim bMasterValue As Boolean
  Dim bMasterValueValue As Boolean
  
  Dim sName As String
  
  'Process Each Master Index
  For iMasterIndex = 1 To 5
  
    'Get the number of 'Child' CheckBoxes for the given 'Master' CheckBox
    iMaxChildCount = GetMaxChildCheckBoxCount(iMasterIndex)
    
    'Get the Values
    sName = "Master" & iMasterIndex
    bMasterValue = ActiveSheet.OLEObjects(sName).Object.Value
  
    'Get the value of the 'Master' Value Checkbox
    sName = sName & "V"
    bMasterValueValue = ActiveSheet.OLEObjects(sName).Object.Value

    'If the Master is 'True' Then all values move in Unison
    'If the Master is True, then the 'Master Value' CheckBox is visible
    'If the Master is False, then the 'Master Value' CheckBox is not
    If bMasterValue = True Then
      bEnable = False
      ActiveSheet.OLEObjects(sName).Visible = True
      If bMasterValueValue = True Then
        bValue = True
      Else
        bValue = False
      End If
    Else
      bEnable = True
      ActiveSheet.OLEObjects(sName).Visible = False
    End If

    'Enable or disable the 'Child' CheckBoxes based on the Value of the 'Master' CheckBox and the 'Master Value' CheckBox
    For i = 1 To iMaxChildCount
      sName = "Master" & iMasterIndex & "_Child" & i
      If bMasterValue = True Then
        ActiveSheet.OLEObjects(sName).Enabled = True
        ActiveSheet.OLEObjects(sName).Object.Value = bValue
      End If
      ActiveSheet.OLEObjects(sName).Enabled = bEnable
      'Debug.Print sName, ActiveSheet.OLEObjects(sName).Enabled
    Next i

  Next iMasterIndex
  
End Sub


Sub RemoveAllActiveXCheckBoxesOnActiveSheet()

  Dim myObject As OLEObject
  Dim iCount As Long

  For Each myObject In ActiveSheet.OLEObjects
    If TypeName(myObject.Object) = "CheckBox" Then
       iCount = iCount + 1
       myObject.Delete
    End If
  Next myObject

  MsgBox iCount & " Active X CheckBoxes deleted from Sheet " & ActiveSheet.Name

End Sub
Sub CreateMasterChildCheckBoxes()
  'This Creates 'Active X' Controls

  Const xCheckBoxHeight = 12
  Const xCheckBoxWidth = 12
  
  Dim r As Range
  Dim TempButt As OLEObject
  
  Dim iColumnOffset As Long
  Dim iCount As Long
  Dim iChildIndex As Long
  Dim iMasterIndex As Long
  Dim iMaxChildCount As Long
  Dim iRowOffset As Long
    
  Dim xHeight As Double
  Dim xLeft As Double
  Dim xTop As Double
  Dim xWidth As Double
  
  '''''''''''''''''''''''''''''''''''''''''''''
  'Create CheckBoxes
  '''''''''''''''''''''''''''''''''''''''''''''
  'First Control will be at the 'Top Left' of the following cell
  Set r = ActiveSheet.Range("B19")
  
  For iMasterIndex = 1 To 5
  
    iRowOffset = 0
    iColumnOffset = iMasterIndex - 1
    Set r = ActiveSheet.Range("B19").Offset(iRowOffset, iColumnOffset)
  
    '''''''''''''''''''''''''''''''''''''''''''''
    'Create Master Control CheckBox
    '''''''''''''''''''''''''''''''''''''''''''''
    
    'Get the dimensions of the active cell
    xHeight = r.Height
    xLeft = r.Left
    xTop = r.Top
    xWidth = r.Width

    'Calculate starting point for the new 'Active X Check Box'
    xLeft = xLeft + (xWidth - xCheckBoxWidth) / 2#
    xTop = xTop + (xHeight - xCheckBoxHeight) / 2#

  
    'Create the Active X CheckBox
    Set TempButt = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
                                              Link:=False, _
                                              DisplayAsIcon:=False, _
                                              Left:=xLeft, _
                                              Top:=xTop, _
                                              Width:=xWidth, _
                                              Height:=xHeight)
                                              
    'Create the 'Caption' for each CheckBox
    TempButt.Object.Caption = ""
    
    'Rename the CheckBox to 'MasterX'
    TempButt.Name = "Master" & iMasterIndex
        
    'Increment the Control Created Count
    iCount = iCount + 1
    
    
    '''''''''''''''''''''''''''''''''''''''''''''
    'Create Child CheckBoxes
    '''''''''''''''''''''''''''''''''''''''''''''
    
    'Get the number of 'Child' CheckBoxes for the given 'Master' CheckBox
    iMaxChildCount = GetMaxChildCheckBoxCount(iMasterIndex)
    
    'Add one to the offset to account for the unused line
    iRowOffset = iRowOffset + 1
    
    For iChildIndex = 1 To iMaxChildCount
    
      iRowOffset = iRowOffset + 1
      Set r = ActiveSheet.Range("B19").Offset(iRowOffset, iColumnOffset)
    
      'Get the dimensions of the active cell
      xHeight = r.Height
      xLeft = r.Left
      xTop = r.Top
      xWidth = r.Width

      'Calculate starting point for the new 'Active X Check Box'
      xLeft = xLeft + (xWidth - xCheckBoxWidth) / 2#
      xTop = xTop + (xHeight - xCheckBoxHeight) / 2#

  
      'Create the Active X CheckBox
      Set TempButt = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
                                                Link:=False, _
                                                DisplayAsIcon:=False, _
                                                Left:=xLeft, _
                                                Top:=xTop, _
                                                Width:=xWidth, _
                                                Height:=xHeight)
                                              
      'Create the 'Caption' for each CheckBox
      TempButt.Object.Caption = ""
    
      'Rename the CheckBox to 'MasterX_ChildY'
      TempButt.Name = "Master" & iMasterIndex & "_Child" & iChildIndex
    
      'Increment the Control Created Count
      iCount = iCount + 1
    
    Next iChildIndex
        
  Next iMasterIndex
  
  Debug.Print iCount & Format(sControlTypeDESCRIPTION, " @") & " Control(s) were Created."
  
End Sub

Sub IterateThruCheckBoxes()
  'This iterates through 'Active X' Controls

  Dim myObject As OLEObject
  Dim iCount As Long

  For Each myObject In ActiveSheet.OLEObjects
    If TypeName(myObject.Object) = "CheckBox" Then
       iCount = iCount + 1
       
       Debug.Print Format(iCount, "000  ") & _
                   Format(myObject.Name, "!@@@@@@@@@@@@@@@@@@  ") & _
                   Format(myObject.Object.Caption, "!@@@@@@@@@@@@@@@@@@@@@@@@@@  ") & _
                   Format(myObject.Top, "@@@@@@@  ") & _
                   Format(myObject.Left, "@@@@@@@  ")

    End If
  Next myObject
  
  If iCount = 0 Then
    Debug.Print "There were NO" & Format(sControlTypeDESCRIPTION, " @") & " CONTROLS to Iterate through."
  End If


End Sub


Sub EnableCheckBoxEvents()
  'This Enables Active X Events (Active X controls must already exist) for CheckBoxes that Start with 'Master'

  Dim ChkBoxEvents As ClassCheckBoxEvent
  Dim myObject As OLEObject
  
  Dim iCount As Long
  Dim iIndex As Long
  
  Dim sName As String
    
  'Define the Event Collection
  Set myCheckBoxEvents = New Collection
    
  'Create the Events
  For Each myObject In ActiveSheet.OLEObjects
    
    iIndex = iIndex + 1
  
    If TypeName(myObject.Object) = sControlType Then
      sName = myObject.Name
      
      If Left(sName, Len("Master")) = "Master" Then
        Set ChkBoxEvents = New ClassCheckBoxEvent
        Set ChkBoxEvents.myCheckBox = ActiveSheet.OLEObjects(iIndex).Object
        myCheckBoxEvents.Add ChkBoxEvents, CStr(myCheckBoxEvents.Count + 1)
        iCount = iCount + 1
      End If
    End If
      
  Next myObject
  
  Debug.Print iCount & Format(sControlTypeDESCRIPTION, " @") & " Control Event(s) were Enabled."
        
End Sub

Sub DisableCheckBoxEvents()
  'This Disables Active X Events
  
  Dim iCount As Long
  Dim iStartingControlEventCount As Long
  
  'Test to see if Controls Exist
  On Error Resume Next
  iStartingControlEventCount = myCheckBoxEvents.Count
  On Error GoTo 0

  'Disable Controls
  If iStartingControlEventCount > 0 Then
    Do While myCheckBoxEvents.Count > 0
      myCheckBoxEvents.Remove 1
      iCount = iCount + 1
    Loop
  End If
  
  Debug.Print iCount & Format(sControlTypeDESCRIPTION, " @") & " Control Event(s) were Disabled."

  'Clear object pointer
  Set myCheckBoxEvents = Nothing
    
End Sub


Function GetMaxChildCheckBoxCount(iMasterIndex As Long) As Long
  'This returns the maximum number of 'Child CheckBoxes' for a given 'Master' CheckBox
  
    Dim iMaxChildCount As Long

    Select Case iMasterIndex
      Case 1
        iMaxChildCount = 3
        
      Case 2
        iMaxChildCount = 5
        
      Case 3
        iMaxChildCount = 4
        
      Case 4
        iMaxChildCount = 15
        
      Case 5
        iMaxChildCount = 23
    End Select
    
    'Assign the return value
    GetMaxChildCheckBoxCount = iMaxChildCount
End Function