Excel - Yes No Checkbox

[AlreadyLost]

I have an excel sheet and currently the user can check both the No and Yes Check box. I want to disable one or the other if I check Yes then I should not be able to check no. Any ideas? Thanks

 

[xlhelp]

You could do that with Option buttons easily. You would create two option buttons and through properties, group them together.

I thought the idea of check box was to have a Yes/No scenario. If it is checked it is Yes and if not NO.

 

[firefytr]

Or if you are using a Forms object (option button), put them in a group box and this will only allow one selection per group box.

HTH

-----------
Regards,
Zack Barresse

 

[fumei]

Having checkable Yes AND No defeats the purpose. Option buttons (grouped) are good if you are using ActiveX.

Gerry

 

[AlreadyLost]

I am using someone's exsiting excel sheet and there are macro that does something when I select Yes or No. Is Option button the only option?

 

[firefytr]

Option buttons are the *logical* option. Anything else will be a hassle and a workaround, and I wouldn't recommend it.

-----------
Regards,
Zack Barresse

 

[AlreadyLost]

Anybody know how to write the macro code?

 

[sfatz]

One way is to put code in the Change event for each control.
In the Yes check box ...
chkNo.Value = Not chkYes.Value
In the No check box ...
chkYes.Value = Not chkNo.Value
The above assumes ActiveX controls and that the controls are named chkYes and chkNo for the Yes checkbox and No checkbox respectively.

 

[firefytr]

Awww, it's like a greek tragedy.. replacing native functionality with slow, obtuse and uneeded VBA code.. If you can find a way it would certainly be worth it.

-----------
Regards,
Zack Barresse

 

[AlreadyLost]

Okay. I am new at this. I have a list of questions on my Excel sheet and next to the question are two checkboxs (YEs, No). So where is this control that I suppose to put the code in?

 

[firefytr]

If you are really intent on doing this (much to my very, very STRONG disagreement) and you are using ActivX controls (from the Controls Toolbox) then you can right click the object and select View Code.

-----------
Regards,
Zack Barresse

 

[AlreadyLost]

If I have to recreat the YES/NO checkbox to Option buttons, I have lots of sheets and lots of code that I have to modify cause once they check YES, it a code calcualte some number on the front page of the sheet.

Anyway, can I write a code withing the Checkbox? When I right click on the check box, I have assign macro, should I use this code in there?

chkNo.Value = Not chkYes.Value

 

[AlreadyLost]

Here is what I have..CheckBox2 is YES and CheckBox46 is No. Is this wrong. Thanks for helping. I know I must be asking some stupid questions so I appreciate your help.

Sub CheckBox2_Click()
CheckBox46.Value = Not CheckBox2.Value

End Sub

 

[firefytr]

You have to code both buttons ...

Private Sub CheckBox1_Click()
    CheckBox1.Value = IIf(CheckBox2.Value, True, False)
    CheckBox2.Value = IIf(CheckBox1.Value, False, True)
End Sub

Private Sub CheckBox2_Click()
    CheckBox2.Value = IIf(CheckBox1.Value, True, False)
    CheckBox1.Value = IIf(CheckBox2.Value, False, True)
End Sub

This will end up with a lot of code if you have many sets of checkboxes..

-----------
Regards,
Zack Barresse

 

[AlreadyLost]

Doesn't work. I must be doing something wrong. I use the same code. and got an error. It highlight" CheckBox1.Value = IIf(CheckBox2.Value, True, False)" in yellow.

 

[AlreadyLost]

I think I found the error. Hold On. Will ask question if still not resolved. Thanks again

 

[AlreadyLost]

Okay. I got an error " Object Required".

 

[sfatz]

As firefytr pointed out, you are trying to fix something that is awkwardly done with methods that are even more awkward. The code that I suggested assumed that you didn't have many controls to update. While it is not the "proper" way to handle yes/no choices, it is a simple 'fix'. If you are going to go to the effort of fixing this spreadsheet, then why not do it properly by utilizing the capabilities of the program.

A yes/no selection is best done with one (1) check box where a check in the box indicates "Yes". Since the spreadsheet has two checkboxes,

I think we can better help you if you are able to post the spreadsheet. Then we can look at the existing condition of the spreadsheet and the code behind the checkboxes. With that information, we should be better able to advise you on the best or easiest method to achieve the results that you are looking for.

 

[firefytr]

The code I posted works wonderfully for me. Ensure the controls names are correct.

-----------
Regards,
Zack Barresse

 

[sfatz]

The code that I posted will also work. In essence, when you click on a checkbox, my line of code will set the other checkbox to be the opposite of the checkbox that you just clicked on. It works like a toggle switch. When you click on a checkbox, it will change it's setting (True becomes False, False becomes True) and then change the other setting on the other checkbox accordingly.

If you have more than two checkboxes, then I think firefytr's code would be more appropriate. His code assumes that when you click on a checkbox, you want that checkbox to be selected (set to True), and then all the other checkboxes get set to be unselected.

Both code examples work for me on my computer. As firefytr said, make sure that the names you use in your code agree with the names of the checkboxes