If Range not equal to zero in Visual Basic If/Then statement

[Clinton W]

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 here:

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

 

[JLatham]

Try adding
If Not IsEmpty(Range("X1")) And ...
as the start of your test.

The statement would look something like
If Not IsEmpty(Range("X1")) And _
Range("X1") > 0 And Range("X1") < 190428 _
And Range("T1") = "V50" Then

Hope this helps with the problem.

 

[JLatham]

As for making it a bit shorter, there's not too much to be done, but we can
make it a little more efficient and run a little quicker.

Near the beginning of the code add the statement:
Application.ScreenUpdating = False
not having to update the display after every action will improve speed.
it will automatically reset to =True at the End Sub statement.

You can use objects to represent the cells (ranges) and work with them
quicker and also make the code block easier to modify later, as:

'near the beginning of the Sub
'declare object variables to
'use to refer to ranges with later
Dim anyRange1 As Range
Dim anyRange2 as Range

'now you can change these values
'to quickly adapt the If... block
'quickly for other cell pairs
'just use Set before each If block
Set anyRange1 = ActiveSheet.Range("X1")
Set anyRange2 = ActiveSheet.Range("T1")

If Not IsEmpty(anyRange1) And _
anyRange1 > 0 And anyRange1 < 190428 And _
anyRange2 = "V50" Then
ActiveSheet.Shapes("Check Box 8").Select
With Selection
.ShapeRange.Line.Visible = msoTrue
.ShapeRange.Line.ForeColor.SchemeColor = 12
End With
ActiveSheet.Shapes("Check Box 9").Select
Selection.ShapeRange.Line.Visible = msoFalse

ElseIf anyRange1 > 190427 And anyRange2 = "V50" Then
ActiveSheet.Shapes("Check Box 9").Select
With Selection
.ShapeRange.Line.Visible = msoTrue
.ShapeRange.Line.ForeColor.SchemeColor = 12
End With
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

You also don't have to actually select the shape to modify them, you could
modify the shapes more directly, as:
ActiveSheet.Shapes("Check Box 9").ShapeRange.Line.Visible = msoFalse
should work as well as the two lines that are being used now.

Also instead of selecting and even using the With statement you can be more
direct as instead of:
ActiveSheet.Shapes("Check Box 9").Select
With Selection
.ShapeRange.Line.Visible = msoTrue
.ShapeRange.Line.ForeColor.SchemeColor = 12
End With

try
With ActiveSheet.Shapes("Check Box 9")
.ShapeRange.Line.Visible = msoTrue
.ShapeRange.Line.ForeColor.SchemeColor = 12
End With


Again, hope this helps some.

 

[Clinton W]

The If Not IsEmpty instruction works beautifully thanks. It's just what I
needed. I understand what you mean about the other things in your answer, but
I'm going to take a bit of time to get my head round them before I use them
in this worksheet, as I'm a novice with VB and learning as I go.
I really appreciate your efforts and fast response.
Regards
Clinton>