Check box - recognise if it is checked or not?

[Fireman_Simon]

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.

 

[phantom1975]

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]

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?

 

[Fireman_Simon]

VBA.

Nnnoooo, not really.

 

[Corticus]

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:

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:

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:

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:

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]

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]

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...