Loops through Check Box in VBA

0 votes

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 in Others by Kithuzzz
• 38,010 points
368 views

1 answer to this question.

0 votes

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...

answered Apr 1 by narikkadan
• 63,180 points

Related Questions In Others

0 votes
1 answer

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

answered Mar 21 in Others by Kithuzzz
• 38,010 points
792 views
0 votes
1 answer

How to check the connected device name in Flutter?

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

answered Jul 17, 2020 in Others by MD
• 95,440 points
6,660 views
0 votes
1 answer

Language independent way to get "My Documents" folder in VBA Excel 2003

 Hello :)  This code may help you in your ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
404 views
0 votes
1 answer

How to create a Custom Dialog box in android?

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

answered Feb 18, 2022 in Others by Rahul
• 9,670 points
567 views
0 votes
1 answer

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

answered Feb 6 in Others by narikkadan
• 63,180 points
236 views
0 votes
1 answer

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

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

answered Feb 7 in Others by narikkadan
• 63,180 points
334 views
0 votes
1 answer

Excel VBA compare values on multiple rows and execute additional code

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

answered Feb 7 in Others by narikkadan
• 63,180 points
1,052 views
0 votes
1 answer

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

answered Feb 16 in Others by narikkadan
• 63,180 points
261 views
0 votes
1 answer

Spell check an Excel sheet in VBA

Use this code to check the whole ...READ MORE

answered Nov 19, 2022 in Others by narikkadan
• 63,180 points
346 views
0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered Feb 6 in Others by narikkadan
• 63,180 points
380 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP