|
Allowing only 1 check box to be ticked
I have 10 check boxes linked to formulas e.g., if link cell is true do this
etc. The problem is only 1 box should be ticked. If I tick another box it should become the active choice and deactivate the previous tick. Obviously check boxes can all be ticked as they are individual objects. Is there a way to make check boxes behave like option buttons (I cannot substitute option buttons as they do not provide true/false returns). BTW I realise (and know) there is probably a better way to achieve the result Im after, but there is a certain senior manager here who insists it can be done using check boxes! :-( cheers, Nadia |
Place them in a group box.
******************* ~Anne Troy www.OfficeArticles.com "Nadia" wrote in message ... I have 10 check boxes linked to formulas e.g., if link cell is true do this etc. The problem is only 1 box should be ticked. If I tick another box it should become the active choice and deactivate the previous tick. Obviously check boxes can all be ticked as they are individual objects. Is there a way to make check boxes behave like option buttons (I cannot substitute option buttons as they do not provide true/false returns). BTW I realise (and know) there is probably a better way to achieve the result Im after, but there is a certain senior manager here who insists it can be done using check boxes! :-( cheers, Nadia |
Ive tried that before. I can still tick all check boxes even when in a group
box. "Anne Troy" wrote: Place them in a group box. ******************* ~Anne Troy www.OfficeArticles.com "Nadia" wrote in message ... I have 10 check boxes linked to formulas e.g., if link cell is true do this etc. The problem is only 1 box should be ticked. If I tick another box it should become the active choice and deactivate the previous tick. Obviously check boxes can all be ticked as they are individual objects. Is there a way to make check boxes behave like option buttons (I cannot substitute option buttons as they do not provide true/false returns). BTW I realise (and know) there is probably a better way to achieve the result Im after, but there is a certain senior manager here who insists it can be done using check boxes! :-( cheers, Nadia |
Just wondering why you can't use option buttons?
They do not return a boolean as you noted, but the one that's selected returns a value (number). Biff "Nadia" wrote in message ... Ive tried that before. I can still tick all check boxes even when in a group box. "Anne Troy" wrote: Place them in a group box. ******************* ~Anne Troy www.OfficeArticles.com "Nadia" wrote in message ... I have 10 check boxes linked to formulas e.g., if link cell is true do this etc. The problem is only 1 box should be ticked. If I tick another box it should become the active choice and deactivate the previous tick. Obviously check boxes can all be ticked as they are individual objects. Is there a way to make check boxes behave like option buttons (I cannot substitute option buttons as they do not provide true/false returns). BTW I realise (and know) there is probably a better way to achieve the result Im after, but there is a certain senior manager here who insists it can be done using check boxes! :-( cheers, Nadia |
Exactly my thought Biff, as I mentioned, I have a senior manager who "knows
better". Will suggest he does it himself! cheers, Nadia "Biff" wrote: Just wondering why you can't use option buttons? They do not return a boolean as you noted, but the one that's selected returns a value (number). Biff "Nadia" wrote in message ... Ive tried that before. I can still tick all check boxes even when in a group box. "Anne Troy" wrote: Place them in a group box. ******************* ~Anne Troy www.OfficeArticles.com "Nadia" wrote in message ... I have 10 check boxes linked to formulas e.g., if link cell is true do this etc. The problem is only 1 box should be ticked. If I tick another box it should become the active choice and deactivate the previous tick. Obviously check boxes can all be ticked as they are individual objects. Is there a way to make check boxes behave like option buttons (I cannot substitute option buttons as they do not provide true/false returns). BTW I realise (and know) there is probably a better way to achieve the result Im after, but there is a certain senior manager here who insists it can be done using check boxes! :-( cheers, Nadia |
Is there a way to make check boxes behave like option buttons (I cannot
substitute option buttons as they do not provide true/false returns). There are two types of option buttons. controls and forms The ones that are created using the Controls toolbar do give a direct true false return to a linked cell. The properties allow you to set groups up. The ones created using the Forms toolbar return a number as you have identified. you could then extract the true false using =$A$1=1 =$A$1=2... in your linked formulae Obviously if the need for a tick box is purely asthetic then I concur with you view of your boss. hth RES |
But you could use option buttons and just adjust the formula:
=if(linkedcell=1, =if(linkedcell=2, Instead of true/falses. But you could have code that turns off the other checkboxes when you check any of them... I used checkboxes from the Forms toolbar so that I could assign the same macro to each. I put 10 checkboxes from the Forms toolbar on a worksheet. Then I assigned this macro to each of them: Option Explicit Sub testme() Dim ThisCBX As CheckBox Dim CBX As CheckBox Set ThisCBX = ActiveSheet.CheckBoxes(Application.Caller) If ThisCBX.Value = xlOn Then For Each CBX In ActiveSheet.CheckBoxes If CBX.Name = ThisCBX.Name Then 'do nothing Else CBX.Value = xlOff End If Next CBX End If End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Nadia wrote: I have 10 check boxes linked to formulas e.g., if link cell is true do this etc. The problem is only 1 box should be ticked. If I tick another box it should become the active choice and deactivate the previous tick. Obviously check boxes can all be ticked as they are individual objects. Is there a way to make check boxes behave like option buttons (I cannot substitute option buttons as they do not provide true/false returns). BTW I realise (and know) there is probably a better way to achieve the result Im after, but there is a certain senior manager here who insists it can be done using check boxes! :-( cheers, Nadia -- Dave Peterson |
Thank you Dave and Robert,
I guess it really is for aesthetic reasons... but as they say... anything to keep the boss happy! many thanks :) cheers, Nadia "Dave Peterson" wrote: But you could use option buttons and just adjust the formula: =if(linkedcell=1, =if(linkedcell=2, Instead of true/falses. But you could have code that turns off the other checkboxes when you check any of them... I used checkboxes from the Forms toolbar so that I could assign the same macro to each. I put 10 checkboxes from the Forms toolbar on a worksheet. Then I assigned this macro to each of them: Option Explicit Sub testme() Dim ThisCBX As CheckBox Dim CBX As CheckBox Set ThisCBX = ActiveSheet.CheckBoxes(Application.Caller) If ThisCBX.Value = xlOn Then For Each CBX In ActiveSheet.CheckBoxes If CBX.Name = ThisCBX.Name Then 'do nothing Else CBX.Value = xlOff End If Next CBX End If End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Nadia wrote: I have 10 check boxes linked to formulas e.g., if link cell is true do this etc. The problem is only 1 box should be ticked. If I tick another box it should become the active choice and deactivate the previous tick. Obviously check boxes can all be ticked as they are individual objects. Is there a way to make check boxes behave like option buttons (I cannot substitute option buttons as they do not provide true/false returns). BTW I realise (and know) there is probably a better way to achieve the result Im after, but there is a certain senior manager here who insists it can be done using check boxes! :-( cheers, Nadia -- Dave Peterson |
Allowing only 1 check box to be ticked
Hi Dave - how easy is it to change your code so only the active row is
evaluated rather than the activesheet. In other words I have 4 check boxes (from the forms toolbar - not using option buttons for aesthetic reasons) on each row (about 40 rows) and only want the user to be able to check only one box on each row. I tried to edit your macro but didn't have any luck. By the way, I added the the worksheet using another macro you posted - thanks! Andrew "Dave Peterson" wrote: But you could use option buttons and just adjust the formula: =if(linkedcell=1, =if(linkedcell=2, Instead of true/falses. But you could have code that turns off the other checkboxes when you check any of them... I used checkboxes from the Forms toolbar so that I could assign the same macro to each. I put 10 checkboxes from the Forms toolbar on a worksheet. Then I assigned this macro to each of them: Option Explicit Sub testme() Dim ThisCBX As CheckBox Dim CBX As CheckBox Set ThisCBX = ActiveSheet.CheckBoxes(Application.Caller) If ThisCBX.Value = xlOn Then For Each CBX In ActiveSheet.CheckBoxes If CBX.Name = ThisCBX.Name Then 'do nothing Else CBX.Value = xlOff End If Next CBX End If End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Nadia wrote: I have 10 check boxes linked to formulas e.g., if link cell is true do this etc. The problem is only 1 box should be ticked. If I tick another box it should become the active choice and deactivate the previous tick. Obviously check boxes can all be ticked as they are individual objects. Is there a way to make check boxes behave like option buttons (I cannot substitute option buttons as they do not provide true/false returns). BTW I realise (and know) there is probably a better way to achieve the result Im after, but there is a certain senior manager here who insists it can be done using check boxes! :-( cheers, Nadia -- Dave Peterson |
Allowing only 1 check box to be ticked
First, I think you're going to be confusing your users by using checkboxes
instead of optionbuttons. Most users understand the difference between checkboxes and optionbuttons. But you can't just use: dim CBX as checkbox for each cbx in activecell.entirerow.checkboxes This won't work. But you can cycle through all the checkboxes and look at the position of each checkbox. For each cbx in activesheet.checkboxes if cbx.topleftcell.row = activecell.row then 'do what you want... end if next cbx But I wouldn't do this if I were you. I'd use the optionbuttons. Andrew wrote: Hi Dave - how easy is it to change your code so only the active row is evaluated rather than the activesheet. In other words I have 4 check boxes (from the forms toolbar - not using option buttons for aesthetic reasons) on each row (about 40 rows) and only want the user to be able to check only one box on each row. I tried to edit your macro but didn't have any luck. By the way, I added the the worksheet using another macro you posted - thanks! Andrew "Dave Peterson" wrote: But you could use option buttons and just adjust the formula: =if(linkedcell=1, =if(linkedcell=2, Instead of true/falses. But you could have code that turns off the other checkboxes when you check any of them... I used checkboxes from the Forms toolbar so that I could assign the same macro to each. I put 10 checkboxes from the Forms toolbar on a worksheet. Then I assigned this macro to each of them: Option Explicit Sub testme() Dim ThisCBX As CheckBox Dim CBX As CheckBox Set ThisCBX = ActiveSheet.CheckBoxes(Application.Caller) If ThisCBX.Value = xlOn Then For Each CBX In ActiveSheet.CheckBoxes If CBX.Name = ThisCBX.Name Then 'do nothing Else CBX.Value = xlOff End If Next CBX End If End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Nadia wrote: I have 10 check boxes linked to formulas e.g., if link cell is true do this etc. The problem is only 1 box should be ticked. If I tick another box it should become the active choice and deactivate the previous tick. Obviously check boxes can all be ticked as they are individual objects. Is there a way to make check boxes behave like option buttons (I cannot substitute option buttons as they do not provide true/false returns). BTW I realise (and know) there is probably a better way to achieve the result Im after, but there is a certain senior manager here who insists it can be done using check boxes! :-( cheers, Nadia -- Dave Peterson -- Dave Peterson |
Allowing only 1 check box to be ticked
Thanks for the info and advice. I agree with you on the option buttons vs.
check boxes, but I really don't think I get a choice in the matter. Andrew "Dave Peterson" wrote: First, I think you're going to be confusing your users by using checkboxes instead of optionbuttons. Most users understand the difference between checkboxes and optionbuttons. But you can't just use: dim CBX as checkbox for each cbx in activecell.entirerow.checkboxes This won't work. But you can cycle through all the checkboxes and look at the position of each checkbox. For each cbx in activesheet.checkboxes if cbx.topleftcell.row = activecell.row then 'do what you want... end if next cbx But I wouldn't do this if I were you. I'd use the optionbuttons. Andrew wrote: Hi Dave - how easy is it to change your code so only the active row is evaluated rather than the activesheet. In other words I have 4 check boxes (from the forms toolbar - not using option buttons for aesthetic reasons) on each row (about 40 rows) and only want the user to be able to check only one box on each row. I tried to edit your macro but didn't have any luck. By the way, I added the the worksheet using another macro you posted - thanks! Andrew "Dave Peterson" wrote: But you could use option buttons and just adjust the formula: =if(linkedcell=1, =if(linkedcell=2, Instead of true/falses. But you could have code that turns off the other checkboxes when you check any of them... I used checkboxes from the Forms toolbar so that I could assign the same macro to each. I put 10 checkboxes from the Forms toolbar on a worksheet. Then I assigned this macro to each of them: Option Explicit Sub testme() Dim ThisCBX As CheckBox Dim CBX As CheckBox Set ThisCBX = ActiveSheet.CheckBoxes(Application.Caller) If ThisCBX.Value = xlOn Then For Each CBX In ActiveSheet.CheckBoxes If CBX.Name = ThisCBX.Name Then 'do nothing Else CBX.Value = xlOff End If Next CBX End If End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Nadia wrote: I have 10 check boxes linked to formulas e.g., if link cell is true do this etc. The problem is only 1 box should be ticked. If I tick another box it should become the active choice and deactivate the previous tick. Obviously check boxes can all be ticked as they are individual objects. Is there a way to make check boxes behave like option buttons (I cannot substitute option buttons as they do not provide true/false returns). BTW I realise (and know) there is probably a better way to achieve the result Im after, but there is a certain senior manager here who insists it can be done using check boxes! :-( cheers, Nadia -- Dave Peterson -- Dave Peterson |
Allowing only 1 check box to be ticked
Hi Dave, I'm using Excel 2007, and new to using macros, and wondered how to do your suggestion for multiple options on the same row. Only want one to be allowed and then calculated using the options button (and are you using the option button on forms or under active x control?) Thanks for any help you can give. EJ P.S. I realize this is an old post but maybe you or someone else will still be able to help. "Dave Peterson" wrote: First, I think you're going to be confusing your users by using checkboxes instead of optionbuttons. Most users understand the difference between checkboxes and optionbuttons. But you can't just use: dim CBX as checkbox for each cbx in activecell.entirerow.checkboxes This won't work. But you can cycle through all the checkboxes and look at the position of each checkbox. For each cbx in activesheet.checkboxes if cbx.topleftcell.row = activecell.row then 'do what you want... end if next cbx But I wouldn't do this if I were you. I'd use the optionbuttons. Andrew wrote: Hi Dave - how easy is it to change your code so only the active row is evaluated rather than the activesheet. In other words I have 4 check boxes (from the forms toolbar - not using option buttons for aesthetic reasons) on each row (about 40 rows) and only want the user to be able to check only one box on each row. I tried to edit your macro but didn't have any luck. By the way, I added the the worksheet using another macro you posted - thanks! Andrew "Dave Peterson" wrote: But you could use option buttons and just adjust the formula: =if(linkedcell=1, =if(linkedcell=2, Instead of true/falses. But you could have code that turns off the other checkboxes when you check any of them... I used checkboxes from the Forms toolbar so that I could assign the same macro to each. I put 10 checkboxes from the Forms toolbar on a worksheet. Then I assigned this macro to each of them: Option Explicit Sub testme() Dim ThisCBX As CheckBox Dim CBX As CheckBox Set ThisCBX = ActiveSheet.CheckBoxes(Application.Caller) If ThisCBX.Value = xlOn Then For Each CBX In ActiveSheet.CheckBoxes If CBX.Name = ThisCBX.Name Then 'do nothing Else CBX.Value = xlOff End If Next CBX End If End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Nadia wrote: I have 10 check boxes linked to formulas e.g., if link cell is true do this etc. The problem is only 1 box should be ticked. If I tick another box it should become the active choice and deactivate the previous tick. Obviously check boxes can all be ticked as they are individual objects. Is there a way to make check boxes behave like option buttons (I cannot substitute option buttons as they do not provide true/false returns). BTW I realise (and know) there is probably a better way to achieve the result Im after, but there is a certain senior manager here who insists it can be done using check boxes! :-( cheers, Nadia -- Dave Peterson -- Dave Peterson |
Allowing only 1 check box to be ticked
If you only one a single selection in a group of choices, then the control you
should use is the optionbutton. That's one of its built-in features and the typical user will have seen these and know how they work. If you haven't designed your "form", you could use the macro at Debra Dalgleish's site. It creates multiple optionbuttons from the Forms toolbar (not the control toolbox toolbar) and puts each group inside a groupbox. http://contextures.com/xlForm01.html If you really, really wanted to use optionbuttons from the control toolbox toolbar, it's possible--but I wouldn't recommend it. For lots of controls on a worksheet, I've always found the controls from the Forms toolbar much better behaved. BunnyHop wrote: Hi Dave, I'm using Excel 2007, and new to using macros, and wondered how to do your suggestion for multiple options on the same row. Only want one to be allowed and then calculated using the options button (and are you using the option button on forms or under active x control?) Thanks for any help you can give. EJ P.S. I realize this is an old post but maybe you or someone else will still be able to help. "Dave Peterson" wrote: First, I think you're going to be confusing your users by using checkboxes instead of optionbuttons. Most users understand the difference between checkboxes and optionbuttons. But you can't just use: dim CBX as checkbox for each cbx in activecell.entirerow.checkboxes This won't work. But you can cycle through all the checkboxes and look at the position of each checkbox. For each cbx in activesheet.checkboxes if cbx.topleftcell.row = activecell.row then 'do what you want... end if next cbx But I wouldn't do this if I were you. I'd use the optionbuttons. Andrew wrote: Hi Dave - how easy is it to change your code so only the active row is evaluated rather than the activesheet. In other words I have 4 check boxes (from the forms toolbar - not using option buttons for aesthetic reasons) on each row (about 40 rows) and only want the user to be able to check only one box on each row. I tried to edit your macro but didn't have any luck. By the way, I added the the worksheet using another macro you posted - thanks! Andrew "Dave Peterson" wrote: But you could use option buttons and just adjust the formula: =if(linkedcell=1, =if(linkedcell=2, Instead of true/falses. But you could have code that turns off the other checkboxes when you check any of them... I used checkboxes from the Forms toolbar so that I could assign the same macro to each. I put 10 checkboxes from the Forms toolbar on a worksheet. Then I assigned this macro to each of them: Option Explicit Sub testme() Dim ThisCBX As CheckBox Dim CBX As CheckBox Set ThisCBX = ActiveSheet.CheckBoxes(Application.Caller) If ThisCBX.Value = xlOn Then For Each CBX In ActiveSheet.CheckBoxes If CBX.Name = ThisCBX.Name Then 'do nothing Else CBX.Value = xlOff End If Next CBX End If End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Nadia wrote: I have 10 check boxes linked to formulas e.g., if link cell is true do this etc. The problem is only 1 box should be ticked. If I tick another box it should become the active choice and deactivate the previous tick. Obviously check boxes can all be ticked as they are individual objects. Is there a way to make check boxes behave like option buttons (I cannot substitute option buttons as they do not provide true/false returns). BTW I realise (and know) there is probably a better way to achieve the result Im after, but there is a certain senior manager here who insists it can be done using check boxes! :-( cheers, Nadia -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 06:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com