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, 2023 in Others by Kithuzzz
• 38,000 points
952 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, 2023 by narikkadan
• 63,640 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, 2023 in Others by Kithuzzz
• 38,000 points
1,726 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,460 points
8,132 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,690 points
800 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,680 points
1,067 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, 2023 in Others by narikkadan
• 63,640 points
587 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, 2023 in Others by narikkadan
• 63,640 points
861 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, 2023 in Others by narikkadan
• 63,640 points
1,835 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, 2023 in Others by narikkadan
• 63,640 points
664 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,640 points
611 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, 2023 in Others by narikkadan
• 63,640 points
892 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