| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.... |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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] |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I simplify a VB code?????? | Excel Discussion (Misc queries) | |||
| code to check file size everytime an Excel file is opened | Excel Programming | |||
| Simplify this code | Excel Programming | |||
| File Size Code not working | Excel Programming | |||
| Help to simplify code. | Excel Programming | |||
Linear Mode
