+ Reply to Thread
Results 1 to 25 of 25

Use of Option / Radio buttons

  1. #1
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Use of Option / Radio buttons

    I want to insert 4 sets of 3 option radio buttons (not tick boxes) in adjacent cells such that each "set" acts independently of the others. Expressed differently: Such that (just) one of 3 options can be selected in each one of the 4 sets.
    My attempts so far result in only one button being selectable at a time from the whole group of 12. I thought I could get round this by inserting 4 individual "groups" [in "developer mode"] but that doesn't seem to work. I am using Excel 2013 at work and 2003 at home. I suspect that there is a simple solution which escapes me so would appreciate any tips. Tks.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Use of Option / Radio buttons

    You need a 'Group Control Box' (from the same insert menu, it's the square with xyz at the top).

    Draw one of these boxes around each set of option buttons and problem solved.

  3. #3
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83
    That's what I did!

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Use of Option / Radio buttons

    Did you do it for all of the radio buttons and are the boxes big enough to completely enclose the buttons and their labels?
    If you ctrl click the buttons to show the object boundaries, they should be completely within the box.

    I've just tried it and it works fine, but it does appear to go wrong if you draw the box too small, then resize it.

    Drawing the box manually from the insert menu seems more reliable than using the Group option from the right click context menu.

  5. #5
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: Use of Option / Radio buttons

    Since last exchange of msgs I have been working along similar lines, a messy and fiddly operation with
    the group boxes which now enclose all the radio option boxes within each of the 4 "sets". The net
    result is much as before: only one button from the whole number in the spreadsheet (12) will "activate"
    at any one time, rather than one within each set of 3 which is / was my aim. There appears to be a
    right click "Grouping" option but this is greyed out [I used the Insert option].

    Ideally the default would be for no one radio box to be "ticked" until the user makes an entry
    but this is perhaps asking too much.

    I am obviously missing a trick somewhere. Otherwise I have to query just what effect "grouping" has.
    It may be that I am falling into the box resize trap but cannot see a way round this if what appears
    initially is too small.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Use of Option / Radio buttons

    What kind of optionbuttons are you using - Form or ActiveX?
    Rory

  7. #7
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: Use of Option / Radio buttons

    Form as per Insert menu. ActiveX is above my pay grade / understanding.

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Use of Option / Radio buttons

    Can you post a copy of the workbook (remove any data if it's confidential)?

  9. #9
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: Use of Option / Radio buttons

    Attached is the non confidential part of the spreadsheet where the problem arises.

    [I hope the Manage Attachments system has worked. Difficult to be certain]
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Use of Option / Radio buttons

    Referring back to my comments in post #4, I did notice problems when moving / resizing existing buttons / boxes that were already not working properly. Deleting them and starting again, building one group at a time appeared to work without issue.

  11. #11
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Use of Option / Radio buttons

    Your buttons are still overhanging the boxes, that is why it is not working.

    Right click any button and you will see that the button label is sticking way out to the right. Look at the screen snip below, the 'external' box is how you have it, the 'internal' box shows how it should be (although a bit untidy).Telegraph Sam.PNG

    edit:- note that by fixing one of the boxes, I have been able to select an option in each of the 2 groups.

  12. #12
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: Use of Option / Radio buttons

    Looks like the rule is that you can make the group box boundaries any size within reason so long as they do not encroach on the neighboring one. Will experiment further ..

  13. #13
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Use of Option / Radio buttons

    That is pretty much it, but also the option buttons cannot leave the boundary of the group box, (as with external in the screen snip above) in this case they are grouped to the sheet, not the box.

    Although the button appears to be within the box, the button label is overhanging, as you're leaving these blank, you can shrink them down to almost nothing.
    Last edited by jason.b75; 03-25-2019 at 09:28 AM.

  14. #14
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: Use of Option / Radio buttons

    It doesn't seem to be at all simple how to edit / adjust the box as opposed to the button. And can one get the box borders to disappear when all is done, for the sake of appearances?

  15. #15
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Use of Option / Radio buttons

    Something like this? It's not perfect, you might need to do some fine tuning.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: Use of Option / Radio buttons

    That would suit the bill very nicely thank you though I'm at a loss how you did it. It has (inevitably) thrown up a further aspect: If in any one set of 3 I want NO button to be selected then I suppose the only way would be to add an additional row entitled "Not Applicable"? I will try this but may have to revert for help in fine tuning.

  17. #17
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: Use of Option / Radio buttons

    As before I am having difficulty in editing and removing the boxes and would appreciate knowing what the secret is. Does it matter whether or not the Developer tab is open?

  18. #18
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: Use of Option / Radio buttons

    This is where I have got to. The buttons in the Not Applicable row are "on" permanently whereas
    I had intended to include them as on-off toggles within the relevant groups above,and then remove
    the visible box borders which don't do much for the appearance.
    Attached Files Attached Files

  19. #19
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Use of Option / Radio buttons

    I think that because you didn't expand the original boxes that I used, the new ones were being isolated from the rest.

    That's my fault for making them almost invisible, I resized them so that the edge of the box blends in with the border of the 3 cells that contain the group of buttons.

    I've just tried to resize the ones that you added after and I'm having problems with them as well now Will return when I find the cause, this could take a while!

  20. #20
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Use of Option / Radio buttons

    Quote Originally Posted by Telegraph Sam View Post
    As before I am having difficulty in editing and removing the boxes and would appreciate knowing what the secret is. Does it matter whether or not the Developer tab is open?
    I missed this reply earlier, in answer to the developer tab, it makes no difference.

    As far as editing goes, the group boxes are a real pain, when you first draw them, they have a text label in the top left corner. Do not delete this until you have moved and resized the box as needed. The label gives you a bigger target to click on in order to select the box. I found it easier to do these manually, but you could experiment with the methods that I've set out below for the option buttons.

    Key point here, 1 box per group of buttons, 2 or more boxes overlapping boxes overlapping the same button(s) appear to be the cause of the problems you had with your latest attempt.

    For the option buttons to get them all the same size and all in a straight line.

    Size:- Ctrl click all of the buttons, so that they are all selected (when you can see the white dots to grab and resize them).
    Click on 'Drawing Tools' (Format) tab in the Excel ribbon, then look for the 2 white boxes on the far right, set the height (top box) and width (bottom box) here using the arrows. For your file, I used Height 0.5 cm and Width 0.6 cm.

    Alignment:- After setting the size, click away from the option buttons to deselect the current selection, then ctrl click to select all of the buttons in 1 row.
    Click on 'Drawing Tools' (Format) tab then look for the 'Align' dropdown next to the size boxes that you used earlier. Choose 'Align Middle' from the dropdown.
    Repeat these steps for each row, remember to click away and deselct each time.

    If the buttons start to creep down in the lower rows, you can drag them back up slightly by hand before deslecting, the alignment works to get them all in a straight line, but it's not perfect in finding the middle of the cell.

    Repeat the above steps, selecting the buttons in 1 column instead of 1 row, using 'Align Center' in the Alignment dropdown box.

    Hope that all makes sense. This appears to be something that you need to get right first try, if it goes wrong, fixing it is far from easy. With the attached file, I had to delete all of the group boxes and start again, resizing them was not working correctly.
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: Use of Option / Radio buttons

    Many thanks for your detailed explanation - in the absence of adequate guidance from the Help file.
    This is(presumably) what help forums are all about.

    There is however one "option" which you failed to mention: Copying and pasting the results of your
    own work into my spreadsheet!! Might be a cop out but hugely easier than fiddling around with borders
    and buttons and things. My mistake was apparently to delete the title of the box at the very
    rather than leave it until I had got the sizing and positioning right.

    Needless to say I will keep your instructions on file for future reference.

  22. #22
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: Use of Option / Radio buttons

    Copying and pasting is not as straightforward as I assumed. The result is attached as a screendump
    which for some reason is more accurate than creating a new spreadsheet extract. The bottom border
    of the boxes seems to be totally fixed regardless of what formatting is done in the background, and
    you can see that the line intrudes into the row below. I can only get round this by creating a
    dummy blank row underneath whose border is adjusted to coincide with and "hide" the box border.

    How can I get rid of the mini boxes that have now re-appeared round the individual buttons?

    Tks
    Attached Files Attached Files

  23. #23
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Use of Option / Radio buttons

    The boxes can be resized, but you would need to select them individually, or all 4 at once with ctrl click, so that the 'grab points' appear, but without the buttons selected.

    I have no idea how you got those little boxes to show up, I just tried copying from the file in post #20 and I'm not getting them.

    They look like checkboxes, but there are none of those in the sheet

  24. #24
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Use of Option / Radio buttons

    You could also use code. Delete any existing controls, then select cells E5:H8 in your sample workbook and run this:

    Please Login or Register  to view this content.

  25. #25
    Registered User
    Join Date
    04-01-2010
    Location
    Chester-le-Street, England
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: Use of Option / Radio buttons

    Coding looks rather cool but a bit above my pay grade. It would however probably get round the problem that it seems to be very difficult, manually, to select the box borders (with grab points) as distinct from the cell borders. But I think I can live with my blank line fiddle for the present at least.

    Having realized that what I thought were button borders were in fact check boxes I found that these were somehow relics of the previous spreadsheet which had used tick boxes before the spec was upgraded to option boxes. After deleting everything and repasting, the final result appears very acceptable thank you. Until something changes ..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Filter Spreadsheet Using Radio/Option Buttons
    By Jim Clayton in forum Excel General
    Replies: 3
    Last Post: 05-14-2018, 05:32 PM
  2. How can I insert multiple sets of option buttons (radio buttons) in Excel 2010?
    By mickwooduclan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-25-2013, 10:01 AM
  3. Excel 2007 : Help with option/radio buttons
    By Twilock in forum Excel General
    Replies: 0
    Last Post: 07-12-2012, 09:14 PM
  4. change values output by option/radio buttons
    By cowboy713 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 12-09-2010, 09:42 PM
  5. Evaluation with Radio buttons and Option buttons
    By soph0101 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-15-2010, 12:23 PM
  6. Protecting a sheet with radio/option buttons
    By Erik.W in forum Excel General
    Replies: 0
    Last Post: 04-24-2007, 11:32 AM
  7. Grouping radio option buttons
    By ahmed in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-01-2006, 06:15 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1