I wrote a worksheet level change and I'm getting the error message "Procedure too Large". As I understand it, since it's at the worksheet level, I can't break the VBA into multiple subs.
Basically, I'm trying to take the VBA below, and have it repeat for rows 7-35. Currently, to do that, I just copied/pasted the code below into MS Word, did find/replace 6 for 7, and pasted the result back into the VBA. I then repeated that process for each row.
Anyone know a smarter way to do this?
Private Sub Worksheet_Change(ByVal Target As Range)
'Quantity Error
If Target.Address = "$G$6" Then
If Range("AT6") = "XXX" Then
MsgBox "Valid Inputs:" & vbCrLf & vbCrLf & "Any #>=0" & vbCrLf & vbCrLf & "All Shares Remaining" & vbCrLf & vbCrLf & "All Shares up to #" & vbCrLf & vbCrLf & "Net Shares" & vbCrLf & vbCrLf & "Sell to Cover", vbInformation, "Must Enter Valid Quantity"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'Contingent Link Error (quantity cell)
If Target.Address = "$G$6" Then
If Range("AU6") = "XXX" Then
MsgBox "For the following orders types, you must first select a contingent order:" & vbCrLf & vbCrLf & "- All Shares Remaining" & vbCrLf & vbCrLf & "- All Shares up to #" & vbCrLf & vbCrLf & "- Net Shares", vbInformation, "Order Must be Contingent"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'Contingent Link Error (contingent cell)
If Target.Address = "$I$6" Then
If Range("AU6") = "XXX" Then
MsgBox "For the following orders types, you must select a contingent order:" & vbCrLf & vbCrLf & "- All Shares Remaining" & vbCrLf & vbCrLf & "- All Shares up to #" & vbCrLf & vbCrLf & "- Net Shares", vbInformation, "Order Must be Contingent"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'ED < SD error
If Target.Address = "$C$6" Or Target.Address = "$D$6" Then
If Range("AV6") = "XXX" Then
MsgBox "REMINDER: Start Date < End Date", vbInformation, "Invalid Date"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'STC error
If Target.Address = "$G$6" Or Target.Address = "$J$6" Or Target.Address = "$K$6" Or Target.Address = "$L$6" Or Target.Address = "$M$6" Or Target.Address = "$N$6" Or Target.Address = "$O$6" Then
If Range("AW6") = "XXX" Then
MsgBox "For a Sell to Cover order, to record an execution:" & vbCrLf & vbCrLf & "- In the quantity column, delete sell to cover, and input the total pre-sale quantity" & vbCrLf & vbCrLf & "- In the executed column, input the execution quantity" & vbCrLf & vbCrLf & "- Any contingent orders should be adjust accordingly", vbInformation, "Invalid Execution Quantity"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'Contingent,Q or STC error
If Target.Address = "$G$6" Or Target.Address = "$I$6" Then
If Range("AX6") = "XXX" Then
MsgBox "Only the following order types can be contingent:" & vbCrLf & vbCrLf & "- All Shares Remaining" & vbCrLf & vbCrLf & "- All Shares up to X" & vbCrLf & vbCrLf & "- Net Shares", vbInformation, "Order Type Cannot be Contingent"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'Contingent,ASR/ASX SD/ED Error
If Target.Address = "$C$6" Or Target.Address = "$D$6" Or Target.Address = "$G$6" Or Target.Address = "$I$6" Then
If Range("AY6") = "XXX" Then
MsgBox "REMINDER: end date of parent order < start date of a contingent order", vbInformation, "Invalid Date (contingent order)"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'Contingent,ASR or ASX link error
If Target.Address = "$G$6" Or Target.Address = "$I$6" Then
If Range("AZ6") = "XXX" Then
MsgBox "The following order types must directly follow their parent order:" & vbCrLf & vbCrLf & "- All Shares Remaining" & vbCrLf & vbCrLf & "- All Shares up to X", vbInformation, "Order Must Directly Proceed Parent Order"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'Contingent,NS SD/ED Error
If Target.Address = "$C$6" Or Target.Address = "$D$6" Or Target.Address = "$G$6" Or Target.Address = "$I$6" Then
If Range("BA6") = "XXX" Then
MsgBox "REMINDER: for a Net Shares order, the start date cannot come prior to the start date of the parent All Shares up to X order ", vbInformation, "Invalid Date (net shares contingent order)"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'Contingent,NS link error
If Target.Address = "$G$6" Or Target.Address = "$I$6" Then
If Range("BB6") = "XXX" Then
MsgBox "REMINDER: a Net Shares order can only be contingent to an All Shares Up to X order", vbInformation, "Invalid Net Shares Order"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub
Basically, I'm trying to take the VBA below, and have it repeat for rows 7-35. Currently, to do that, I just copied/pasted the code below into MS Word, did find/replace 6 for 7, and pasted the result back into the VBA. I then repeated that process for each row.
Anyone know a smarter way to do this?
Private Sub Worksheet_Change(ByVal Target As Range)
'Quantity Error
If Target.Address = "$G$6" Then
If Range("AT6") = "XXX" Then
MsgBox "Valid Inputs:" & vbCrLf & vbCrLf & "Any #>=0" & vbCrLf & vbCrLf & "All Shares Remaining" & vbCrLf & vbCrLf & "All Shares up to #" & vbCrLf & vbCrLf & "Net Shares" & vbCrLf & vbCrLf & "Sell to Cover", vbInformation, "Must Enter Valid Quantity"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'Contingent Link Error (quantity cell)
If Target.Address = "$G$6" Then
If Range("AU6") = "XXX" Then
MsgBox "For the following orders types, you must first select a contingent order:" & vbCrLf & vbCrLf & "- All Shares Remaining" & vbCrLf & vbCrLf & "- All Shares up to #" & vbCrLf & vbCrLf & "- Net Shares", vbInformation, "Order Must be Contingent"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'Contingent Link Error (contingent cell)
If Target.Address = "$I$6" Then
If Range("AU6") = "XXX" Then
MsgBox "For the following orders types, you must select a contingent order:" & vbCrLf & vbCrLf & "- All Shares Remaining" & vbCrLf & vbCrLf & "- All Shares up to #" & vbCrLf & vbCrLf & "- Net Shares", vbInformation, "Order Must be Contingent"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'ED < SD error
If Target.Address = "$C$6" Or Target.Address = "$D$6" Then
If Range("AV6") = "XXX" Then
MsgBox "REMINDER: Start Date < End Date", vbInformation, "Invalid Date"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'STC error
If Target.Address = "$G$6" Or Target.Address = "$J$6" Or Target.Address = "$K$6" Or Target.Address = "$L$6" Or Target.Address = "$M$6" Or Target.Address = "$N$6" Or Target.Address = "$O$6" Then
If Range("AW6") = "XXX" Then
MsgBox "For a Sell to Cover order, to record an execution:" & vbCrLf & vbCrLf & "- In the quantity column, delete sell to cover, and input the total pre-sale quantity" & vbCrLf & vbCrLf & "- In the executed column, input the execution quantity" & vbCrLf & vbCrLf & "- Any contingent orders should be adjust accordingly", vbInformation, "Invalid Execution Quantity"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'Contingent,Q or STC error
If Target.Address = "$G$6" Or Target.Address = "$I$6" Then
If Range("AX6") = "XXX" Then
MsgBox "Only the following order types can be contingent:" & vbCrLf & vbCrLf & "- All Shares Remaining" & vbCrLf & vbCrLf & "- All Shares up to X" & vbCrLf & vbCrLf & "- Net Shares", vbInformation, "Order Type Cannot be Contingent"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'Contingent,ASR/ASX SD/ED Error
If Target.Address = "$C$6" Or Target.Address = "$D$6" Or Target.Address = "$G$6" Or Target.Address = "$I$6" Then
If Range("AY6") = "XXX" Then
MsgBox "REMINDER: end date of parent order < start date of a contingent order", vbInformation, "Invalid Date (contingent order)"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'Contingent,ASR or ASX link error
If Target.Address = "$G$6" Or Target.Address = "$I$6" Then
If Range("AZ6") = "XXX" Then
MsgBox "The following order types must directly follow their parent order:" & vbCrLf & vbCrLf & "- All Shares Remaining" & vbCrLf & vbCrLf & "- All Shares up to X", vbInformation, "Order Must Directly Proceed Parent Order"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'Contingent,NS SD/ED Error
If Target.Address = "$C$6" Or Target.Address = "$D$6" Or Target.Address = "$G$6" Or Target.Address = "$I$6" Then
If Range("BA6") = "XXX" Then
MsgBox "REMINDER: for a Net Shares order, the start date cannot come prior to the start date of the parent All Shares up to X order ", vbInformation, "Invalid Date (net shares contingent order)"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
'Contingent,NS link error
If Target.Address = "$G$6" Or Target.Address = "$I$6" Then
If Range("BB6") = "XXX" Then
MsgBox "REMINDER: a Net Shares order can only be contingent to an All Shares Up to X order", vbInformation, "Invalid Net Shares Order"
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End Sub