I have 5 radio buttons in a frame, and I would like to know the best way to return the focus to the text box on the form after a radio button is clicked. Will I have to add code for each individual button or is there a more streamlined way in Excel VBA?
Unlike VB6, VBA doesn't suppport control arrays. There is a workaround which uses a custom class to create a generic event handler for all of the radio buttons. We create an instance of the custom class for each radiobutton. For only 5 controls it might not be worth the effort, but here's a demonstation for you if you're interested.
Firstly we have a userform called UserForm1 which has a Frame (Frame1) control containing five option buttons (OptionButton1, OptionButton2, etc...) and a textbox called TextBox1.
In the userform's class module we have the following code:
Private colListeners As Collection
Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control
Dim clsListener As cListener
For Each ctl In Me.Frame1.Controls
If TypeName(ctl) = "OptionButton" Then
If colListeners Is Nothing Then
Set colListeners = New Collection
Set clsListener = New cListener
Set clsListener.OptButton = ctl
Set clsListener.TargetTextBox = Me.TextBox1
Then we add a custom class module to the project and give it the name cListener. We add this code:
Private WithEvents pOptionButton As MSForms.OptionButton
Private pTargetTextBox As MSForms.TextBox
Public Property Set OptButton(ByRef obj As MSForms.OptionButton)
Set pOptionButton = obj
Public Property Set TargetTextBox(ByRef obj As MSForms.TextBox)
Set pTargetTextBox = obj
Private Sub pOptionButton_Click()
If Not pTargetTextBox Is Nothing Then
Let me know if any of this doesn't make sense and I'll try to explain it.
Thanks Colin. I have studied the code you provided and found it interesting how a collection (of references to a set of controls, I think) can be created to act on a single control's event among the group of controls.
From this Excel exercise I have learned that coding the AfterUpdate event of 5 radio buttons is the shortest and simplest way to return focus to the text box after a button is clicked.
Private Sub optBtn1_AfterUpdate()