Probably a For..Each sort of thing (SOLVED)

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
I have a frame with 8 OptionButtons inside of it. I have a variable that is defined by the caption of the OptionButton that is selected. This is the code I am about to write:

Private Sub OptionButton1_Change()
If OptionButton1.Value = True Then
MyVariable = OptionButton1.Caption
End Sub

I would have to write this code for each of the 8 OptionButtons. All of the OptionButtons are inside of a Frame. Is there an easier, more efficient way of doing this?

_________________
The only dumb question is the one that isn't asked. Pass on what you have learned.
This message was edited by phantom1975 on 2002-10-23 03:41
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
There is no collection of OptionButtons so you can't use a For Each Next loop on them.

But you can avoid writing 8 event procedures by setting your variable in the OKButton_Click event procedure, assuming you don't need to do anything with your variable while the Userform is active.

Code:
Private Sub OKButton_Click()
   For Each Ctrl in UserForm1.Controls
      If TypeName(Ctrl) = "OptionButton" Then
         If Ctrl Then
            MyVariable = Ctrl.Caption
            Exit For
         End If
      End If
   Next Ctrl
End Sub

Change the name of your OKButton to suit.
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
How and when do you what to use the variable, when a button is clicked or the userform dismissed or when an option button's value is changed?

If it's the latter than I think you would need to write code in <i.each[/i] of the option buttons Change events, as you wouldn't really be able to catch the change otherwise.

If it's via a command button (or the userform closing for example), then something like this should be OK: -
<pre>
Private Sub CommandButton1_Click()
Dim ctl As Control

For Each ctl In Me.Frame1.Controls
If TypeName(ctl) = "OptionButton" Then
If ctl.Value Then MsgBox ctl.Caption ' assign your variable here
End If
Next ctl

End Sub
</pre>
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
There isn't a Caption property for the variable ctl. This is what I have:

Private Sub BtnSubmit_Click()
Dim Ctl as Control
For Each Ctl in Me.FrmTenderType.Controls
If TypeName(Ctl) = "OptionButton" Then
TenderType = Ctl.Caption
End If
Next
MsgBox TenderType
End Sub

When I type Ctl. , Caption isn't available. What am I missing?
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
On 2002-10-23 03:27, phantom1975 wrote:
There isn't a Caption property for the variable ctl. This is what I have:

Private Sub BtnSubmit_Click()
Dim Ctl as Control
For Each Ctl in Me.FrmTenderType.Controls
If TypeName(Ctl) = "OptionButton" Then
TenderType = Ctl.Caption
End If
Next
MsgBox TenderType
End Sub

When I type Ctl. , Caption isn't available. What am I missing?

Don't worry about it, it'll still work. The autocomplete only lists properties and methods common to all controls. In your code above, you also need an If..Then to check for the option button with a True value, otherwise you'll just end up assigning your variable to the last one in the loop.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,688
Messages
5,597,546
Members
414,154
Latest member
thevaper

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