Check Box Help

[ss6857]

Hello everyone.

I have a check box and when it is checked it changes the conditional formatting (which is what I want). However when it is unchecked I want it to go back to its original formatting. Is there a way I can do that? if you need to see my VB just let me know..

Thank you

 

[repairman615]

Hello,

It would help to post the code. Also, how are you using the conditional formatting?

Welcome to the Msg board BTW.

 

[ss6857]

Thank you!

So this is my VB for when I check the box:

Dim rng As Range
Select Case Selection.Cells.Count
Case 1
Set rng = Cells
Case Else
Set rng = Selection
End Select
On Error Resume Next
rng.FormatConditions.Delete
On Error GoTo 0
Range("A4").Select
rng.FormatConditions.Add Type:=xlExpression, Formula1:="=$O4=1"
rng.FormatConditions(1).Interior.ColorIndex = 36


And then when I want to go back to the original formatting when its unchecked which is something like this:

Sub Blue()
Dim rng As Range
Select Case Selection.Cells.Count
Case 1
Set rng = Cells
Case Else
Set rng = Selection
End Select
Range("A4").Select
rng.FormatConditions.Add Type:=xlExpression, Formula1:="=$E4<=$E$2"
rng.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With rng.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
End With
rng.FormatConditions(1).StopIfTrue = False
rng.FormatConditions.Add Type:=xlExpression, Formula1:="=$O4=1"
rng.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With rng.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10092543
.TintAndShade = 0
End With
rng.FormatConditions(1).StopIfTrue = False
End Sub

 

[repairman615]

I have used something along these lines for using a checkbox for true and false.

Using an ActiveX control Check Box named CheckBox1, right click and then veiw code. Use the below structure to toggle actions (Checked and Unchecked).

Replace msgbox 1 with what you would like to happen under true or checked value.

Replace msgbox 2 with what you would like to happen when false or unchecked.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox1_Click()<br><br>    <SPAN style="color:#00007F">If</SPAN> CheckBox1 = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">'''The check box has been checked...do stuff below</SPAN><br>        MsgBox 1<br>        <br>    <SPAN style="color:#00007F">Else</SPAN><br>        <SPAN style="color:#007F00">'''The check box has been unchecked...do stuff below</SPAN><br>        MsgBox 2<br>        <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[ss6857]

:D

That is wonderful. Thank you so much!!!!