Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting radio buttons (Xcl 2K, Win 2K)

    Edited by HansV to display options in a table

    I have two sets of radio buttons, each set in its own control box. Each selection in the first box has one or more eligible selections in the second box. The user must select one item in Box 1 and one item in Box 2 for the spreadsheet to work correctly. I currently list, in parentheses, the appropriate selections for Box 2 after each option in Box 1 (see below).

    The problem is that some users are still confused about which options in Box 2 are eligible selections for the option selected in Box 1. I would like to highlight the eligible options in Box 2 depending on which option is selected in Box 1. Is there a way to do that?

    <table border=1><td>Box 1</td><td>Box 2</td><td>Option 1 (A/[img]/forums/images/smilies/cool.gif[/img]</td><td>Option A</td><td>Option 2 (B/C)</td><td>Option B</td><td>Option 3 (A/C)</td><td>Option C</td><td>Option 4 (B/D)</td><td>Option D</td><td>Option 5 (E)</td><td>Option E</td></table>
    So, if Option 3 was selected in Box 1, Options A and C would become higlighted in Box 2.

    Looking forward to hearing from you.

    Douglas

  2. #2
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting radio buttons (Xcl 2K, Win 2K)

    Hi, you can uses the optionbutton's enabled property to set whether or not the option buttons are highlighted.

    on the click event of each of the option buttons in box 1 you could have something like:

    Private Sub OptionButton1_Click()
    With Me
    'set all to not enabled
    .OptionButtonA.Enabled = False
    .OptionButtonB.Enabled = False
    .OptionButtonC.Enabled = False
    .OptionButtonD.Enabled = False
    .OptionButtonE.Enabled = False

    'set correct options in this case A and B
    .OptionButtonA.Enabled = True
    .OptionButtonB.Enabled = True
    End With
    End Sub

    Hope this helps. I wasn't sure whether your option buttons were on a userform or just on a sheet.
    Thanks,

    pmatz

  3. #3
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting radio buttons (Xcl 2K, Win 2K)

    Your reply looks like it would work for my problem, but I am not sure how to implement it. I constructed my radio button set-up using the "Forms" toolbar. I don't know how to get to the "enabled" property area.

    Douglas

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formatting radio buttons (Xcl 2K, Win 2K)

    This will only work with the radio buttons from the Control Toolbox Toolbar. You cannot attach VBA code to the controls on the forms toolbar. Replace your controls using the Control Toolbox Toolbar and double click, placing the code in the click event of that control.
    Regards,
    Rudi

  5. #5
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting radio buttons (Xcl 2K, Win 2K)

    Ah, I'm sorry, I had thought you had used the Visual Basic Controls, not the Forms.

    Um, I dont think the forms controls have an enabled / disabled property. You can either use VB, or use another method of highlighting.

    I think if each of the radio buttons is in a cell you could colour this cell depending on which radio button was selected from the 1st column, but you wouldn't be able to stop other radio buttons from being selected.

    Could you paste a stripped down sheet of your project at all? Make sure all important info is removed 1st! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Thanks,

    pmatz

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting radio buttons (Xcl 2K, Win 2K)

    The buttons from the FORMS toolbar are very limited in the formatting. They tend to be easier to use than the ones in control toolbox, but much more limiting in terms of formatting and other controls and use.

    The only real advantage of the FORMS toolbar items is the ability to place them on a chart which allows comboboxes and options, etc to relate to be selectable directly from a chart sheet to make it more interactive

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting radio buttons (Xcl 2K, Win 2K)

    So, if I replace my "forms" radio buttons with "control" radion buttons, I would be able to use the posted script to highlight my options?

    Is there an easy way to make the switch?

    Douglas

  8. #8
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting radio buttons (Xcl 2K, Win 2K)

    Yes, if you changed the FORMS radio buttons to VBA radio buttons you can use the script.

    If you click View, Toolbars, Visual Basic this will bring up the Visual Basic toolbar, then you can click the controls button, and this should bring up the controls pallette, the radio button is on this. It is very similar to construct to the FORMS one, just draw it on the sheet.

    If you then right click the radio button on the sheet, you can go to Properties and change various properties of the individual control. One thing you can change is the property GroupName. This is how you group radio buttons together. So you can set the GroupName of radiobuttons 1 - 5 to 'Group1' for example, and for options A - E set the groupName to 'Group2'.

    If you right click on a radio button and goto View Code, then the code will defualt to a 'click event' - ie that will execute when the radio button is clicked. Here is where you can add the script ( you will have to amend it accordingly for each option button)
    Thanks,

    pmatz

  9. #9
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting radio buttons (Xcl 2K, Win 2K)

    OK, I've changed the FORMS radio buttons in Box 1 to VBA buttons. How do I set the linked cell for each button? I see the place in the properties box, but I have been unsuccessful at linking the button to my target cell (on another worksheet).

    I renamed all of the buttons in Box 1 with the same groupname. That should make them all of the same group so that only one button can be selected at a time - correct?

    Douglas

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting radio buttons (Xcl 2K, Win 2K)

    Right click and choose - view properties - linked cell enter the sheet name and cell for each one.

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting radio buttons (Xcl 2K, Win 2K)

    Thanks everyone. I believe I have it sorted out. The "unavailable" options in Box 2 now appear greyed out when one of the selections is made in Box 1. I had originally conceived of the reverse - the available options would be highlighted, but this effect is the same.

    How does one go about learning more about working with VBA? I have known about the more complex VBA buttons, but have not ventured there due to my lack of knowledge. All suggestions welcome.

    Douglas

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting radio buttons (Xcl 2K, Win 2K)

    Check out <post#=320321>post 320321</post#> it has links to excel and VB sources of information. There are some good vb articles linked.

    Steve

  13. #13
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting radio buttons (Xcl 2K, Win 2K)

    Glad you got it sorted dhfifield! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Its good to delve into VB, lots of possibilites. This site is a wonderful help too <img src=/S/chatter.gif border=0 alt=chatter width=38 height=16> - I learnt so much from the peeps here. Keep at it.
    Thanks,

    pmatz

Posting Permissions

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