Checkbox doing one action when checked and another when not

[zerdan]

This should be easy. I have read through the forums and found many examples of this and it seems very straight forward. I have used the examples other people have had without success.

What I'm trying to do seems pretty simple to me. I want a checkbox on one sheet (let's call it "A"). When checked it will add a series of 1's to a column in a different sheet (let's call it "B"). When unchecked it will clear these 1's that it added.

Sub CheckBox1_Click()
    With Sheets("B")
        If CheckBox1 = True Then
            .Range("A2:A12").Value = 1
        Else
            .Range("A2:A12").Clear
        End If
 End With
End Sub

With this I can clear the Range no problem, but I cannot add the 1's. Any suggestions? I'm stuck. It seems like it just skips the If CheckBox1 = True Then line.

 

[texasalynn]

you need the code added to the checkbox. If you right click on the check box select view code, then add your code like

Private Sub CheckBox1_Click()
    With Sheets("B")
        If CheckBox1 = True Then
            .Range("A2:A12").Value = 1
        Else
            .Range("A2:A12").Clear
        End If
 End With
End Sub

 

[zerdan]

Thanks for the reply. Sorry, to sound like an idiot, but this is my first time working with macros and I'm really struggling. I am not following what you said. I had my code "assigned" to the checkbox (and it did clear the column on uncheck, just wouldn't add the 1's).

 

[KGee]

This works for me.

If .CheckBox1.Value = True Then

 

[zerdan]

I tried the .CheckBox1.Value you said worked for you and that didn't work for me, the debug thing popped up. If you have a working version, any chance you would email it to me so I could look at it? I must be doing something silly stupid wrong. This is my first time with macros and I feel like this is just too easy of a task to be this difficult.

I can PM you my email if you are ok with that. It would be greatly appreciated.

 

[KGee]

I had it on the worksheet level and that worked. If you follow texaslynn's suggestion you can make it work more like a toggle switch I believe. Once you're in design mode, right-click the check box and choose "view code" and overwrite everything with the code lynn posted.

 

[texasalynn]

a few questions:
can you right click on your check box?
when you right click on your check box do you see "assign code"?
how did you create your check box? (with the forms tool bar or the control toolbar)

 

[zerdan]

I've been doing some more digging after when KGee and you have said, and I think the problem might lie in the fact that I'm on a mac with the new version of Excel.

I'm on Excel 2011 Mac.

can you right click on your check box?
Yes.

when you right click on your check box do you see "assign code"?
No. I see: Cut, Copy, Paste, Edit Text, Save as Picture, Grouping, Arrange, Assign Macro, and Formatting Control

how did you create your check box? (with the forms tool bar or the control toolbar)
From the Developer Ribbon. It is under "Form Controls" I cannot find the control toolbar anywhere on this version of excel.

 

[texasalynn]

I'm not familiar with Mac, but maybe they have changed to allow macros. I know prior versions did not. When you right click on the check box, look at the Web tab and see how the text is spelled. Make sure these match what you have in your macro. Also I would do a help search to see if the control toolbar is available, these controls behave differently from the forms tools


edit:
also doing a little digging I found some information about 2007 here

 

[zerdan]

I got it working by linking the checkbox to a cell and then checking if the cell was True.

So the line

If CheckBox1 = True Then

had to be replaced with

If .Range("AV1") = True Then

after linking the checkbox to cell AV1