Consulting

Results 1 to 5 of 5

Thread: Please Help! Code For Option Buttons in Excel VBA User Form

  1. #1

    Please Help! Code For Option Buttons in Excel VBA User Form

    Good day,

    A request for help from a complete newbie!

    I have a questionnaire that I have created a User Form for. Creating the user form went well but I am now stuck trying to code the answers to cells in the database.

    I have managed to get the code right for the first 8 questions as they all use a text box field:

    Private Sub CommandButton13_Click()
    
    
        erow = Sheets("Database").Range("a" & Rows.Count).End(xlUp).Row
        
            Range("a" & erow + 1) = TextBox1.Value
            Range("b" & erow + 1) = ComboBox1.Value
            Range("c" & erow + 1) = ComboBox2.Value
            Range("d" & erow + 1) = TextBox59.Value
            Range("e" & erow + 1) = TextBox60.Value
            Range("f" & erow + 1) = TextBox61.Value
            Range("g" & erow + 1) = TextBox2.Value
            Range("h" & erow + 1) = OptB_Male.Value
    
    
    End Sub
    However, my next question (question 9) requires an Option Box field for "Weather", with the options being:
    1 (Good)
    2 (Fair)
    3 (Bad)

    I need the answer, whether it be 1, 2 or 3, to be placed into column "i" in my excel database (the worksheet is named "Database").

    I have googled extensively and watched many video tutorials, but I cannot understand how to do it. My form makes use of many option boxes so I need to understand the code in order to modify the code for the other questions.

    I will be using the User Form, on a tablet, to carry out multiple surveys. I have a "submit" button that sends the inputted data input to my "Database" worksheet.

    Please help this Novice learn (and understand) new tricks

  2. #2
    modify the code here to get the Value of your option button:
    VBA - Value of an option button in a frame (within an Excel sheet) - Stack Overflow

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Something like this probably

    Assumes OB_Good, OB_Fair, and OB_Bad are the names of the option buttons

    I'd put all 3 inside a frame

        If OB_Good Then
            Range("i" & erow + 1) = "Good"
        ElseIf OB_Fair Then
            Range("i" & erow + 1) = "Fair"
        ElseIf OB_Bad Then
            Range("i" & erow + 1) = "Bad"
        End If
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Thank you so much!! Finally, code that is short, sweet, simple, easy to understand and manipulate for other option buttons. I appreciate you

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    2 strategies:

    store the value when a optionbutton is clicked; e.g. in the tag property of the userform:

    Private Sub Optionbutton1_click()
      if optionbutton1 then tag=optionbutton1.caption
    End sub
    
    Cells(1,5)=tag
    Or

    - check the values of all linked optionbuttons

    cells(1,5)= choose(ABS(1*Opt_1 + 2*Opt_2 +3*Opt_3 +4*Opt_4+5*Opt_5),Opt_01.caption,Opt_2.caption,Opt_3.caption,Opt_4.caption,Opt_5.caption)

Tags for this Thread

Posting Permissions

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