I need to go through 20 distinct checkboxes (named Checkbox 30 to Checkbox 50). Four distinct arrays must be translated for each checkbox choice.
My arrays look like this: Graph 1(2,20) Graph 2(2,20) Graph 3(3,20) Graph 4(6,20)
I presently use this code; is there a simpler way to achieve this? This approach will function but is neither clear nor appealing. I know there must be a faster way to accomplish this, but I am at a loss for ideas.

If ActiveSheet.Shapes("Check Box 30").ControlFormat.Value = 1 Then ' Team 1
Graph_1(1, 1) = 1
Graph_1(2, 1) = 1
Graph_2(1, 1) = 1
Graph_2(2, 1) = 1
Graph_3(1, 1) = 1
Graph_3(2, 1) = 1
Graph_3(3, 1) = 1
Graph_4(1, 1) = 1
Graph_4(2, 1) = 1
Graph_4(3, 1) = 1
Graph_4(4, 1) = 1
Graph_4(5, 1) = 1
Graph_4(6, 1) = 1
End If

Checkbox 31 refers to row 2 of each array, 32 to row 3 etc.

Apr 1, 2023 in Others 550 views

## 1 answer to this question.

Please attempt the next option. Assuming you utilise form check boxes, you must enter 1 for the name of the check box that ends in 30, 2 for the name that ends in 31, and so on.

Sub CheckBoxesLoopHandling()
Dim sh As Worksheet, chkB As CheckBox, ext As Long
Dim Graph_1(1 To 2, 1 To 1), Graph_2(1 To 2, 1 To 1)
Dim Graph_3(1 To 3, 1 To 1), Graph_4(1 To 6, 1 To 1) 'array with a column...

Set sh = ActiveSheet

For Each chkB In sh.CheckBoxes
ext = Split(chkB.name)(2)
If ext >= 30 And ext <= 50 And chkB.Value = 1 Then
Graph_1(1, 1) = ext - 29
Graph_1(2, 1) = ext - 29
Graph_2(1, 1) = ext - 29
Graph_2(2, 1) = ext - 29
Graph_3(1, 1) = ext - 29
Graph_3(2, 1) = ext - 29
Graph_3(3, 1) = ext - 29
Graph_4(1, 1) = ext - 29
Graph_4(2, 1) = ext - 29
Graph_4(3, 1) = ext - 29
Graph_4(4, 1) = ext - 29
Graph_4(5, 1) = ext - 29
Graph_4(6, 1) = ext - 29
End If
Next
End Sub

or

Sub CheckBoxesLoopHandling()
Dim sh As Worksheet, chkB As CheckBox, ext As Long
Dim Graph_1(1 To 2, 1 To 21), Graph_2(1 To 2, 1 To 21)
Dim Graph_3(1 To 3, 1 To 21), Graph_4(1 To 6, 1 To 21)

Set sh = ActiveSheet

For Each chkB In sh.CheckBoxes
ext = Split(chkB.name)(2)
If ext >= 30 And ext <= 50 And chkB.Value = 1 Then
Graph_1(1, ext - 29) = 1
Graph_1(2, ext - 29) = 1
Graph_2(1, ext - 29) = 1
Graph_2(2, ext - 29) = 1
Graph_3(1, ext - 29) = 1
Graph_3(2, ext - 29) = 1
Graph_3(3, ext - 29) = 1
Graph_4(1, ext - 29) = 1
Graph_4(2, ext - 29) = 1
Graph_4(3, ext - 29) = 1
Graph_4(4, ext - 29) = 1
Graph_4(5, ext - 29) = 1
Graph_4(6, ext - 29) = 1
End If
Next
End Sub

To place 1 in the corresponding array columns...

• 63,420 points

## Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

## How to check the connected device name in Flutter?

Hi@akhtar, Flutter has its command own command to ...READ MORE

## How to create a Custom Dialog box in android?

Here I have created a simple Dialog, ...READ MORE

## Copy the respective values from another workbook depend on specific conditions

Try this: Sub Get_Respective_Values_Of_Last_Closing_Date() Dim wb1 ...READ MORE

## VBA How do I replace the range with an array in SUMIF

You can't, in my opinion. When you ...READ MORE

## Excel VBA compare values on multiple rows and execute additional code

I would use a Dictionary & Collection ...READ MORE

## How to concatenate elements of a single-dimensional array using VBA?

Using Microsoft 365's UNIQUE and SORT in VBA ' This is a ...READ MORE