Adding macro to checkbox

[VKachnow]

Hi,

I am trying to add a macro to a check box in such a way that clicking on the
box makes the macro run while un-clicking the box makes the macro "un-run"
or return things to how they were. Is there a way to do this?

Thanks in advance for your help!

 

[JE McGimpsey]

You could have the first step in the macro check the checkbox's value,
and branch either to a "do" or "undo" routine.

If your checkbox is from the forms toolbar:

Public Sub Checkbox1_Click()
If Sheet1.DrawingObjects("Check box 1").Value > 0 Then
'Do Stuff
Else
'Undo Stuff
End If
End Sub

If it's from the Control toolbar:

Private Sub Checkbox1_Click()
If Sheet1.CheckBox1.Value Then
'Do Stuff
Else
'Undo Stuff
End If
End Sub

Undo's can be somewhat complicated depending on how long you want the
ability to revert to last. See John Walkenbach's

Undoing a VBA Subroutine
http://j-walk.com/ss/excel/tips/tip23.htm

 

[VKachnow]

Thank you very much for the suggestion! My checkbox is from the control
toolbar, so I tried the second set of code, but I am running into a problem
with the first line. What do I put in in place of "Value"?

Thanks in advance for your help!

 

[mudraker]

try
CheckBox1.Value = true
and
CheckBox1.Value = fals

 

[VKachnow]

Thank you for the suggestion-- I entered it into my code, which is listed
below, but when I try to run the macro by checking on the box I get an error
message that says: "Run-time error 438: Object doesn't support this property
or method." When I hit the debug button, the first line is highlighted as
having a problem. Any other thoughts? Thanks in advance!

If Sheets("Step 4").CheckBox43.Value = True Then
'Sheets("Step 6").Select
Range("C15").Select
Selection.Interior.ColorIndex = 2
Range("G15").Select
Selection.Interior.ColorIndex = 2
Range("I15").Select
Selection.Interior.ColorIndex = 2
Range("J17").Select
Sheets("Step 4").Select
Else
'Sheets("Step 6").Select
Range("C15").Select
Selection.Interior.ColorIndex = 42
Range("G15").Select
Selection.Interior.ColorIndex = 42
Range("I15").Select
Selection.Interior.ColorIndex = 42
Sheets("Step 4").Select
End If
End Sub

 

[mudraker]

Check your checkbox name.

I have been able to replicate your error message by calling for the
value of a checkbox that did not exist on my test sheet.


To simplify your code I suggest you modify it as

If Sheets("Step 4").CheckBox43.Value = True Then

Sheets("Step 6").Range("C15").Interior.ColorIndex = 2
Sheets("Step 6").Range("G15").Interior.ColorIndex = 2
Sheets("Step 6").Range("I15").Interior.ColorIndex = 2
Else
Sheets("Step 6").Range("C15").Interior.ColorIndex = 42
Sheets("Step 6").Range("G15").Interior.ColorIndex = 42
Sheets("Step 6").Range("I15").ColorIndex = 42
End If

 

[VKachnow]

I replaced what I had with your new code and now it is working beautifully!
Thank you so so much for your help!!