Macro to Display String on Checkbox Select

views:

answers:

Macro to Display String on Checkbox Select

I have a checkbox on sheet1 (there are about 7 sheets total). If the checkbox is selected (true), I want it to say "Approved" in cell M9. If the checkbox is not selected (false), I want it to say "Denied" in the textbox.

Do I need to create a macro for that?

If I want it to display the same text in cell M5 of sheet2, how would I put it all together?

+2 A:

You can link the status of a checkbox to a cell (right-click, format control, cell link on the control tab), this means you can then refer to that cell in any other sheet or cell.

Remou 2010-06-09 22:02:08

Thanks, but I have no clue what type of code I would need for that. Any suggestions?

Daniel 2010-06-10 12:30:02

There is no code, just formulas: =If(a9, "a9 is true","Denied")Where are you having problems?

Remou 2010-06-10 12:45:10

Or even SheetX!a9

Remou 2010-06-10 12:51:14

Don't you mean, =If checkbox1.value = true, "Denied"Would that work?

Daniel 2010-06-10 13:21:10

But I want to put it in my code, so how do I do that?

Daniel 2010-06-10 13:22:10

No, I do not mean checkbox1.value. And, no, it would not work. You need to link the checkbox to a cell, as described. You say you wish to put text in a cell based on a checkbox on sheet1. Why do you now wish to use code? Please update your question to show your new requirements.

Remou 2010-06-10 13:31:56

The easiest way is fine. How do I link the checkbox to a cell?

Daniel 2010-06-10 13:40:16

I describe it in my answer above. Right-click and choose Format Control, on the Control tab, fill a cell reference into Cell Link.

Remou 2010-06-10 13:43:16

Actually it has to apply on the click of a button, so linking wouldn't work.I'm going to have one checkbox on each sheet, so linking would sometimes display something other than what I want.

Daniel 2010-06-10 13:44:36

You understand that the linked cell will just give you a convenient way to refer to a checkbox in a worksheet without using code? The value of the linked cell will either be True or False, depending on whether the user has checked the box or not. You can then refer to this cell in any other cell or formula. For example, =IF(A9=TRUE, "Accepted","Denied")

Remou 2010-06-10 13:49:57

What is this button to which you refer?

Remou 2010-06-10 13:50:20

It's a button that runs other steps of the program. The code above works great for my purposes.

Daniel 2010-06-10 13:55:35

You need to rephrase your question, because, at the moment, it asks whether you need a macro, which you don't, for a checkbox, so it should be on superuser.

Remou 2010-06-10 14:04:45

+1 A:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Range("M9").Value = "Approved"
Else
Range("M9").Value = "Denied"
Sheets("Sheet2").Range("M5").Value = "Denied"
End If
End Sub

krusaint 2010-06-10 13:34:52

Are you not thinking if a user form? The above code would need to be added to the checkbox on sheet1, surely it is easier to skip the code and simply link a cell?

Remou 2010-06-10 13:38:31

This works great. Linking a cell would eliminate code, which would be a plus, but I don't know how to do it.

Daniel 2010-06-10 13:41:21

ansaurus

tags:

views:

answers:

Macro to Display String on Checkbox Select

related questions