using a variable to cycle through controls.

StevenD

Active Member
Joined
Nov 17, 2004
Messages
262
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.

Code:
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?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Steve

Try cycling through the Controls collection.
Code:
Dim ctl As MSForms.Control
Dim arrCaption()
Dim I As Long

    For Each ctl In Me.Controls
        If TypeName(ctl) = "CommandButton" Then
            ReDim Preserve arrCaption(I)
            arrCaption(I) = ctl.Caption
            I = I + 1
        End If
    Next ctl
 
Upvote 0
Hi Norie,

Thanks for the reply.

That works fine but I have hit a bit of an obstacle.

What I want to do is compare the caption that is on the button to a predefined cell, which I can do using your code but I want it to remember which one matches then disable at random 2 of the 3 remaining ones that doesn't match.

The one that matches could be any of the 4.
 
Upvote 0
Steve

Well that's a totally different question really.:)
Code:
Dim ctl As MSForms.Control

    For Each ctl In Me.Controls
        If TypeName(ctl) = "CommandButton" Then
            ctl.Enabled = ctl.Caption  = Range("A1")
        End If
    Next ctl
 
Upvote 0
Hi Norie,

Thanks for your help.

How do I do it so it randomly only disables 2 out of the 3 that doesn't match?
 
Upvote 0
Steve

I don't quite follow, could you explain further?
 
Upvote 0
OK,

Only 1 Caption on the commandbuttons will match the range, so I will have 3 commandbuttons with captions that do not match the range.

I want to randomly disable 2 out of the 3 remaining, leaving the one commandbutton that matches and one that doesn't.

This is the reason I tried to go down the answer(Number) route becuase that way I could just generate 2 random numbers an disable those 2 commandbuttons.
 
Upvote 0
Steve

I'm still not quite following.

This code will enable the command button that matches what's in A1 and randomly enable/disable any other command buttons.
Code:
Dim ctl As MSForms.Control

    For Each ctl In Me.Controls
        If TypeName(ctl) = "CommandButton" Then
            ctl.Enabled = (ctl.Caption = Range("A1")) Or Int(Rnd * 2)
        End If
    Next ctl
 
Upvote 0
Hi Norie,

Almost there,

In the end I need 2 commandbuttons to be enabled, the one that matches and any of the other 3 chosen randomly.
 
Upvote 0
Hi,

assuming
there are 4 commandbuttons named Answerx where x = 1 to 4
one has an caption which is the same as range A1
this button must beenabled + 1 of the three others

you can use this code
Code:
Dim i As Integer
Dim j As Integer
Dim RndButton

RndButton = Int(Rnd * 3) + 1
    For i = 1 To 4
        With Controls("Answer" & i)
            If .Caption = Range("A1") Then
            .Enabled = True
            Else
            j = j + 1
            .Enabled = RndButton = j
            End If
        End With
    Next i
kind regards,
Erik
 
Upvote 0

Forum statistics

Threads
1,218,811
Messages
6,144,610
Members
450,559
Latest member
kwenda farai

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top