Consulting

Results 1 to 13 of 13

Thread: Option Button "Linked Cell" Property

  1. #1

    Option Button "Linked Cell" Property

    Does the "Linked Cell" property in an Option Button (Activex Control) work in the same fashion as the linked cell in used an option button in the Forms toolbar?

    For example, with the invaluable help of Carlos Paleo, I am refining the design of a worksheet whereby two option buttons will hide or unhide two sets of rows. Depending on the selection made by the user, one of the two sets of rows will be aggregated on the bottom of the worksheet.

    If I were working with an option button from Forms, I would enter a simple formula choosing the rows to aggregate depending on whether the linked cell displayed 1 or 2. How does this work with an Activex control?

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    For an ActiveX Option Button I would just add code to the click event. Then check if the value is true or false and add your code for what you want to have happen depending on that.

  3. #3
    My VBA coding skills are rather rudimentary. Can you show me sample code?

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Make two Option Buttons the paste this code in the worksheet code module for the worksheet that has the Option Buttons. You can right click on the sheet tab and select View Code to get to the appropriate code window.

    Option Explicit
    
    Private Sub OptionButton1_Click()
    If Me.OptionButton1.Value = True Then
            MsgBox "You presses OptionButton1"
        Else
            MsgBox "You presses OptionButton2"
        End If
    End Sub
    
    Private Sub OptionButton2_Click()
    If Me.OptionButton1.Value = True Then
            MsgBox "You presses OptionButton1"
        Else
            MsgBox "You presses OptionButton2"
        End If
    End Sub

  5. #5
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Fwiw, there are subtle differences between ActiveX (control toolbar) and Forms (toolbar) controls. ActiveX controls do not work with Mac's. They also have code associated with them which is located in the sheet on which the control is located. A Forms control can have a macro 'assigned' to it; such code would be located in a Standard Module. So by virtue, ActiveX controls are inherently more associated with events on a more singular (worksheet) level.

    If there is not a specific reason to use one or the other, I guess it's just personal preference.

    And Jake, tag. You're it.

  6. #6

    Linked Cell property in Option buttons

    Jake, thanks for the post. I understand however that your code will simply cause message boxes to pop up, depending on whether the user clicked on Option Button 1 or Option Button 2.

    What I really need is for the option buttons to place a distinguishing value in cell G4 (which happens to be available!); this could be a TRUE or FALSE statement, or the values 1 or 2 (indicating whether option box 1 or 2 was clicked). This will enable me to use a logical Excel function to determine which rows should be summed up. For example, if the value in cell G4 is "1", rows 5, 10, and 15 will be aggregated; if it is "2", rows 6, 11, and 16 will be aggregated.

  7. #7
    Administrator
    VP-Knowledge Base VBAX Master
    Joined
    Jan 2005
    Location
    Porto Alegre - RS - Brasil
    Posts
    1,219
    Location
    Hi K.,

    you may add one line to the code I had provided you in the other thread.

    In Private Sub OptionButton1_Click() add as the last line:

    Range("G4") = True
    And for the other button:

    Range("G4") = False

    If this is for another worksheet use:

    Private Sub OptionButton1_Click()
        Range("G4") = True
    End Sub
    
    Private Sub OptionButton2_Click()
        Range("G4") = False
    End Sub
    Best Regards,

    Carlos Paleo.

    To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.

    If Debugging is harder than writing a program and your code is as good as you can possibly make
    it, then by definition you're not smart enough to debug it.




    http://www.mugrs.org

  8. #8
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    The message box was just an example. You can use a Select Case statement to chech for values.

    Private Sub OptionButton1_Click() 
    If Me.OptionButton1.Value = True Then 
    Select Case Range("G4").Value
    Case Is = TRUE
    Case Is = FALSE
    Case Is = 1
    Case Is = 2
    End Select
    End If 
    End Sub
    Then you can add whatever code you want for each case.

  9. #9
    It works great, as does the code you provided for the two option buttons. Since I'm trying to learn as I do, could you explain how your code operates? For example. what does this line do?

    For i = 5 To Range("A65536").End(xlUp).Row
    Thanks!

  10. #10
    Administrator
    VP-Knowledge Base VBAX Master
    Joined
    Jan 2005
    Location
    Porto Alegre - RS - Brasil
    Posts
    1,219
    Location
    Hi K.,

    this line loops from all the rows starting at row 5 and going up till the last row used in column A.

    Lets analyse it in parts:

    Range("A65536") -> goes to the last row in column A

    End(xlUp) -> its the same as pressing end and the up key in the keyboard, this makes your selection go to the last occupied cell in this column

    Row -> returns the row number

    So it makes: For i = 5 To Last_occupied_row
    Best Regards,

    Carlos Paleo.

    To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.

    If Debugging is harder than writing a program and your code is as good as you can possibly make
    it, then by definition you're not smart enough to debug it.




    http://www.mugrs.org

  11. #11
    Thank you Carlos and Jake!

  12. #12
    Administrator
    VP-Knowledge Base VBAX Master
    Joined
    Jan 2005
    Location
    Porto Alegre - RS - Brasil
    Posts
    1,219
    Location
    You are welcome!
    Best Regards,

    Carlos Paleo.

    To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.

    If Debugging is harder than writing a program and your code is as good as you can possibly make
    it, then by definition you're not smart enough to debug it.




    http://www.mugrs.org

  13. #13
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    You're Welcome

    Take Care

Posting Permissions

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