Search code examples
excelvbams-office

How to use On Error to break a loop in VBA?


I'm currently making a form that contains a single multi page with 11 pages. Each page has multiple check boxes, ranging from 3 - 12 check boxes. The check boxes represent list items going like follows:

I plan to use a 2 dimensional for loop (for loop within for loop) to check each box using the following syntax.

I'm used to Python, so my thinking was loop until an error occurs (Indicating no more check boxes) and then break the current loop and continue.


For Each pg In Me.MultiPage1.Pages
    x = 1
    For i = 0 To 12 
        If ActiveSheet.Shapes("CheckBox" & x & i).Value = xlOn Then
            'Code to be executed
             x = x = 1
    On Error ExitHandler

ExitHandler:
    Exit

So I'm trying to do something similar to the functionality of continue within Python. Or perhaps I am going about this in a completely incorrect manner.

Furthermore, I am also unsure if my method of naming the check boxes is correct as they are related to list items that goes as follows:

1.1
1.2
1.3
...

11.1
11.2
11.3

I'm also just realising the naming for the check boxes may be a bit messed up. I used the following method. It goes:

Page 1
CheckBox11
CheckBox12
CheckBox13 
CheckBox14

Page 2
CheckBox21,
CheckBox22, 
CheckBox23, 
CheckBox24,

...
Page 11
CheckBoxEleven1,
CheckBoxEleven2, 
CheckBoxEleven3, 
CheckBoxEleven4, 
CheckBoxEleven5, 

I used "Eleven" because the check box names for 1.11 and 1.12 were conflicting and causing an Ambiguous Name error.


Solution

  • Thanks for all the replies, they have given me a better understanding of the error handling system of VBA.

    I have created the solution to my issue using a similar approach to what was pointed out by @Damian in this post.

    Dim cont As Control
    
    For Each pg In Me.MultiPage1.Pages
            
        For Each cont In pg.Controls
                
            If cont.Value = True Then
                checkBoxes.Add cont.Caption
            End If
    
                
        Next cont
    
    Next pg
    

    Just needed to place the controls of the page into a variable Control variable which I could use with a For Each to loop the exact amount of times I need to be able to check the check box. This solution works for me as I only have checkbox controls on the page. Therefore, all I needed to do is check the value of the control, instead of also checking the type of control.

    If I had other controls then I would need to check the type of control first. This also means I don't have to formulate the name of the checkbox as I was attempting before.