Smart questions
Smart answers
Smart people
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 now!
  • 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.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

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.

AlreadyLost (MIS)
1 Jun 06 14:14
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 (Instructor)
1 Jun 06 14:43
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 (TechnicalUser)
1 Jun 06 15:48
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 (TechnicalUser)
1 Jun 06 15:57
Having checkable Yes AND No defeats the purpose.  Option buttons (grouped) are good if you are using ActiveX.

Gerry

AlreadyLost (MIS)
1 Jun 06 16:00
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 (TechnicalUser)
1 Jun 06 16:05
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 (MIS)
1 Jun 06 16:31
Anybody know how to write the macro code?
sfatz (TechnicalUser)
1 Jun 06 16:53
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 (TechnicalUser)
1 Jun 06 17:42
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 (MIS)
1 Jun 06 18:14
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 (TechnicalUser)
1 Jun 06 18:16
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 (MIS)
1 Jun 06 18:32
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 (MIS)
1 Jun 06 18:34
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 (TechnicalUser)
1 Jun 06 18:45
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

AlreadyLost (MIS)
1 Jun 06 19:18
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 (MIS)
1 Jun 06 19:19
I think I found the error. Hold On. Will ask question if still not resolved. Thanks again
AlreadyLost (MIS)
1 Jun 06 19:23
Okay. I got an error " Object Required".
sfatz (TechnicalUser)
2 Jun 06 8:31
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 (TechnicalUser)
2 Jun 06 10:19
The code I posted works wonderfully for me.  Ensure the controls names are correct.

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

sfatz (TechnicalUser)
2 Jun 06 14:10
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
AlreadyLost (MIS)
5 Jun 06 16:04
How do I post my spreadsheet? So you guys can assist?
firefytr (TechnicalUser)
5 Jun 06 17:16
You must upload it to a hosting site/server, this board will not let you upload attachments directly to it.

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

AlreadyLost (MIS)
5 Jun 06 17:42
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.
firefytr (TechnicalUser)
5 Jun 06 17:49
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

AlreadyLost (MIS)
5 Jun 06 18:22
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?
firefytr (TechnicalUser)
5 Jun 06 19:00
No, that has no affect on any code, that is strictly your CellLink property.

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

propublico (TechnicalUser)
7 Jun 06 17:46
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?
firefytr (TechnicalUser)
7 Jun 06 19:04
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

propublico (TechnicalUser)
8 Jun 06 15:28
Thanks, I shoulda thought of that.
AlreadyLost (MIS)
9 Jun 06 12:02
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
firefytr (TechnicalUser)
9 Jun 06 14:51
Yes, that is why the code does not work.  They are part of the Shapes collection.

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

jfhewitt (TechnicalUser)
11 Jun 06 13:35
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.  
firefytr (TechnicalUser)
11 Jun 06 14:39
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

jfhewitt (TechnicalUser)
11 Jun 06 14:49
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.
firefytr (TechnicalUser)
11 Jun 06 14:52
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

jfhewitt (TechnicalUser)
11 Jun 06 15:11
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?
firefytr (TechnicalUser)
11 Jun 06 17:09
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

AlreadyLost (MIS)
12 Jun 06 18:04
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?
firefytr (TechnicalUser)
12 Jun 06 18:38
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

firefytr (TechnicalUser)
12 Jun 06 18:41
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

jfhewitt (TechnicalUser)
13 Jun 06 13:47
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!

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