| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Range not equal to zero in Visual Basic If/Then statement
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Range not equal to zero in Visual Basic If/Then statement
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. "Clinton W" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Range not equal to zero in Visual Basic If/Then statement
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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Range not equal to zero in Visual Basic If/Then statement
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 "JLatham" wrote: 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is Visual Basic the same as Visual Studio 2008? | Excel Worksheet Functions | |||
| Visual Basic | Excel Discussion (Misc queries) | |||
| visual basic | Excel Discussion (Misc queries) | |||
| Micrsoft Visual Basic Compile error: Expected: end of statement | Excel Discussion (Misc queries) | |||
| changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) | |||
Linear Mode
