How to tab to a specific control automatically once an option button inside a frame is selected
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: How to tab to a specific control automatically once an option button inside a frame is selected
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2015
    Posts
    173
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to tab to a specific control automatically once an option button inside a frame is selected

    Inside a userform, I have a frame and a textbox. Inside the frame I have six option buttons. When the form initializes none of the option buttons are selected but as soon as the user selects any of the option buttons I need it to tab to the next control (textbox1). Is there not an easier way to do this? When I web searched this, one website stated that a class had to be created and then a whole bunch of code, which made no sense to me. Here is what I have so far. This code will work when assigned to a command button but I would like to avoid using a command button. I have tried using two different events for this to see how the results would be different and here they are:

    The first way gave me a run time error 13: Type Mismatch.
    Code:
    Private Sub Frame1_Enter()    
    Dim optBtn As OptionButton
        For Each optBtn In Me.Frame1.Controls
            If optBtn.Value = True Then
                MsgBox optBtn.Caption
            End If
        Next
    End Sub
    The second way doesn't do anything.
    Code:
    Private Sub Frame1_Click()
    
    Dim optBtn As OptionButton
        For Each optBtn In Me.Frame1.Controls
            If optBtn.Value = True Then
                MsgBox optBtn.Caption
            End If
        Next
    End Sub
    The use of the msgbox in both examples is for test purposes only. Thank you all for any help given.
    Last edited by Pookiemeister; Jul 20th, 2019 at 04:33 PM.

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,079
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    5 Thread(s)

    Default Re: How to tab to a specific control automatically once an option button inside a frame is selected

    Place this code in the button_click event of each option button:
    Code:
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is msforms.OptionButton Then
            If ctrl = True Then
                TextBox1.SetFocus
            End If
        End If
    Next
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,673
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: How to tab to a specific control automatically once an option button inside a frame is selected

    Try this for your test of controls:
    Since you want script to run when you click on Frame:

    Code:
    Private Sub Frame1_Click()
    'Modified  7/20/2019  5:04:38 PM  EDT
    Dim c As Control
        For Each c In Me.Frame1.Controls
            If TypeOf c Is MSForms.OptionButton And c.Value = True Then MsgBox c.Caption
        Next
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  4. #4
    Board Regular
    Join Date
    Nov 2015
    Posts
    173
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to tab to a specific control automatically once an option button inside a frame is selected

    Thank you for the quick response. This code only works when the user clicks the option button and then clicks anywhere inside the frame. Which makes sense because the code is inside the Frame1_Click event. Is there a way to this without clicking Frame1 after selecting an option button like a change event but for Frame1 (which unfortunately does not exist).

  5. #5
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,079
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    5 Thread(s)

    Default Re: How to tab to a specific control automatically once an option button inside a frame is selected

    Did you try the macro I suggested in Post #2 ?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,673
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: How to tab to a specific control automatically once an option button inside a frame is selected

    That is because the script looks to see if any Option Button is clicked. If no option button is true then their is no message to report.
    Quote Originally Posted by Pookiemeister View Post
    Thank you for the quick response. This code only works when the user clicks the option button and then clicks anywhere inside the frame. Which makes sense because the code is inside the Frame1_Click event. Is there a way to this without clicking Frame1 after selecting an option button like a change event but for Frame1 (which unfortunately does not exist).
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,673
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: How to tab to a specific control automatically once an option button inside a frame is selected

    I thought you said you wanted script to run when you clicked on Frame.

    Then I would suggest you use Mumps suggestion
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  8. #8
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,673
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: How to tab to a specific control automatically once an option button inside a frame is selected

    Put these three scripts in your UserForm
    You may need more depending on number of Option Buttons

    Code:
    Private Sub OptionButton2_Click()
    Call SetMyFocus
    End Sub
    Private Sub OptionButton3_Click()
    Call SetMyFocus
    End Sub
    Sub SetMyFocus()
    'Modified  7/20/2019  5:37:36 PM  EDT
    TextBox1.SetFocus
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  9. #9
    Board Regular
    Join Date
    Nov 2015
    Posts
    173
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to tab to a specific control automatically once an option button inside a frame is selected

    #mumps
    I did try the macro in post 2 and I responded; which would be in post 4. Thank you both for your help.

  10. #10
    Board Regular
    Join Date
    Nov 2015
    Posts
    173
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to tab to a specific control automatically once an option button inside a frame is selected

    #My Aswer Is This
    I was trying to avoid doing it that way. I was hoping for a universal solution than assigning code to each individual option buttons. So I guess this is the best way of doing what I need?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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