Procedure too Large (worksheet level change)

[kwp004]

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

 

[mole999]

you might try (reducing the script slightly) as you are testing for both bits

If Target.Address = "$G$6" And 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

 

[kwp004]

Thanks. How would I simply the code for for the following?

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"

Is it just this?

If Target.Address = "$C$6" Or Target.Address = "$D$6" Or Target.Address = "$G$6" Or Target.Address = "$I$6" And Range("BA6") = "XXX" Then

Will the and/or combination create an error? (I'd want to be triggered if you click C6,D6,G6, or I6, if range BA6=XXX).


Also, this will help, but I think my code will still be too large. Does anyone know how I could reduce it further? Is it possible to create some kind of a loop?

Thanks!

 

[ExcelMaster89]

Unfortunately, what you're trying to accomplish is currently not possible in excel. The best thing you can do is to make an instruction manual to tell users not to make those mistakes.