using a variable to cycle through controls.

[StevenD]

Hi,

I have 4 command buttons.

I would like to get what the caption is on each command button using a for next loop.

The command buttons are named Answer1, Answer2,Answer3,Answer4.

I was trying to use the below code but it won't work.

Dim Caption(1 to 4) As String
Dim Number As Integer

For Number = 1 to 4
      Caption(Number)=Userform1.Answer(Number).Caption
Next Number

Any ideas on how I can achieve this?

 

[Norie]

Steve

Try this.

Dim ctl As MSForms.Control
Dim arrCaption()
Dim I As Long
Dim valRnd As Long
    
    For Each ctl In Me.Controls
        If TypeName(ctl) = "CommandButton" Then
            If ctl.Name <> Range("A1") Then
                ReDim Preserve arrCaption(I)
                arrCaption(I) = ctl.Name
                ctl.Enabled = False
                I = I + 1
            Else
                ctl.Enabled = True
            End If
        End If
    Next ctl
    
    valRnd = Int(Rnd * UBound(arrCaption) + 1)
    
    Me.Controls(arrCaption(valRnd)).Enabled = True

 

[erik.van.geit]

Norie,

1. sometimes your code will have only one commandbutton enabled
the correct answer + another oneshould be enabled

2. I think we are "playing" with the captions

What I want to do is compare the caption that is on the button to a predefined cell

3. if we are using a button to activate the code, it will sometimes be disabled

best regards,
Erik

 

[Norie]

Erik

1 Why do you think that?

It cycles through all the controls on the form and adds their name to the array only if their caption is not the same as the value in A1.

So the array ends up with all the names of the controls that don't have a caption matching A1, ie the control which does match isn't in the array

Now I've not exhaustively tested it but not once when I did test was only 1 button enabled.

There is however a problem with code, and that's to do with generating the random number.

Since we add 1 when we do that we can never return the value 0.

Therefore since by default arrays are 0 based then the first element of the array will never get picked.

This can be fixed however by using Option Base 1 and slightly changing the code.

All that needs to be changed in the code is to put this line for incrementing I before redimming the array.

I=I+1

2 Not sure what you mean here.

3 Yes, that's perfectly true but the OP hasn't indicated how they are running the code.

 

[erik.van.geit]

1.

1 Why do you think that?

I didn't study your code in detail
just tested 25 times

The logic of my code looks easier to me, but the reason for thinking this might be because I'm the author.
explain the logic
4 buttons - 1 which is the correct one = 3
so one of those 3 buttons must stay enabled
choosing this in advance
RndButton = Int(Rnd * 3) + 1
each time the button is not the correct one, it is counted
when code reaches the RndButton.Value, the button will stay enabled else disabled
.Enabled = RndButton = j

2. you are comparing the names of the buttons instead of the captions

What I want to do is compare the caption that is on the button to a predefined cell

not a big deal to change that

 

[Norie]

Erik

1 I tested approximately the same and like I said not once did what you describe occur.

I asssume you mean that sometimes the button whose caption matches is some time the only one enabled?

I can't see how that would happen, as I explained the button which matches is not part of the array that is used when enabling the other button.

The only way I can see only 1 button was enabled if there wasn't a button with a caption that matched what's in A1.

Then the only enabled button would be the one randomly selected

2 I think that's just a typo that slipped through because in my test situation the Names/Captions where the same.

 

[erik.van.geit]

Now I tested using a label to activate the macro.
the line
valRnd = ...
is only resulting in "1 or 2" while it should have "0, 1 or 2"
it should read

valRnd = Int(Rnd * (UBound(arrCaption) + 1))

if not, then I'm missing something ...

 

[Norie]

Erik

I just created a userform with 12 command buttons, each with the caption Answer 1, Answer 2,..., Answer 12.

I then put Answer 8 in A1 and put this code in it's initialize event.

Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control
Dim arrCaption()
Dim I As Long
Dim J As Long
Dim K As Long
Dim X As Long
Dim valRnd As Long
    
    For J = 1 To 20000
        For Each ctl In Me.Controls
            If TypeName(ctl) = "CommandButton" Then
                If ctl.Caption <> Range("A1") Then
                    I = I + 1
                    ReDim Preserve arrCaption(I)
                    arrCaption(I) = ctl.Name
                    ctl.Enabled = False
                   
                Else
                    ctl.Enabled = True
                End If
            End If
        Next ctl
        
        valRnd = Int(Rnd * UBound(arrCaption)) + 1
        
        Me.Controls(arrCaption(valRnd)).Enabled = True
        
        For Each ctl In Me.Controls
            If TypeName(ctl) = "CommandButton" Then
                If ctl.Enabled Then X = X + 1
            End If
        Next
        Range("B" & J) = X
        X = 0
    Next J
    
End Sub

All I got in column B were 2s, indicating to me that 2 command buttons on the form were enabled.

 

[Norie]

Erik

Why do you have the +1 within the parentheses?

 

[erik.van.geit]

enhanced code to take into acount more "Answerbuttons"

Dim i As Integer
Dim j As Integer
Dim RndButton
Dim ctrl As MSForms.Control

Randomize Timer

    'check how many controls have "Answer in their name"
    For Each ctrl In Me.Controls
    If InStr(1, ctrl.Name, "Answer") > 0 Then i = i + 1
    Next ctrl

    RndButton = Int(Rnd * (i - 1)) + 1

    For i = 1 To i
        With Controls("Answer" & i)
            If .Caption = Range("A1") Then
            .Enabled = True
            Else
            j = j + 1
            .Enabled = RndButton = j
            End If
        End With
    Next i

EDIT: didn't see your replies, Norie, I'll check it out

 

[Norie]

Erik

I'm still checking out the code I posted.

It's actually still running, sorry about the 'white lie', but it does take a while for 20000*14 iterations.

But I have been checking in on it occasionally and all I'm getting in column B are 2s.

PS J is currently at 11250.