Userform VBA option button get selected option
Results 1 to 7 of 7

Thread: Userform VBA option button get selected option

  1. #1
    Board Regular
    Join Date
    Jul 2014
    Location
    Anywhere
    Posts
    450
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Userform VBA option button get selected option

    Hi there,
    There's no easy way to get selected option of a option button in a Userform VBA besides read one by one is is true or false (checked or not)???
    GroupName works only to avoid multiselection in the group? There's no "grupname.value" option.
    Thanks
    Using Excel/Word 2010.
    Backup your original file before doing any modification.

  2. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,381
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Userform VBA option button get selected option

    In Access VBA you can get the value of the Frame the option buttons are in. But from what I've seen from digging around on the internet, Excel VBA isn't set up that way. This code should work for what you want to do though. These are all generic control names, so change those as needed.

    Code:
    Private Sub CommandButton1_Click()
    For Each ctl In UserForm1.Controls
        If TypeName(ctl) = "OptionButton" Then
            If ctl.Value = True Then
                Select Case ctl.Caption
                    Case "OptionButton1"
                        MsgBox "do something"
                    Case "OptionButton2"
                        MsgBox "do something else"
                End Select
            End If
        End If
    Next ctl
    End Sub
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

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

    Default Re: Userform VBA option button get selected option

    You can use this to check all Option Buttons in a UserForm.

    Code:
    Private Sub CommandButton1_Click()
    'Modified  8/16/2019  11:41:28 PM  EDT
    For Each Control In Me.Controls
        If TypeName(Control) = "OptionButton" And Control.Value = True Then
            MsgBox Control.Caption
        End If
    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
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,992
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Userform VBA option button get selected option

    Hi,
    If you just want to establish the status for a named group of controls (True / False) you could as an idea, create a function

    See if following will do what you want

    Code:
     Function GroupNameValue(ByVal Group As String, ByVal ControlType As String) As Boolean    
        Dim Ctrl As Control
        For Each Ctrl In Me.Controls
            If TypeName(Ctrl) = ControlType Then
             GroupNameValue = CBool(Ctrl.Object.GroupName = Group And Ctrl.Value)
             If GroupNameValue Then Exit Function
            End If
        Next Ctrl
    End Function
    You just pass the group name & type of control in the group

    e.g.

    Code:
     Private Sub CommandButton1_Click()   
      MsgBox GroupNameValue("G1", "OptionButton")
    End Sub
    
    
    Private Sub CommandButton2_Click()
      MsgBox GroupNameValue("G1", "CheckBox")
    End Sub

    Hope Helpful

    Dave
    Last edited by dmt32; Aug 17th, 2019 at 02:02 AM.

  5. #5
    Board Regular
    Join Date
    Jul 2014
    Location
    Anywhere
    Posts
    450
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform VBA option button get selected option

    There's no single function that gives the selected option button?
    Like: GroupNameValue("MyGroup")=3 (the 3rd option button ou the groupname "MyGroup" is checked (true))
    Using Excel/Word 2010.
    Backup your original file before doing any modification.

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,752
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Userform VBA option button get selected option

    Quote Originally Posted by eduzs View Post
    There's no single function that gives the selected option button?
    Like: GroupNameValue("MyGroup")=3 (the 3rd option button ou the groupname "MyGroup" is checked (true))

    Apparently there is not a single function.



    This is an option without reviewing one by one, it only shows you the last one selected.

    Code:
    Option Explicit
    Dim opSel As String
    
    
    Private Sub CommandButton1_Click()
      MsgBox "Selected buton: " & opSel
    End Sub
    
    
    Private Sub OptionButton1_Click()
      opSel = OptionButton1.GroupName & " " & OptionButton1.Name
    End Sub
    Private Sub OptionButton2_Click()
      opSel = OptionButton2.GroupName & " " & OptionButton2.Name
    End Sub
    Private Sub OptionButton3_Click()
      opSel = OptionButton3.GroupName & " " & OptionButton3.Name
    End Sub
    Private Sub OptionButton4_Click()
      opSel = OptionButton4.GroupName & " " & OptionButton4.Name
    End Sub
    Private Sub OptionButton5_Click()
      opSel = OptionButton5.GroupName & " " & OptionButton5.Name
    End Sub
    Private Sub OptionButton6_Click()
      opSel = OptionButton6.GroupName & " " & OptionButton6.Name
    End Sub

    ------------------------------------------------------------------------

    Or this other option, shows you true if any optionbutton is selected.

    Code:
    Private Sub CommandButton2_Click()
      MsgBox OptionButton1 = OptionButton2 = OptionButton3
    End Sub
    Regards Dante Amor

  7. #7
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,992
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Userform VBA option button get selected option

    Quote Originally Posted by eduzs View Post
    There's no single function that gives the selected option button?
    Like: GroupNameValue("MyGroup")=3 (the 3rd option button ou the groupname "MyGroup" is checked (true))
    Not as far as I am aware unless another here reading this thread can offer further insight.

    All suggestions posted are workarounds & any one may offer something that will go a little way in helping you.

    Dave

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
  •