Help with a Change Event and Error Handling
Experts Please see the attached workbook:
PS On #1 I know that I could just have the calculations run at the completion of te ModifyFHPercentage sub, However, without using nn='s to define the row of the target.address that would be a lot of ranges/cells to spell out for the calculations (the value in Q and O effects the calculation row by row).
You're help is appreciated!
EESafe-ManualMode-Errors.xlsm
1. Change Event Issue
Click the "Increase Flight / Float Hours by X%" button (ModifyFHPercentage Sub) and enter in a any %. Accordingly, Range K15:M33 and K:42:K45 updates. However, you'll notice that within the Worksheet Change sub there are 3 separate events that should run when changes are made in Ranges K15:K45, L15:L45, and/or M15:M45, the corresponding calculations update Ranges E, F, and G in the same rows. However, when ModifyFHPercentage is run these ranges do not see the worksheet change and do not update. Is there a work around?2. Error Handling on Worksheet Change events
If a user drags and drops or tries to mass-delete (more than 1 row of data) Yellow values in ranges E,F,G,K,L,M,O, or Q (rows 15-45) a Type-Mismatch Run-time Error 13 appears. I think this is because in my change events I'm using nn = CInt(Mid(Target.Address, InStr(2, Target.Address, "$") + 1)) to determine the appropriate row for the target.address. I've been using On Error Resume Next but I know that's not what I should be doing.... Moreover, if a user does a drag and drop a value, the values in dependent cells do not update because of the error.PS On #1 I know that I could just have the calculations run at the completion of te ModifyFHPercentage sub, However, without using nn='s to define the row of the target.address that would be a lot of ranges/cells to spell out for the calculations (the value in Q and O effects the calculation row by row).
You're help is appreciated!
EESafe-ManualMode-Errors.xlsm
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Other than giving then skipping processing and alerting the user is there anyway we can force the worksheet event calculations to update values respective of all of the rows they may have changed data on?
The code I changed (using the Select Case method) will allow multiply-selected cells to be deleted with no effect on any other cells. Currently, if a cell value is deleted, dependent cells are set to zero.
If you want multiply-selected cells to be processed, I'm thinking the entire section would need to be changed to loop through all selected cells (even if just one) and then processed. One issue I just noticed would be if a user selected more than one column of data - that would cause an error and would need to be trapped in advance (i.e., Target.Columns.Count > 1)
I'll look into it and see if I can come up with something.
-Glenn
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Rory, that certainly is more economical than what I coded:
...even though it does only iterate though the active selection and not the entire test range.
That's good to know; thanks.
-Glenn
If Not Intersect(Target, Range("E15:E45")) Is Nothing Then
Set rng = Range(Target.Address)
For Each cl In rng...even though it does only iterate though the active selection and not the entire test range.
That's good to know; thanks.
-Glenn
ASKER
Probably one of the most thorough and educational solutions I've ever received, thanks guys!
PS Added a quick undo to your user alert Glenn.
PS Added a quick undo to your user alert Glenn.
If Target.Columns.Count > 1 Then
MsgBox "Please do not edit or delete across columns.", vbCritical + vbOKOnly, "Notice"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
CascadeSelections = False
Exit Sub
End If
^Great Idea! I need to remember to apply that in some of my own code.
ASKER
1. Change_Event
Ah, yes! forgot I added that to the ManualModeButtons module when I was experiencing an issue with values from $M$2X, $M$3X, $M$4X populating in M2 and M3 after running the %Modify sub and prior to adding the If Not Intersect(Target, Range("M2:P4")) Is Nothing Then bit to the worksheet cascading!2. Error Handling on multiple-cell actions
I see, there would be a cases such as "Operational Days" where the end-user would have a practical need to drag and drop down values over multiple-cells. Moreover, there would many instances where a mass-delete would be handy. Other than giving then skipping processing and alerting the user is there anyway we can force the worksheet event calculations to update values respective of all of the rows they may have changed data on? Or is this a case where I have to simply given them an alert that says only one input cell may be modified at a timeWith respect to all of your code optimization, THANK YOU, hope you can see that I'm learning, but your solutions are by far the most educational on here.