user forms - button class

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
I have a user form with at least 6 option buttons, with the possibility of 12.
As I understand it, unless I create a "button class" (here the quotes indicate my ignorance of what I am talking about), I will have to duplicate the code in each of the [Private Sub OptionButtonx_Click()] functions. :(

Can any of the experts let me know how to set up a button class for option buttons?

Gene, "The Mortgage Man", Klein
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Norie

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

Try the following code.

In a new class module called COptions.
Code:
Option Explicit

Public WithEvents ButtonGroup As MSForms.OptionButton

Private Sub ButtonGroup_Click()
    MsgBox ButtonGroup.Caption
End Sub
In a new standard module.
Code:
Option Explicit


Dim Buttons() As New COptions

Sub ShowUserForm()
Dim ButtonCount As Integer
Dim ctl As Control

    ButtonCount = 0
    For Each ctl In UserForm1.Controls
    
        If TypeName(ctl) = "OptionButton" Then
            
            ButtonCount = ButtonCount + 1
            ReDim Preserve Buttons(1 To ButtonCount)
            Set Buttons(ButtonCount).ButtonGroup = ctl

        End If
    Next ctl
    UserForm1.Show
End Sub
Now if you run the code in the standard module, it will show the userform and whenever you click an option a message box with it's caption should appear. Change the code in the class module to do what you want.

I tried putting the module code in the userform initialize event but it didn't work.

I don't know if it's me doing something wrong or it just can't go there.
 
Upvote 0

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,367
Office Version
  1. 365
Platform
  1. Windows
Just worked out how to use the userform initialize event for this.

Put the same code I suggested in the previous post in the class module.

In a standard module put this.
Code:
Public Buttons() As New COptions
And in the userform module put this.
Code:
Private Sub UserForm_Initialize()

Dim ButtonCount As Integer
Dim ctl As Control

    ButtonCount = 0
    For Each ctl In UserForm1.Controls
    
        If TypeName(ctl) = "OptionButton" Then
            
            ButtonCount = ButtonCount + 1
            ReDim Preserve Buttons(1 To ButtonCount)
            Set Buttons(ButtonCount).ButtonGroup = ctl

        End If
    Next ctl

End Sub
 
Upvote 0

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
Thanks - To be honest, I wasn't going to try it, given that you said you couldn't get it to work. (I figure it would just be too arrogant for me to try if you couldn't!)

I will give it a whirl and let you know

BTW - What stuff?
 
Upvote 0

Forum statistics

Threads
1,195,594
Messages
6,010,628
Members
441,558
Latest member
lambierules

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