Option (Radio) Button Event Programming

[pistonbroke]

I have a user form with two Frames, each containing 20 radio or option buttons. I want to run some code independently based on the selected button (value=true) in each frame. I can make it work fine by having separate event programming for each button, which calls out a sub-routine, but this seems like a long way around it..

example....

Private Sub OptionButton6_Click()
Call Set_Baseline
End Sub

Private Sub OptionButton7_Click()
Call Set_Baseline
End Sub

Private Sub OptionButton8_Click()
Call Set_Baseline
End Sub

Obviously i have 40 sets of the above lines of code.. Is there a way to have the sub-routine run based on clicking any radio button inside each Frame control ? I can't find a Frame property that does this. I also do not want to add an additional "Submit" button, even though this would work as i want the action to occurr just by clicking any radio button ?

Any help appreciated. Thanks.

 

[pgc01]

Hi

I think the cleanest way is to create a class where you define your own Option Button, one that always reacts the same way to the click event. If you associate a group of buttons in the userform to this new type, they will all behave the same.

If you think it's the way to go, I can post an example.

 

[pistonbroke]

pgc01, i'd be interested to see how that works, so please do post an example.

To elaborate, my form shows rows of data of racecar laps, including driver and lap segment times, there are 20 rows of data (20 different drivers) and the two radio button collections line up with each row entry. On clicking one of the first radio button sets (within each of the 2 frame controls), the macro gets the data for that driver / lap data and compares it with the data from a second selection (from the 2nd set of radio buttons) to create a lap time comparison between the 2 selected drivers. The method i mentioned earlier works fine, but is messy code, with 40 sub routines that call one of two secondary subroutines. Thanks for the input, look forwar to trying it.

 

[mikerickson]

If all 20 buttons are in the same option button group, you could put this code in the userform's code module

Public WithEvents clickedButton As msforms.OptionButton

Private Sub clickedButton_Change()
    Static commonEventDisabled
    Dim oneControl As Object
    
    If commonEventDisabled Then commonEventDisabled = False: Exit Sub
    
    Rem call the sub
    Call set_Baseline
    
    Rem find which option button was clicked and assign it to clickedButton
    For Each oneControl In Me.Controls
        If TypeName(oneControl) = "OptionButton" Then
            If oneControl.Value Then
                commonEventDisabled = True
                Set clickedButton = oneControl
            End If
        End If
    Next oneControl
End Sub

Private Sub UserForm_Initialize()
    Rem set first clickedButton
    OptionButton1.Value = True
    Set clickedButton = OptionButton1
End Sub

 

[mikerickson]

Oh, I mis read 2 frames, each with 20 buttons. Put this in the UF code module.

Public WithEvents clickedButton As msforms.OptionButton

Private Sub clickedButton_Change()
    Static commonEventDisabled As Boolean
    Dim oneControl As Object
    
    If commonEventDisabled Then commonEventDisabled = False: Exit Sub
    
    Rem call the sub
    Call set_Baseline
    
    Rem find which option button was clicked and assign it to clickedButton
    For Each oneControl In clickedButton.Parent.Controls
        If TypeName(oneControl) = "OptionButton" Then
            If oneControl.Value Then
                commonEventDisabled = True
                Set clickedButton = oneControl
            End If
        End If
    Next oneControl
End Sub

Private Sub Frame1_Enter()
    Dim oneControl As Object
    For Each oneControl In Frame1.Controls
        If TypeName(oneControl) = "OptionButton" Then
            If oneControl.Value Then
                Set clickedButton = oneControl
            End If
        End If
    Next oneControl
End Sub

Private Sub Frame2_Enter()
    Dim oneControl As Object
    For Each oneControl In Frame2.Controls
        If TypeName(oneControl) = "OptionButton" Then
            If oneControl.Value Then
                Set clickedButton = oneControl
            End If
        End If
    Next oneControl
End Sub

Private Sub UserForm_Initialize()
    OptionButton1.Value = True: Rem intial button in frame 1
    OptionButton21.Value = True: Rem intial button in frame 2
    Set clickedButton = OptionButton1
End Sub

 

[pistonbroke]

Mikerickson, thanks for that, i have a compile error though at the #Public WithEvents clickedButton As msforms.OptionButton# line. Says Invalid Attribute at Sub or Function ? Does this Public line need to be somewhere different than the UF ?

 

[mikerickson]

It possible that that is a difference between my VBA for Mac and VBA for Windows.
Are the # in y0ur post also in the code module?

The BB syntax for code tags is

[ c o d e ] ' code[ / c o d e]

without the spaces

 

[mikerickson]

Wait a minute, it can't be a Mac/PC thing, I wrote that code on the PC at work.

Did you put it in the userform's code module.