Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    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?

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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.

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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.

    Example code:
    Private Sub optBtn1_AfterUpdate()
    Me.txtMyTextBox.SetFocus
    End Sub

    That's 15 lines of code vs. your 29.

    Thanks again.
    Jerry
    RESOLVED

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Jerry,

    I definitely agree it's simpler. Don't pay too much attention to the number of lines of code though - I could easily lop off 10+ lines from the example I gave but it wouldn't be as accurately written.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •