"Indirect" reference a combobox in a loop

Question

I have this problem that my excel crash whenever I try to run my code.

I do believe I have a solution but I don't know how to execute it.

I have this code:

If (AnswerGame1A <> "") And (AnswerGame1B <> "") Then
    Score1A.Visible = False
    Score1B.Visible = False
    Resultlist1.Visible = False
    SubmitGame1.Visible = False
    Dash1.Visible = False
    GameLabel1.Visible = True
    GameLabel1.Left = 36
End If

If (AnswerGame2A <> "") And (AnswerGame2B <> "") Then
    Score2A.Visible = False
    Score2B.Visible = False
    Resultlist2.Visible = False
    SubmitGame2.Visible = False
    Dash2.Visible = False
    GameLabel2.Visible = True
    GameLabel2.Left = 36
End If

And this continues for another 51 times.

If I remove this code, the file does not chrash, My idea is to write a loop instead.

something like this, but this doesn't work.

INFO: all these names are controls within a multipage, that is within a userform. It is comboboxes, labels, commandbuttons and textboxes. The code run when the userform initialize.

For i = 1 to 51
If (Indirect("AnswerGame" & i & "A") <> "") And (Indirect("AnswerGame" & i & "B") <> "") Then
    Indirect("Score" & i & "A").Visible = False
    Indirect("Score" & i & "B").Visible = False
    Indirect("Resultlist" & i).Visible = False
    Indirect("SubmitGame" & i).Visible = False
    Indirect("Dash" & i).Visible = False
    Indirect("GameLabel" & i).Visible = True
    Indirect("GameLabel" & i).Left = 36
End If
Next i

Do you think this could help excel from not crashing? and how can I fix the code to work?

Are the combo box in discussion of type ActiveX sheet objects? — FaneDuru, May 14 '21 at 12:38

score 1 · Answer 1 · answered May 14 '21 at 12:57

Supposing that your combo boxes are of sheet ActiveX type, try the next code, please:

Sub testAvoitManyIterationsCombo()
   Dim sh As Worksheet, i As Long
   
   Set sh = ActiveSheet ' use here your necessary sheet

   For i = 1 To 51
        If sh.OLEObjects("AnswerGame" & i & "A").Object.Value <> "" And sh.OLEObjects("AnswerGame" & i & "B").Object.Value <> "" Then
            sh.Shapes("Score" & i & "A").Visible = False
            sh.Shapes("Score" & i & "B").Visible = False
            sh.Shapes("Resultlist" & i).Visible = False
            sh.Shapes("SubmitGame" & i).Visible = False
            sh.Shapes("Dash" & i).Visible = False
            sh.Shapes("GameLabel" & i).Visible = True
            sh.Shapes("GameLabel" & i).left = 36
        End If
    Next i
End Sub

Thank you @FaneDuru I was able to solve it – Nick May 16 '21 at 19:36 — Nick, May 16 '21 at 19:36

score 1 · Answer 2 · answered May 14 '21 at 13:05

1

and if they are not activeX this should get you on track:

    Option Explicit
    Private Sub UserForm_Click()
        Dim i As Long, str As String
        
        For i = 1 To 10
            str = "AnswerGame" & i & "A"
            If Me.Controls(str).Value = "" Then
                Score1A.Visible = False
            End If
        Next i
    End Sub

answered May 14 '21 at 13:05

ceci

589
4
14

Thank you @ceci I used your idea to solve it. see my answer. – Nick May 16 '21 at 19:36

score 0 · Accepted Answer · answered May 14 '21 at 17:38

My solution that works for my purpose. The file does not seem to crash anymore. thank you @ceci for showing how to do it.

sorry for using "x" instead of "i", "i" is already being used elsewhere.

Dim x As Long, str1 As String, str2 As String, SCO1 As String, SCO2 As String, Res As String
Dim SubmitG As String, Da As String, GameL As String

For x = 1 To 51
    str1 = "AnswerGame" & x & "A"
    str2 = "AnswerGame" & x & "B"
    If Me.Controls(str1) <> "" Then
        If Me.Controls(str2) <> "" Then
    
            SCO1 = "Score" & x & "A"
            SCO2 = "Score" & x & "B"
            Me.Controls(SCO1).Visible = False
            Me.Controls(SCO2).Visible = False
            
            Res = "Resultlist" & x
            Me.Controls(Res).Visible = False
            
            SubmitG = "SubmitGame" & x
            Me.Controls(SubmitG).Visible = False
            
            Da = "Dash" & x
            Me.Controls(Da).Visible = False
            
            GameL = "GameLabel" & x
            Me.Controls(GameL).Visible = True
            Me.Controls(GameL).Left = 36
            
            
        End If
    End If
Next x

"Indirect" reference a combobox in a loop

3 Answers3