Simplify code to lower file size. Possible ?

[Corey]

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]

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]

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....

 

[Tom Ogilvy]

Range("C11").Offset(0,-3) would be an illegal range reference.

 

[Corey]

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,U11,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....

 

[macropod]

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

 

[bz]

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.

(e-mail address removed) remove ch100-5 to avoid spam trap

 

[Bob Phillips]

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)