Manipulate cells with checkboxes in Excel 2002 1

[nelson97]

Please review code below to see if there's anything wrong with it. I have this code on several checkboxes on Sheet 1. But I'm seeing a inconsistency with the cells in Sheet 3 not updating properly based on populating it with 1 or 0 based on True or False values of the checkbox. I notice that sometimes the value does not change to 0 even when the checkbox is rendered 'false'.. Please advise- Thank you
Private Sub CheckBox126_Click()

If CheckBox126.Value = True Then
Sheets("Sheet3").Range("A2").Value = 1
Range("D30").Interior.Color = vbBlack
CheckBox305.Value = False
Range("D32").Interior.ColorIndex = xlNone
ElseIf Sheets("Sheet3").Range("F1").Value = 3 Then
Range("D30").Interior.ColorIndex = xlNone
Range("D31").Interior.Color = &H8000&

ElseIf Sheets("Sheet3").Range("F1").Value = 2 Then
Range("D30").Interior.ColorIndex = xlNone
Range("D31").Interior.Color = vbYellow

ElseIf Sheets("Sheet3").Range("F1").Value = 1 Then
Range("D30").Interior.ColorIndex = xlNone
Range("D31").Interior.Color = vbRed

ElseIf CheckBox126.Value = False Then
Sheets("Sheet3").Range("A2").Value = 0
Range("D30").Interior.ColorIndex = xlNone
Range("D31").Interior.Color = &H8000&

End If

End Sub

 

[TonyJollans]

Hi nelson97,

If your Checkbox is False, what happens depends on the contents of cell F1 on Sheet3. Only if it is not 3 and not 2 and not 1, will the code run to set the value of cell A2 to 0.

I can't advise on what the code should be unless you can give a description (in English) of what you want to happen under all circumstances. You might, though, be able to move the setting of the cell outside the If construct ..

[blue]Private Sub CheckBox126_Click()

Sheets("Sheet3").Range("A2").Value = -CheckBox126.Value

If CheckBox126.Value = True Then
       Range("D30").Interior.Color = vbBlack
       CheckBox305.Value = False
       Range("D32").Interior.ColorIndex = xlNone
ElseIf Sheets("Sheet3").Range("F1").Value = 3 Then
       Range("D30").Interior.ColorIndex = xlNone
       Range("D31").Interior.Color = &H8000&
       
ElseIf Sheets("Sheet3").Range("F1").Value = 2 Then
       Range("D30").Interior.ColorIndex = xlNone
       Range("D31").Interior.Color = vbYellow
       
ElseIf Sheets("Sheet3").Range("F1").Value = 1 Then
       Range("D30").Interior.ColorIndex = xlNone
       Range("D31").Interior.Color = vbRed
       
ElseIf CheckBox126.Value = False Then
       Range("D30").Interior.ColorIndex = xlNone
       Range("D31").Interior.Color = &H8000&
       
        End If
    
End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[nelson97]

Hi TonyJollans, here's what I've applied below and has been working properly from my testings. What I'm basically trying to accomplish is triggering a cell to turn green, yelllow or red depending on how many checkboxes has been clicked. For example, I have embedded checkboxes embedded in cells A1-D1 with corresponding cells in Sheet 3 that hold either a value of "0" or "1" with a main cell holding a current count of the cells with max value of 4. This concept is applied to other groups of cells that may contain more checkboxes. Can you explain taking this line "Sheets("Sheet3").Range("A1").Value = -CheckBox1.Value" out of the If construct?

Thanks

Sheets("Sheet3").Range("A1").Value = -CheckBox1.Value
If CheckBox1.Value = True Then
Sheets("Sheet3").Range("A1").Value = 1
Range("A1").Interior.Color = vbBlack
CheckBox5.Value = False
Range("A3").Interior.ColorIndex = xlNone
ElseIf Sheets("Sheet3").Range("G1").Value >= 6 Then
Range("A1").Interior.ColorIndex = xlNone
Range("A2").Interior.Color = &H8000&

ElseIf Sheets("Sheet3").Range("G1").Value >= 3 Then
Range("A1").Interior.ColorIndex = xlNone
Range("A2").Interior.Color = vbYellow

ElseIf Sheets("Sheet3").Range("G1").Value >= 0 Then
Range("A1").Interior.ColorIndex = xlNone
Range("A2").Interior.Color = vbRed

End If

 

[TonyJollans]

Hi nelson97,

What you are trying to do is conditionally format some cells - use conditional formatting!!

To do this name your cell on Sheet3 (cell F1 or G1?) using Insert > Name > Define. Call it, say, CheckBoxTotal. Then select the cells you want coloured and Select Format > Conditional Formatting.

Under Condition 1 select Formula Is and then enter the formula =CheckBoxTotal>=6, choose your format and press Add.

Under Condition 2 select Formula Is and then enter the formula =CheckBoxTotal>=3, choose your format and press Add.

Under Condition 3 select Formula Is and then enter the formula =CheckBoxTotal>=0, choose your format and press OK.

With the data you mention you could actually set the cell to be green normally and just use two conditions in the conditional formatting.

If you do still want code, what I meant in my first post was that it was simpler to separate out the two actions - the simple one of setting the cell value, and the more complex one of setting the colours. The line ..

[blue]

Sheets("Sheet3").Range("A2").Value = -CheckBox126.Value

[/blue] .. is simply a short form of saying ..

[blue]If CheckBox126.Value = True Then
       Sheets("Sheet3").Range("A2").Value = 1
Else
       Sheets("Sheet3").Range("A2").Value = 0
End If[/blue]

.. which is what I thought you were trying to do, but getting wrong when mixed with the other logic.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.