Help with Do / Loop function!

[curtryan45]

I have this code:
Private Sub checkbox1_Change()
If CheckBox1.Value = False Then
Sheet2.Range("b13") = ""
End If
End Sub
Private Sub CheckBox1_Click()
If CheckBox1.Value = True And Sheet2.Range("b13") = "" Then
Sheet2.Range("b13") = Sheet2.Range("a13")
Sheet1.Range("C27") = Sheet2.Range("a13")
End If
Sheet1.Range("C27") = Sheet2.Range("b13")
End Sub

I need the same code to run for
Checkbox1 - Checkbox50,
b13-b62,
a13-a62,
c27-76

Right now I just have that same code over and over. It is very cumbersome to right and add too. Please help!

 

[xld]

Private Sub CheckBox1_Change()
    CheckBoxChange Me.CheckBox1
End Sub

Private Sub CheckBox1_Click()
    CheckBoxClick Me.CheckBox1
End Sub

Private Sub CheckBox2_Change()
    CheckBoxChange Me.CheckBox2
End Sub

Private Sub CheckBox2_Click()
    CheckBoxClick Me.CheckBox2
End Sub

Private Sub CheckBoxChange(cb As MSForms.CheckBox)
Dim CBNum As Long

    CBNum = Mid(cb.Name, 9)
    If Not cb.Value Then
        Sheet2.Cells(CBNum + 12, "B").Value = ""
    End If
End Sub

Private Sub CheckBoxClick(cb As MSForms.CheckBox)
Dim CBNum As Long

    CBNum = Mid(cb.Name, 9)
    If cb.Value And Sheet2.Cells(CBNum + 4, "B").Value = "" Then
        Sheet2.Cells(CBNum + 12, "B").Value = Sheet2.Cells(CBNum + 12, "A").Value
        Sheet1.Cells(CBNum + 26, "C").Value = Sheet2.Cells(CBNum + 12, "A").Value
    End If
    Sheet1.Cells(CBNum + 26, "C").Value = Sheet2.Cells(CBNum + 12, "B").Value
End Sub

 

[iliace]

The best way to do this (and perhaps not the most evident) would be to create a custom class for your checkboxes, with a single event handler. Then, adjust your userform init procedure by creating an array of these buttons, and indexing them.

It is from within the class that you can assign whatever ranges to whichever checkboxes, referring to the latter by name and the former by address.

 

[curtryan45]

Private Sub CheckBox1_Change()
    CheckBoxChange Me.CheckBox1
End Sub
 
Private Sub CheckBox1_Click()
    CheckBoxClick Me.CheckBox1
End Sub
 
Private Sub CheckBox2_Change()
    CheckBoxChange Me.CheckBox2
End Sub
 
Private Sub CheckBox2_Click()
    CheckBoxClick Me.CheckBox2
End Sub
 
Private Sub CheckBoxChange(cb As MSForms.CheckBox)
Dim CBNum As Long
 
    CBNum = Mid(cb.Name, 9)
    If Not cb.Value Then
        Sheet2.Cells(CBNum + 12, "B").Value = ""
    End If
End Sub
 
Private Sub CheckBoxClick(cb As MSForms.CheckBox)
Dim CBNum As Long
 
    CBNum = Mid(cb.Name, 9)
    If cb.Value And Sheet2.Cells(CBNum + 4, "B").Value = "" Then
        Sheet2.Cells(CBNum + 12, "B").Value = Sheet2.Cells(CBNum + 12, "A").Value
        Sheet1.Cells(CBNum + 26, "C").Value = Sheet2.Cells(CBNum + 12, "A").Value
    End If
    Sheet1.Cells(CBNum + 26, "C").Value = Sheet2.Cells(CBNum + 12, "B").Value
End Sub

What would I have to change to make this work for CheckBox3-50? It works for the ones you did (Checkbox 1 & 2).

 

[curtryan45]

The best way to do this (and perhaps not the most evident) would be to create a custom class for your checkboxes, with a single event handler. Then, adjust your userform init procedure by creating an array of these buttons, and indexing them.

It is from within the class that you can assign whatever ranges to whichever checkboxes, referring to the latter by name and the former by address.

Is this a lengthy process? I am not extrememly skilled in VBA, so I really wouldnt know how to right this. Do you have an example?

 

[xld]

What would I have to change to make this work for CheckBox3-50? It works for the ones you did (Checkbox 1 & 2).

Add click events for each one similar to CheckBox2_Click.

 

[iliace]

You would create a class module, let's say call it myClass (although I'd suggest something more self-documenting for a name).

Within the class, you'll declare a checkbox:

Public WithEvents CheckBoxes As MsForms.CheckBox

You'll then add event processing code

Private Sub CheckBoxes_Click()
  Call ProcessControl(CheckBoxes.Name)
End Sub

This passes the name of the clicked checkbox to a function called ProcessControl, which will be doing whatever functionality you need.

Now, in your UserForm, add an array of your new class (as a global variable) and an Initialize event, to populate the array:

Dim Checks() as New myClass
 
Private Sub UserForm_Initialize()
  Dim i As Long
  Dim ctrl As Control
  i = 0
  For Each ctl In UserForm1.Controls
    If TypeName(ctl) = "CheckBox" Then
      i = i + 1
      ReDim Preserve Checks(1 To i)
      Set Checks(i).CheckBoxes = ctl
    End If
  Next ctl
End Sub

 

[curtryan45]

You would create a class module, let's say call it myClass (although I'd suggest something more self-documenting for a name).

Within the class, you'll declare a checkbox:

Public WithEvents CheckBoxes As MsForms.CheckBox

You'll then add event processing code

Private Sub CheckBoxes_Click()
  Call ProcessControl(CheckBoxes.Name)
End Sub

This passes the name of the clicked checkbox to a function called ProcessControl, which will be doing whatever functionality you need.

Now, in your UserForm, add an array of your new class (as a global variable) and an Initialize event, to populate the array:

Dim Checks() as New myClass
 
Private Sub UserForm_Initialize()
  Dim i As Long
  Dim ctrl As Control
  i = 0
  For Each ctl In UserForm1.Controls
    If TypeName(ctl) = "CheckBox" Then
      i = i + 1
      ReDim Preserve Checks(1 To i)
      Set Checks(i).CheckBoxes = ctl
    End If
  Next ctl
End Sub

The Checkboxes are actually made in the sheet itself, I have no userform. They are the checkboxes from Control Box.

So the ProcessControl Function is what will copy the the cells when the checkbox is clicked?

Can you explain what the last part of the code actually does?

Thank you for the help, it looks perfect if I can write the function correctly.

 

[iliace]

Then do it on Workbook_Open event. Whatever happens on load - simply loop through controls, pick out checkboxes, and add an item in the array for each.