Check box - recognise if it is checked or not?

Fireman_Simon

New Member
Joined
Nov 6, 2002
Messages
3
Hi Everyone!
Please help my "bitten-of more than I can chew project".
I have two columns next to one-another with check-boxes in them. One column for YES, and one for NO. What formula do I need for the recognition of the box being checked or not? Also, as the option is YES or NO, can I include something to prevent both check boxes in the same row being ticked OR not ticked?
Advice greatly appreciated.

Many thanks, Simon.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
WELCOME TO THE BOARD!

You may want to consider using OptionButtons instead of CheckBoxes. An OptionButton will only allow one to picked. When either a CheckBox or an OptionButton is selected, it's value is TRUE. Otherwise, it is FALSE. You can set up an IF statement with the value of the CheckBox/OptionButton included. In the properties of either you can elect to have the value (TRUE/FALSE) to show up in a cell on your worksheet. This may help in creating an IF statement. Otherwise, you can use VB code:

Private Sub OptionButton1_Change()
If OptionButton1.Value = TRUE Then
'This is where you put your code
End If
End Sub

I hope that this helps.
 

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
Option buttons have a group property. Just make sure each "yes" and "no" are in the same group, then only one can be pushed at a time.

Can't tell if you understand you have to use VBA for this, not just a worksheet formula. Are you familiar with vba?
 

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579

ADVERTISEMENT

Welcome!

First, to get value from checkbox, right-click on the checkbox, select properties, and put a cell reference in 'linked cell'. You will refer to this cell to test the status of the checkbox.

To prevent both yes and no from being clicked, try:

I'm using these names:
Yes = Checkbox1
No = Checkbox2

Now insert the following code on the sheet:
Code:
Private Sub CheckBox1_Change()

CheckBox2 = CheckBox1 + 1

End Sub

Private Sub CheckBox2_Change()

CheckBox1 = CheckBox2 + 1

End Sub

To insert this code, double click on the checkbox. You'll see something like this:
Code:
Private Sub CheckBox1_Click()

End Sub

Right above this, in the same window, you'll see two drop combos, one says Checkbox1, and one says click. Select 'change' from the 'click' dropcombo. This will appear:
Code:
Private Sub CheckBox1_Change()

End Sub

This is where you put the first piece of code I gave you.

Now click on the dropcombo that says 'checkbox1' and select 'checkbox2'. The click on the other dropcombo(the one with 'click') and select 'change'.

You should get this:
Code:
Private Sub CheckBox2_Change()

End Sub

Here, you put the second piece of code I gave you. You can delete the checkbox1_click() part, and the checkbox2_click() because you aren't attaching any action to the events.

You're code should look like what I have up top.

HTH,
Corticus
This message was edited by Corticus on 2002-11-08 09:18
 

ExcelNovice

Well-known Member
Joined
May 12, 2002
Messages
583
Hi all,

Is it possible to write a macro to copy a particular range of cells to a new sheet in a known workbook?

This known workbook will be different each time, so the user will have to be given an option to indicate the name of the workbook the range of cells should be copied to.

Three things are therefore needed:
1. The macro will prompt the user to indicate the workbook to which the range of cells will be copied.
2. The macro will create a new sheet in that workbook
3. The macro will copy the range of cells that workbook

Thanks for your help...
 

ExcelNovice

Well-known Member
Joined
May 12, 2002
Messages
583
Hi all,

Is it possible to write a macro to copy a particular range of cells to a new sheet in a known workbook?

This known workbook will be different each time, so the user will have to be given an option to indicate the name of the workbook the range of cells should be copied to.

Three things are therefore needed:
1. The macro will prompt the user to indicate the workbook to which the range of cells will be copied.
2. The macro will create a new sheet in that workbook
3. The macro will copy the range of cells that workbook

Thanks for your help...
 

Forum statistics

Threads
1,144,060
Messages
5,722,276
Members
422,420
Latest member
losc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top