ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Simplify code to lower file size. Possible ? (https://www.excelbanter.com/excel-programming/378499-simplify-code-lower-file-size-possible.html)

Corey
Simplify code to lower file size. Possible ?
 
The code below is part of a TimeSheet i have set up.
The code below is to prevent an overlap in job finish/start times, unless
"V17".value is selected.

Is there a way i can simplify the code to be more efficiently written?
I am trying to reduce th size of the file.

((Private Sub Worksheet_SelectionChange(ByVal target As Range)
If Range("C8") = "" Then Exit Sub
If Range("C11") = "" Then Exit Sub
If Range("C11").Value < Range("C8").Value And Range("C9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", , "
....."
Range("C11").ClearContents
Range("C11").Select
End If
If Range("C15") < "" And Range("C15") < Range("C12") And Range("C11").Value
< Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
"...."
Range("C15").ClearContents
Range("C15").Select
End If
If Range("F8") = "" Then Exit Sub
If Range("F11") = "" Then Exit Sub
If Range("F11").Value < Range("F8").Value And Range("F9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
"...."
Range("F11").ClearContents
Range("F11").Select
End If
If Range("F15") < "" And Range("F15") < Range("F12") And Range("F11").Value
< Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", , "
....."
Range("F15").ClearContents
Range("F15").Select
End If
If Range("I8") = "" Then Exit Sub
If Range("I11") = "" Then Exit Sub
If Range("I11").Value < Range("I8").Value And Range("I9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
"...."
Range("I11").ClearContents
Range("I11").Select
End If
If Range("I15") < "" And Range("I15") < Range("I12") And Range("I11").Value
< Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
"...."
Range("I15").ClearContents
Range("I15").Select
End If
If Range("L8") = "" Then Exit Sub
If Range("L11") = "" Then Exit Sub
If Range("L11").Value < Range("L8").Value And Range("Ll9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
"...."
Range("L11").ClearContents
Range("L11").Select
End If
If Range("L15") < "" And Range("L15") < Range("L12") And Range("L11").Value
< Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
"...." Range("L15").ClearContents
Range("L15").Select
End If
If Range("O8") = "" Then Exit Sub
If Range("O11") = "" Then Exit Sub
If Range("O11").Value < Range("O8").Value And Range("O9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", , "
....."
Range("O11").ClearContents
Range("O11").Select
End If
If Range("O15") < "" And Range("O15") < Range("O12") And Range("O11").Value
< Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", , "
....."
Range("O15").ClearContents
Range("O15").Select
End If
If Range("R8") = "" Then Exit Sub
If Range("R11") = "" Then Exit Sub
If Range("R11").Value < Range("R8").Value And Range("R9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", , "
....."
Range("R11").ClearContents
Range("R11").Select
End If
If Range("R15") < "" And Range("R15") < Range("R12") And Range("R11").Value
< Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
""
Range("R15").ClearContents
Range("R15").Select
End If
If Range("U8") = "" Then Exit Sub
If Range("U11") = "" Then Exit Sub
If Range("U11").Value < Range("U8").Value And Range("U9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", , "
....."
Range("U11").ClearContents
Range("U11").Select
End If
If Range("U15") < "" And Range("U15") < Range("U12") And Range("U11").Value
< Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", , "
....."
Range("U15").ClearContents
Range("U15").Select
End If
End Sub


COrey....



Bob Phillips
Simplify code to lower file size. Possible ?
 
I ope I have got the ranges and logic correct

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Const WS_RANGE1 As String = "C8,C12,F8,I8,I12,L8,L11,O8,O12,R8,R12,U8,U11"
Const WS_RANGE2 As String = "C11,C15,F9,I9,I15,L19,L2,O9,O15,R9,R15,U9,U12 "

Const msg As String = _
"There is an overlap in the Times Entered." & vbNewLine & _
"The next Start Time needs to be equal or greater than the previous
Finish Time."

If Not Intersect(target, Range(WS_RANGE1)) Is Nothing Then
If target.Value = "" Or target.Offset(0, 3).Value = "" Then
Exit Sub
End If
If target.Offset(0, 3).Value < target.Value And _
target.Offset(0, 3).Value < Range("V17").Value Then
MsgBox msg, , "...."
target.Offset(0, 3).ClearContents
target.Offset(0, 3).Select
End If
ElseIf Not Intersect(target, Range(WS_RANGE2)) Is Nothing Then
If target.Value = "" Or target.Offset(0, -3).Value = "" Then
Exit Sub
End If
If target.Value < target.Offset(0, -3).Value And _
target.Value < Range("V17").Value Then
MsgBox msg, , "...."
target.ClearContents
target.Select
End If


End Sub



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Corey" wrote in message
.. .
The code below is part of a TimeSheet i have set up.
The code below is to prevent an overlap in job finish/start times, unless
"V17".value is selected.

Is there a way i can simplify the code to be more efficiently written?
I am trying to reduce th size of the file.

((Private Sub Worksheet_SelectionChange(ByVal target As Range)
If Range("C8") = "" Then Exit Sub
If Range("C11") = "" Then Exit Sub
If Range("C11").Value < Range("C8").Value And Range("C9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
" ...."
Range("C11").ClearContents
Range("C11").Select
End If
If Range("C15") < "" And Range("C15") < Range("C12") And
Range("C11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
"...."
Range("C15").ClearContents
Range("C15").Select
End If
If Range("F8") = "" Then Exit Sub
If Range("F11") = "" Then Exit Sub
If Range("F11").Value < Range("F8").Value And Range("F9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
"...."
Range("F11").ClearContents
Range("F11").Select
End If
If Range("F15") < "" And Range("F15") < Range("F12") And
Range("F11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
" ...."
Range("F15").ClearContents
Range("F15").Select
End If
If Range("I8") = "" Then Exit Sub
If Range("I11") = "" Then Exit Sub
If Range("I11").Value < Range("I8").Value And Range("I9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
"...."
Range("I11").ClearContents
Range("I11").Select
End If
If Range("I15") < "" And Range("I15") < Range("I12") And
Range("I11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
"...."
Range("I15").ClearContents
Range("I15").Select
End If
If Range("L8") = "" Then Exit Sub
If Range("L11") = "" Then Exit Sub
If Range("L11").Value < Range("L8").Value And Range("Ll9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
"...."
Range("L11").ClearContents
Range("L11").Select
End If
If Range("L15") < "" And Range("L15") < Range("L12") And
Range("L11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
"...." Range("L15").ClearContents
Range("L15").Select
End If
If Range("O8") = "" Then Exit Sub
If Range("O11") = "" Then Exit Sub
If Range("O11").Value < Range("O8").Value And Range("O9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
" ...."
Range("O11").ClearContents
Range("O11").Select
End If
If Range("O15") < "" And Range("O15") < Range("O12") And
Range("O11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
" ...."
Range("O15").ClearContents
Range("O15").Select
End If
If Range("R8") = "" Then Exit Sub
If Range("R11") = "" Then Exit Sub
If Range("R11").Value < Range("R8").Value And Range("R9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
" ...."
Range("R11").ClearContents
Range("R11").Select
End If
If Range("R15") < "" And Range("R15") < Range("R12") And
Range("R11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
""
Range("R15").ClearContents
Range("R15").Select
End If
If Range("U8") = "" Then Exit Sub
If Range("U11") = "" Then Exit Sub
If Range("U11").Value < Range("U8").Value And Range("U9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
" ...."
Range("U11").ClearContents
Range("U11").Select
End If
If Range("U15") < "" And Range("U15") < Range("U12") And
Range("U11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
" ...."
Range("U15").ClearContents
Range("U15").Select
End If
End Sub


COrey....

Corey
Simplify code to lower file size. Possible ?
 
thanks for the reply Bob,
I get an error as soon as the cell C11 is selected at this line:
If target.Value = "" Or target.Offset(0, -3).Value = "" Then exit sub

The offset(0, -3).value has a value in it.

Corey....
"Bob Phillips" wrote in message
...
I ope I have got the ranges and logic correct

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Const WS_RANGE1 As String = "C8,C12,F8,I8,I12,L8,L11,O8,O12,R8,R12,U8,U11"
Const WS_RANGE2 As String =
"C11,C15,F9,I9,I15,L19,L2,O9,O15,R9,R15,U9,U12 "

Const msg As String = _
"There is an overlap in the Times Entered." & vbNewLine & _
"The next Start Time needs to be equal or greater than the previous
Finish Time."

If Not Intersect(target, Range(WS_RANGE1)) Is Nothing Then
If target.Value = "" Or target.Offset(0, 3).Value = "" Then
Exit Sub
End If
If target.Offset(0, 3).Value < target.Value And _
target.Offset(0, 3).Value < Range("V17").Value Then
MsgBox msg, , "...."
target.Offset(0, 3).ClearContents
target.Offset(0, 3).Select
End If
ElseIf Not Intersect(target, Range(WS_RANGE2)) Is Nothing Then
If target.Value = "" Or target.Offset(0, -3).Value = "" Then
Exit Sub
End If
If target.Value < target.Offset(0, -3).Value And _
target.Value < Range("V17").Value Then
MsgBox msg, , "...."
target.ClearContents
target.Select
End If


End Sub



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Corey" wrote in message
.. .
The code below is part of a TimeSheet i have set up.
The code below is to prevent an overlap in job finish/start times, unless
"V17".value is selected.

Is there a way i can simplify the code to be more efficiently written?
I am trying to reduce th size of the file.

((Private Sub Worksheet_SelectionChange(ByVal target As Range)
If Range("C8") = "" Then Exit Sub
If Range("C11") = "" Then Exit Sub
If Range("C11").Value < Range("C8").Value And Range("C9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("C11").ClearContents
Range("C11").Select
End If
If Range("C15") < "" And Range("C15") < Range("C12") And
Range("C11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...."
Range("C15").ClearContents
Range("C15").Select
End If
If Range("F8") = "" Then Exit Sub
If Range("F11") = "" Then Exit Sub
If Range("F11").Value < Range("F8").Value And Range("F9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...."
Range("F11").ClearContents
Range("F11").Select
End If
If Range("F15") < "" And Range("F15") < Range("F12") And
Range("F11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("F15").ClearContents
Range("F15").Select
End If
If Range("I8") = "" Then Exit Sub
If Range("I11") = "" Then Exit Sub
If Range("I11").Value < Range("I8").Value And Range("I9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...."
Range("I11").ClearContents
Range("I11").Select
End If
If Range("I15") < "" And Range("I15") < Range("I12") And
Range("I11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...."
Range("I15").ClearContents
Range("I15").Select
End If
If Range("L8") = "" Then Exit Sub
If Range("L11") = "" Then Exit Sub
If Range("L11").Value < Range("L8").Value And Range("Ll9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...."
Range("L11").ClearContents
Range("L11").Select
End If
If Range("L15") < "" And Range("L15") < Range("L12") And
Range("L11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...." Range("L15").ClearContents
Range("L15").Select
End If
If Range("O8") = "" Then Exit Sub
If Range("O11") = "" Then Exit Sub
If Range("O11").Value < Range("O8").Value And Range("O9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("O11").ClearContents
Range("O11").Select
End If
If Range("O15") < "" And Range("O15") < Range("O12") And
Range("O11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("O15").ClearContents
Range("O15").Select
End If
If Range("R8") = "" Then Exit Sub
If Range("R11") = "" Then Exit Sub
If Range("R11").Value < Range("R8").Value And Range("R9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("R11").ClearContents
Range("R11").Select
End If
If Range("R15") < "" And Range("R15") < Range("R12") And
Range("R11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, ""
Range("R15").ClearContents
Range("R15").Select
End If
If Range("U8") = "" Then Exit Sub
If Range("U11") = "" Then Exit Sub
If Range("U11").Value < Range("U8").Value And Range("U9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("U11").ClearContents
Range("U11").Select
End If
If Range("U15") < "" And Range("U15") < Range("U12") And
Range("U11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("U15").ClearContents
Range("U15").Select
End If
End Sub


COrey....

Tom Ogilvy
Simplify code to lower file size. Possible ?
 
Range("C11").Offset(0,-3) would be an illegal range reference.


--
Regards,
Tom Ogilvy




"Corey" wrote in message
...
thanks for the reply Bob,
I get an error as soon as the cell C11 is selected at this line:
If target.Value = "" Or target.Offset(0, -3).Value = "" Then exit sub

The offset(0, -3).value has a value in it.

Corey....
"Bob Phillips" wrote in message
...
I ope I have got the ranges and logic correct

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Const WS_RANGE1 As String =
"C8,C12,F8,I8,I12,L8,L11,O8,O12,R8,R12,U8,U11"
Const WS_RANGE2 As String =
"C11,C15,F9,I9,I15,L19,L2,O9,O15,R9,R15,U9,U12 "

Const msg As String = _
"There is an overlap in the Times Entered." & vbNewLine & _
"The next Start Time needs to be equal or greater than the previous
Finish Time."

If Not Intersect(target, Range(WS_RANGE1)) Is Nothing Then
If target.Value = "" Or target.Offset(0, 3).Value = "" Then
Exit Sub
End If
If target.Offset(0, 3).Value < target.Value And _
target.Offset(0, 3).Value < Range("V17").Value Then
MsgBox msg, , "...."
target.Offset(0, 3).ClearContents
target.Offset(0, 3).Select
End If
ElseIf Not Intersect(target, Range(WS_RANGE2)) Is Nothing Then
If target.Value = "" Or target.Offset(0, -3).Value = "" Then
Exit Sub
End If
If target.Value < target.Offset(0, -3).Value And _
target.Value < Range("V17").Value Then
MsgBox msg, , "...."
target.ClearContents
target.Select
End If


End Sub



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Corey" wrote in message
.. .
The code below is part of a TimeSheet i have set up.
The code below is to prevent an overlap in job finish/start times,
unless "V17".value is selected.

Is there a way i can simplify the code to be more efficiently written?
I am trying to reduce th size of the file.

((Private Sub Worksheet_SelectionChange(ByVal target As Range)
If Range("C8") = "" Then Exit Sub
If Range("C11") = "" Then Exit Sub
If Range("C11").Value < Range("C8").Value And Range("C9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("C11").ClearContents
Range("C11").Select
End If
If Range("C15") < "" And Range("C15") < Range("C12") And
Range("C11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...."
Range("C15").ClearContents
Range("C15").Select
End If
If Range("F8") = "" Then Exit Sub
If Range("F11") = "" Then Exit Sub
If Range("F11").Value < Range("F8").Value And Range("F9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...."
Range("F11").ClearContents
Range("F11").Select
End If
If Range("F15") < "" And Range("F15") < Range("F12") And
Range("F11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("F15").ClearContents
Range("F15").Select
End If
If Range("I8") = "" Then Exit Sub
If Range("I11") = "" Then Exit Sub
If Range("I11").Value < Range("I8").Value And Range("I9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...."
Range("I11").ClearContents
Range("I11").Select
End If
If Range("I15") < "" And Range("I15") < Range("I12") And
Range("I11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...."
Range("I15").ClearContents
Range("I15").Select
End If
If Range("L8") = "" Then Exit Sub
If Range("L11") = "" Then Exit Sub
If Range("L11").Value < Range("L8").Value And Range("Ll9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...."
Range("L11").ClearContents
Range("L11").Select
End If
If Range("L15") < "" And Range("L15") < Range("L12") And
Range("L11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...." Range("L15").ClearContents
Range("L15").Select
End If
If Range("O8") = "" Then Exit Sub
If Range("O11") = "" Then Exit Sub
If Range("O11").Value < Range("O8").Value And Range("O9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("O11").ClearContents
Range("O11").Select
End If
If Range("O15") < "" And Range("O15") < Range("O12") And
Range("O11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("O15").ClearContents
Range("O15").Select
End If
If Range("R8") = "" Then Exit Sub
If Range("R11") = "" Then Exit Sub
If Range("R11").Value < Range("R8").Value And Range("R9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("R11").ClearContents
Range("R11").Select
End If
If Range("R15") < "" And Range("R15") < Range("R12") And
Range("R11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, ""
Range("R15").ClearContents
Range("R15").Select
End If
If Range("U8") = "" Then Exit Sub
If Range("U11") = "" Then Exit Sub
If Range("U11").Value < Range("U8").Value And Range("U9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("U11").ClearContents
Range("U11").Select
End If
If Range("U15") < "" And Range("U15") < Range("U12") And
Range("U11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("U15").ClearContents
Range("U15").Select
End If
End Sub


COrey....

Corey
Simplify code to lower file size. Possible ?
 
Ok Got a litle further now.

I changed the Offset(0, -3) to Offset(-3, 0)

Now i get the required msgbox, but ONLY if i re- Select the cell, I do not
get the msgbox if the value is entered and then another cell is selected.

How can i change it so ONCE the value triggers the msgbox, and the cell with
that value is then no longer selected the msgbox pop's up,
Not only if i re-select the cell ?

Working code now:
~~~~~~~~~~~~~~
Private Sub Worksheet_SelectionChange(ByVal target As Range)
Const WS_RANGE1 As String =
"C8,C12,F8,F12,I8,I12,L8,L12,O8,O12,R8,R12,U8, U12"
Const WS_RANGE2 As String =
"C11,C15,F11,F15,I11,I15,L11,L15,O11,O15,R11,R15,U 11,U15"

Const msg As String = _
"There is an overlap in the Times Entered." & vbNewLine & _
"The next Start Time needs to be equal or greater than the previous
Finish Time."

If Not Intersect(target, Range(WS_RANGE1)) Is Nothing Then
If target.Value = "" Or target.Offset(0, 3).Value = "" Then
Exit Sub
End If
If target.Offset(0, 3).Value < target.Value And _
target.Offset(0, 3).Value < Range("V17").Value Then
MsgBox msg, , "...."
target.Offset(0, 3).ClearContents
target.Offset(0, 3).Select
End If
ElseIf Not Intersect(target, Range(WS_RANGE2)) Is Nothing Then
If target.Value = "" Or target.Offset(-3, 0).Value = "" Then
Exit Sub
End If
If target.Value < target.Offset(-3, 0).Value And _
target.Value < Range("V17").Value Then
MsgBox msg, , "...."
target.ClearContents
target.Select
End If

End If
End Sub


~~~~~~~~~~~~~~
Corey....
"Tom Ogilvy" wrote in message
...
Range("C11").Offset(0,-3) would be an illegal range reference.


--
Regards,
Tom Ogilvy




"Corey" wrote in message
...
thanks for the reply Bob,
I get an error as soon as the cell C11 is selected at this line:
If target.Value = "" Or target.Offset(0, -3).Value = "" Then exit sub

The offset(0, -3).value has a value in it.

Corey....
"Bob Phillips" wrote in message
...
I ope I have got the ranges and logic correct

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Const WS_RANGE1 As String =
"C8,C12,F8,I8,I12,L8,L11,O8,O12,R8,R12,U8,U11"
Const WS_RANGE2 As String =
"C11,C15,F9,I9,I15,L19,L2,O9,O15,R9,R15,U9,U12 "

Const msg As String = _
"There is an overlap in the Times Entered." & vbNewLine & _
"The next Start Time needs to be equal or greater than the previous
Finish Time."

If Not Intersect(target, Range(WS_RANGE1)) Is Nothing Then
If target.Value = "" Or target.Offset(0, 3).Value = "" Then
Exit Sub
End If
If target.Offset(0, 3).Value < target.Value And _
target.Offset(0, 3).Value < Range("V17").Value Then
MsgBox msg, , "...."
target.Offset(0, 3).ClearContents
target.Offset(0, 3).Select
End If
ElseIf Not Intersect(target, Range(WS_RANGE2)) Is Nothing Then
If target.Value = "" Or target.Offset(0, -3).Value = "" Then
Exit Sub
End If
If target.Value < target.Offset(0, -3).Value And _
target.Value < Range("V17").Value Then
MsgBox msg, , "...."
target.ClearContents
target.Select
End If


End Sub



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Corey" wrote in message
.. .
The code below is part of a TimeSheet i have set up.
The code below is to prevent an overlap in job finish/start times,
unless "V17".value is selected.

Is there a way i can simplify the code to be more efficiently written?
I am trying to reduce th size of the file.

((Private Sub Worksheet_SelectionChange(ByVal target As Range)
If Range("C8") = "" Then Exit Sub
If Range("C11") = "" Then Exit Sub
If Range("C11").Value < Range("C8").Value And Range("C9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish
Time.", , " ...."
Range("C11").ClearContents
Range("C11").Select
End If
If Range("C15") < "" And Range("C15") < Range("C12") And
Range("C11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish
Time.", , "...."
Range("C15").ClearContents
Range("C15").Select
End If
If Range("F8") = "" Then Exit Sub
If Range("F11") = "" Then Exit Sub
If Range("F11").Value < Range("F8").Value And Range("F9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish
Time.", , "...."
Range("F11").ClearContents
Range("F11").Select
End If
If Range("F15") < "" And Range("F15") < Range("F12") And
Range("F11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish
Time.", , " ...."
Range("F15").ClearContents
Range("F15").Select
End If
If Range("I8") = "" Then Exit Sub
If Range("I11") = "" Then Exit Sub
If Range("I11").Value < Range("I8").Value And Range("I9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish
Time.", , "...."
Range("I11").ClearContents
Range("I11").Select
End If
If Range("I15") < "" And Range("I15") < Range("I12") And
Range("I11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish
Time.", , "...."
Range("I15").ClearContents
Range("I15").Select
End If
If Range("L8") = "" Then Exit Sub
If Range("L11") = "" Then Exit Sub
If Range("L11").Value < Range("L8").Value And Range("Ll9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish
Time.", , "...."
Range("L11").ClearContents
Range("L11").Select
End If
If Range("L15") < "" And Range("L15") < Range("L12") And
Range("L11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish
Time.", , "...." Range("L15").ClearContents
Range("L15").Select
End If
If Range("O8") = "" Then Exit Sub
If Range("O11") = "" Then Exit Sub
If Range("O11").Value < Range("O8").Value And Range("O9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish
Time.", , " ...."
Range("O11").ClearContents
Range("O11").Select
End If
If Range("O15") < "" And Range("O15") < Range("O12") And
Range("O11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish
Time.", , " ...."
Range("O15").ClearContents
Range("O15").Select
End If
If Range("R8") = "" Then Exit Sub
If Range("R11") = "" Then Exit Sub
If Range("R11").Value < Range("R8").Value And Range("R9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish
Time.", , " ...."
Range("R11").ClearContents
Range("R11").Select
End If
If Range("R15") < "" And Range("R15") < Range("R12") And
Range("R11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish
Time.", , ""
Range("R15").ClearContents
Range("R15").Select
End If
If Range("U8") = "" Then Exit Sub
If Range("U11") = "" Then Exit Sub
If Range("U11").Value < Range("U8").Value And Range("U9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish
Time.", , " ...."
Range("U11").ClearContents
Range("U11").Select
End If
If Range("U15") < "" And Range("U15") < Range("U12") And
Range("U11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish
Time.", , " ...."
Range("U15").ClearContents
Range("U15").Select
End If
End Sub


COrey....

macropod
Simplify code to lower file size. Possible ?
 
Hi Corey,

An easy way to reduce the size of most files containing vba modules/forms is
to simply export & remove the modules/forms, save the file, then re-import
the modules/forms. Ideally, you'd do this when you've finished your
workbook's development.

Cheers

--
macropod
[MVP - Microsoft Word]

bz
Simplify code to lower file size. Possible ?
 
"Corey" wrote in
:

The code below is part of a TimeSheet i have set up.
The code below is to prevent an overlap in job finish/start times,
unless "V17".value is selected.

Is there a way i can simplify the code to be more efficiently written?
I am trying to reduce th size of the file.


why not something like the following?
---------------------------
((Private Sub Worksheet_SelectionChange(ByVal target As Range)
If Range("C8") = "" Then Exit Sub
If Range("C11") = "" Then Exit Sub

call check_overlap("C11", "C8", "C9", "V17")
call check_overlap("C11", "C15", "C12", "V17")
.....

end sub

-----------------------------
private sub check_overlap(A_col as text, B_col as text, C_col as text,
D_col as text, E_col as text)

Range(A_col).ClearContents
Range(A_col).Select
End If
If Range(A_col) < "" And Range(B_col) < Range(C_col) And Range _
(A_col).Value < Range(D_col).Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.", ,
"...."

end sub







--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

remove ch100-5 to avoid spam trap

Bob Phillips
Simplify code to lower file size. Possible ?
 
Sorry, I mixed my columns and rows. My (0,-3) should be (-3,0), and (0,3)
should be (3,0)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Corey" wrote in message
...
thanks for the reply Bob,
I get an error as soon as the cell C11 is selected at this line:
If target.Value = "" Or target.Offset(0, -3).Value = "" Then exit sub

The offset(0, -3).value has a value in it.

Corey....
"Bob Phillips" wrote in message
...
I ope I have got the ranges and logic correct

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Const WS_RANGE1 As String =
"C8,C12,F8,I8,I12,L8,L11,O8,O12,R8,R12,U8,U11"
Const WS_RANGE2 As String =
"C11,C15,F9,I9,I15,L19,L2,O9,O15,R9,R15,U9,U12 "

Const msg As String = _
"There is an overlap in the Times Entered." & vbNewLine & _
"The next Start Time needs to be equal or greater than the previous
Finish Time."

If Not Intersect(target, Range(WS_RANGE1)) Is Nothing Then
If target.Value = "" Or target.Offset(0, 3).Value = "" Then
Exit Sub
End If
If target.Offset(0, 3).Value < target.Value And _
target.Offset(0, 3).Value < Range("V17").Value Then
MsgBox msg, , "...."
target.Offset(0, 3).ClearContents
target.Offset(0, 3).Select
End If
ElseIf Not Intersect(target, Range(WS_RANGE2)) Is Nothing Then
If target.Value = "" Or target.Offset(0, -3).Value = "" Then
Exit Sub
End If
If target.Value < target.Offset(0, -3).Value And _
target.Value < Range("V17").Value Then
MsgBox msg, , "...."
target.ClearContents
target.Select
End If


End Sub



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Corey" wrote in message
.. .
The code below is part of a TimeSheet i have set up.
The code below is to prevent an overlap in job finish/start times,
unless "V17".value is selected.

Is there a way i can simplify the code to be more efficiently written?
I am trying to reduce th size of the file.

((Private Sub Worksheet_SelectionChange(ByVal target As Range)
If Range("C8") = "" Then Exit Sub
If Range("C11") = "" Then Exit Sub
If Range("C11").Value < Range("C8").Value And Range("C9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("C11").ClearContents
Range("C11").Select
End If
If Range("C15") < "" And Range("C15") < Range("C12") And
Range("C11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...."
Range("C15").ClearContents
Range("C15").Select
End If
If Range("F8") = "" Then Exit Sub
If Range("F11") = "" Then Exit Sub
If Range("F11").Value < Range("F8").Value And Range("F9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...."
Range("F11").ClearContents
Range("F11").Select
End If
If Range("F15") < "" And Range("F15") < Range("F12") And
Range("F11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("F15").ClearContents
Range("F15").Select
End If
If Range("I8") = "" Then Exit Sub
If Range("I11") = "" Then Exit Sub
If Range("I11").Value < Range("I8").Value And Range("I9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...."
Range("I11").ClearContents
Range("I11").Select
End If
If Range("I15") < "" And Range("I15") < Range("I12") And
Range("I11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...."
Range("I15").ClearContents
Range("I15").Select
End If
If Range("L8") = "" Then Exit Sub
If Range("L11") = "" Then Exit Sub
If Range("L11").Value < Range("L8").Value And Range("Ll9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...."
Range("L11").ClearContents
Range("L11").Select
End If
If Range("L15") < "" And Range("L15") < Range("L12") And
Range("L11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, "...." Range("L15").ClearContents
Range("L15").Select
End If
If Range("O8") = "" Then Exit Sub
If Range("O11") = "" Then Exit Sub
If Range("O11").Value < Range("O8").Value And Range("O9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("O11").ClearContents
Range("O11").Select
End If
If Range("O15") < "" And Range("O15") < Range("O12") And
Range("O11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("O15").ClearContents
Range("O15").Select
End If
If Range("R8") = "" Then Exit Sub
If Range("R11") = "" Then Exit Sub
If Range("R11").Value < Range("R8").Value And Range("R9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("R11").ClearContents
Range("R11").Select
End If
If Range("R15") < "" And Range("R15") < Range("R12") And
Range("R11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, ""
Range("R15").ClearContents
Range("R15").Select
End If
If Range("U8") = "" Then Exit Sub
If Range("U11") = "" Then Exit Sub
If Range("U11").Value < Range("U8").Value And Range("U9").Value <
Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("U11").ClearContents
Range("U11").Select
End If
If Range("U15") < "" And Range("U15") < Range("U12") And
Range("U11").Value < Range("V17").Value Then
MsgBox "There is an overlap in the Times Entered." & vbCrLf & "The next
Start Time needs to be equal or greater than the previous Finish Time.",
, " ...."
Range("U15").ClearContents
Range("U15").Select
End If
End Sub


COrey....


All times are GMT +1. The time now is 12:33 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com