INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Excel - Yes No Checkbox

Excel - Yes No Checkbox

(OP)
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

RE: Excel - Yes No Checkbox

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.

RE: Excel - Yes No Checkbox

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

RE: Excel - Yes No Checkbox

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

Gerry

RE: Excel - Yes No Checkbox

(OP)
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?

RE: Excel - Yes No Checkbox

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

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

RE: Excel - Yes No Checkbox

(OP)
Anybody know how to write the macro code?

RE: Excel - Yes No Checkbox

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.

RE: Excel - Yes No Checkbox

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

RE: Excel - Yes No Checkbox

(OP)
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?

RE: Excel - Yes No Checkbox

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

RE: Excel - Yes No Checkbox

(OP)
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

RE: Excel - Yes No Checkbox

(OP)
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

RE: Excel - Yes No Checkbox

You have to code both buttons ...

CODE

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

RE: Excel - Yes No Checkbox

(OP)
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.

RE: Excel - Yes No Checkbox

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

RE: Excel - Yes No Checkbox

(OP)
Okay. I got an error " Object Required".

RE: Excel - Yes No Checkbox

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.

RE: Excel - Yes No Checkbox

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

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

RE: Excel - Yes No Checkbox

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

RE: Excel - Yes No Checkbox

(OP)
How do I post my spreadsheet? So you guys can assist?

RE: Excel - Yes No Checkbox

You must upload it to a hosting site/server, this board will not let you upload attachments directly to it.

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

RE: Excel - Yes No Checkbox

(OP)
I don't know one hosting site nor have one. :(

What is the purpose of a Format Control "Cell Link" the Yes/No checkbox have a cell link.

RE: Excel - Yes No Checkbox

The purpose is so that you can check the value of the control with worksheet formulae.  It returns a Boolean True/False record of the controls status.  Basically, you can use an IF function (primarily used) to base calculations on such a state.

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

RE: Excel - Yes No Checkbox

(OP)
Since I can't post the spreadsheet.

Here is something else this yes no checkbox does. When I select Yes, there is an hide column (Cell Link in Format Control) that change it to True. When I deselect it , it change to False. This goes the same as the No checkbox.

Could this be the reason why the code does not worK?

RE: Excel - Yes No Checkbox

No, that has no affect on any code, that is strictly your CellLink property.

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

RE: Excel - Yes No Checkbox

I accidentally clicked on the View Code menu that comes up when you right click on the name of a tab.

Now my main worksheet is 15% heavier, and every time I open it  there is a message asking if I want to disable macros.

At first I was scared there was a virus, but then I found this is the behavior on any sheet where you choose to View Code.

Is there a way to undo this and take it back to its pristine status quo ante?

RE: Excel - Yes No Checkbox

Yes.

Right click the control
Select View Code
Delete all code you see
Close the VBE window
Save workbook

You should have no more prompts; this is assuming there is no other code, modules, forms, etc in your workbook.

HTH

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

RE: Excel - Yes No Checkbox

Thanks, I shoulda thought of that.

RE: Excel - Yes No Checkbox

(OP)
Bear with me everyone for helping. I just found out that my checkbox are FORM checkbox. Is that why the code does not work that someone suggested does not work?

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

RE: Excel - Yes No Checkbox

Yes, that is why the code does not work.  They are part of the Shapes collection.

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

RE: Excel - Yes No Checkbox

I think this has gotten needlessly confusing.  Probably what you want is a Control Toolbox checkbox.  Linked to the cell you want to reflect "yes" or "no".  When you check the box, the linked cell shows "true", unchecked it shows "false".  There is only one checkbox.  Radio boxes require two boxes, one for yes, one for no.  With all boxes you usually must convert the "true" to "YES", etc. An "IF" statement will do that.  

RE: Excel - Yes No Checkbox

Needlessly confusing?  Of course it has!  This could be settled if the OP put a group box around them and be done with it.  No code needed whatsoever.

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

RE: Excel - Yes No Checkbox

I'm not kicking a dead horse, but why a group box if the single check box does it?  No code, unless an IF statement is too simple.

RE: Excel - Yes No Checkbox

Single checkbox, radio boxes in a group box, it's all the same.  My point is that it's much more efficient than trying to code a solution.  :)

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

RE: Excel - Yes No Checkbox

Agreed!  Amazing how many people insist on using code where a simple statement or function will do the trick.  Is it some kind of elitism?

RE: Excel - Yes No Checkbox

I think it's generally more of people not wanting to change a workbook structure; native functionality and spreadsheet adaptation versus keeping the structure they (or their suprevisors/bosses) want.  Spreadsheet design is (IMHO) the #1 killer of the application; it leads to so many other problems and is hard to create around.

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

RE: Excel - Yes No Checkbox

(OP)
Ok. you experts are confusing me. So now what. It sounds like I don't need to code my checkbox right? It sounds like there is an easy fix to the Yes No Checkbox, correct? Let me restate my problem.
I have sheets that display a questions with FORM Yes No Checkbox (with Cell link that changes from False to True of checked). Now, I can select both yes and no. I want to limit the ability to check on or the other.

What is the easier way to do this without having to redo all the sheets again without modifing much effort?

RE: Excel - Yes No Checkbox

I still stand by my original recommendation of getting the dirty work over and done with by replacing the checkboxes for radio buttons putting them in group boxes.

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

RE: Excel - Yes No Checkbox

Btw, the cell link on Forms Option buttons, you only need to link one of the buttons and all in that group box will report (as an integer value) as to which is selected (i.e. 1, 2, 3, 4, etc).

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

RE: Excel - Yes No Checkbox

AlreadyLost, the  important thing is you don't need code.  You can use a check box or option (radio) boxes, either Forms or Control Toolbox.  If you want the user to indicate yes or no, a single check box will do it.  If checked, the value in the linked cell is true, if unchecked, false.  You can convert the true/false value to yes/no with an IF statement.  If you want the user to affirmatively indicate "yes" or "no", the option box is the way to go.  If grouped, only one option box will give a value.  Option boxes are a bit more complex than check boxes, since Form option boxes return numerical values (1,2,3,etc) As previously suggested, if all you want is a yes or no in the linked cell, a single check box will do it. No code needed.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close