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.
dBforums
>
Excel Form Text Box Focus
03-08-11, 11:59
Linear Mode
