If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Excel Form Text Box Focus

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:

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
End If
Set clsListener = New cListener
Set clsListener.OptButton = ctl
Set clsListener.TargetTextBox = Me.TextBox1
colListeners.Add clsListener
End If
Next ctl
End Sub

Then we add a custom class module to the project and give it the name cListener. We add this code:

Code:

Private WithEvents pOptionButton As MSForms.OptionButton
Private pTargetTextBox As MSForms.TextBox
Public Property Set OptButton(ByRef obj As MSForms.OptionButton)
Set pOptionButton = obj
End Property
Public Property Set TargetTextBox(ByRef obj As MSForms.TextBox)
Set pTargetTextBox = obj
End Property
Private Sub pOptionButton_Click()
If Not pTargetTextBox Is Nothing Then
pTargetTextBox.SetFocus
End If
End Sub

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.