create a loop, that will fill 300 cells, from 300 checkboxes

Question

I want to write a value (1) into a desired cell within Excel 2007, when I select a checkbox. The checkbox is in a Visual Basic userform, not on the active sheet itself.

The value (1) must revert back to zero when the checkbox is not selected.

I managed to get it working, however, I have more than 300 check-boxes, and want to know how to create one code that will do it in a loop?

{

    Private Sub CheckBox1_Click()
    If CheckBox1.Value = True Then
    ThisWorkbook.Sheets("sheet3").Range("b8").Value = 1
    Else: ThisWorkbook.Sheets("sheet3").Range("b8").Value = 0
    End If
    End Sub

    Private Sub CheckBox2_Click()
    If CheckBox2.Value = True Then
    ThisWorkbook.Sheets("sheet3").Range("b9").Value = 1
    Else: ThisWorkbook.Sheets("sheet3").Range("b9").Value = 0
    End If
    End Sub

    Private Sub CheckBox3_Click()
    If CheckBox3.Value = True Then
    ThisWorkbook.Sheets("sheet3").Range("b10").Value = 1
    Else: ThisWorkbook.Sheets("sheet3").Range("b10").Value = 0
    End If
    End Sub

}

from checkbox 1 to checkbox 300, the cell range will be "B8" all the way to "B308"

checkbox1 = cell range b8

checkbox2 = cell range b9

checkbox3 = cell range b10

checkbox4 = cell range b11

etc.......

Either go the `Application.Caller`-way (which still would need a sub for every checkbox, or create a class module for catching the `With Events`... — Dirk Reichel, Jul 19 '16 at 08:32
Seems to be a very similar question with fairly in-depth responses [here](http://stackoverflow.com/questions/4860724/vba-many-buttons-point-to-the-same-click-sub). — Tim Edwards, Jul 19 '16 at 08:34

score 0 · Accepted Answer · answered Jul 19 '16 at 09:22

0

Use IIf to set a value based on True or False.

Private Sub CheckBox1_Click()

    ThisWorkbook.Sheets("sheet3").Range("B8").Value = IIf(CheckBox1.Value, 1, 0)

End Sub

Private Sub CheckBox2_Click()

    ThisWorkbook.Sheets("sheet3").Range("B9").Value = IIf(CheckBox2.Value, 1, 0)

End Sub

Private Sub CheckBox3_Click()

    ThisWorkbook.Sheets("sheet3").Range("B10").Value = IIf(CheckBox3.Value, 1, 0)

End Sub

answered Jul 19 '16 at 09:22

while `ThisWorkbook.Sheets("sheet3").Range("B8").Value = 0 - CheckBox1.Value` would do the same without `IIF` – Dirk Reichel Jul 19 '16 at 12:16

score 0 · Answer 2 · answered Jul 19 '16 at 10:09

I don't know how your workbook/userform looks in detail, but this should show pretty good how to do it:

First, we need the class which we will call Class1. In this class we put the code:

Option Explicit

Public WithEvents CBoxC As MSForms.CheckBox

Private Sub CBoxC_Change()
  Dim i As Long
  i = CLng(Replace(CBoxC.Name, "CheckBox", ""))
  Sheet3.Cells(i + 7, 2).Value = 0 - CBoxC.Value
End Sub

Now we need a variable which "transfere" the events to our class. We jast add to any module:

Option Explicit

Public CBox() As New Class1

As the last step, we need to insert all the controls into our variable. So we add (or just include, if already there):

Option Explicit

Private Sub UserForm_Initialize()
  Dim b As Variant
  For Each b In Me.Controls
    If TypeName(b) = "CheckBox" Then
      If (0 / 1) + (Not Not CBox) = 0 Then ReDim CBox(0) Else ReDim Preserve CBox(UBound(CBox) + 1)
      Set CBox(UBound(CBox)).CBoxC = b
    End If
  Next
End Sub

Instead of _Click we better use _Change. This way also keyboard-input will work...

As it is pretty much no code, it also should be self explaining. Just keep in mind, that such events will come last. (which should not matter in your case)

If you still have any questions, just ask ;)

thanks dirk, think i need to go study this a bit more... or ask someone to do it for me! — Hennie Kotze, Jul 19 '16 at 12:14
@HennieKotze doesn't it do what you want? from the information you provided, it does exactly the same... `checkbox1` will set B8 to 1 if checked and to 0 if unchecked... — Dirk Reichel, Jul 19 '16 at 12:15

create a loop, that will fill 300 cells, from 300 checkboxes

2 Answers2