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

 

[AlreadyLost]

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

 

[firefytr]

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

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

 

[AlreadyLost]

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]

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]

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]

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

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

 

[propublico]

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]

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]

Thanks, I shoulda thought of that.

 

[AlreadyLost]

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]

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

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

 

[jfhewitt]

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]

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]

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]

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]

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]

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]

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]

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]

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