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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
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
 

StevenD

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
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
 

StevenD

Active Member
Joined
Nov 17, 2004
Messages
262

ADVERTISEMENT

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?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Steve

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

StevenD

Active Member
Joined
Nov 17, 2004
Messages
262

ADVERTISEMENT

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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
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
 

StevenD

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

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

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
Top