Hi there, I have a fairly large excel workbook which in many cases requires
different Check Boxes (form controls) to change border color depending on the
contents of a cell. I've put an example of one of them he
If Range("X1") 0 And Range("X1") < 190428 And Range("T1") = "V50" Then
ActiveSheet.Shapes("Check Box 8").Select
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 12
ActiveSheet.Shapes("Check Box 9").Select
Selection.ShapeRange.Line.Visible = msoFalse
ElseIf Range("X1") 190427 And Range("T1") = "V50" Then
ActiveSheet.Shapes("Check Box 9").Select
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 12
ActiveSheet.Shapes("Check Box 8").Select
Selection.ShapeRange.Line.Visible = msoFalse
Else
ActiveSheet.Shapes("Check Box 8").Select
Selection.ShapeRange.Line.Visible = msoFalse
ActiveSheet.Shapes("Check Box 9").Select
Selection.ShapeRange.Line.Visible = msoFalse
End If
There are allot of these and in most cases work very well, except that range
("X1") is sometimes empty, and in frequent cases Check box 8 or 9 changes
scheme color regardless. I need them to remain unchanged if there's nothing
in Range X1.
I've tried putting it in these different ways:
If Range("X1") < 0 And Range("X1") < 190428 Then
If Range("X1") < 190428 And Range("X1") 0 Then
If Range("X1") = "" And Range("X1") < 190428 Then
If Range("X1") = "" And Range("X1") < "190428" Then
If Range("X1") < "0" And Range("X1") < "190428" Then
If Range("X1") "0" And Range("X1") < "190428" Then
If I put quotation marks around both the 0 and the 190428 it doesn't work at
all.
Every test I've done on a new worksheet works perfectly, so I'm a bit
baffled. I'm positive the check boxes aren't being effected by any other
unrelated instructions.
What I basically want to say in the script is "If Range("X1") is between 1
and 190428 Then....", but
VB doesn't like that.
Can any body suggest an alternative way of putting this? I would welcome any
suggestion on how to shorten the script as well, if that's possible.
Thank you.
Regards
Clinton