Help with a Change Event and Error Handling

Experts Please see the attached workbook:

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
LVL 1
-PolakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:

1. Change_Event

The quick answer here is that the boolean variable - CascadeSelections is being set to True in the ModifyFHPercentage Sub, so the Worksheet_Change event is never executed (it's exited at the start of the sub).

2. Error Handling on multiple-cell actions

You can test for the size of the target and then use that to determine processing.  Something like:
If Target.Rows.Count > 1 then
    'skip processing or alert user
Else
    'normal processing
End If

Open in new window


Your use of Select Case Target.Address could be modified here, since the use is currently superfluous; that is, you don't really need it as it is currently applied.  But, taking the previous suggestion of noting the row count, you could do this instead (Daily sorties section):
     'Planning for DAILY Sorties
        If Not Intersect(Target, Range("E15:E45")) Is Nothing Then
            Select Case Target.Rows.Count
                Case 1
                    nn = CInt(Mid(Target.Address, InStr(2, Target.Address, "$") + 1))
                    If Range("O" & nn) > 0 And Range("Q" & nn) > 0 Then
                            SortieFHCompat = True
                            Range("$F$" & nn).Value = Round(((Target.Value * Range("O" & nn)) / 12), 0)
                            Range("$G$" & nn).Value = Round((Target.Value * Range("O" & nn)), 0)
                            Range("$K$" & nn).Value = Round((Target.Value * Range("Q" & nn)), 1)
                            Range("$L$" & nn).Value = Round(((Target.Value * Range("Q" & nn)) * Range("O" & nn) / 12), 0)
                            Range("$M$" & nn).Value = Round(((Target.Value * Range("Q" & nn)) * Range("O" & nn)), 0)
                    End If
                Case Else
                    'Do not process
            End Select
        End If

Open in new window


=========================
Additionally, several sections of code can be compressed significantly.  On PlanningtoHistoricalValues, the two sections that loop through rows 15-33 and 42-45 can be combined into one:
        For Rw = 15 To 45  'skip 34-41
            If Rw <= 33 Or Rw >= 42 Then
                WS.Cells(Rw, 5) = WS.Cells(Rw, 2).Value * (1 + (InputPercent / 100))
                WS.Cells(Rw, 6) = WS.Cells(Rw, 3).Value * (1 + (InputPercent / 100))
                WS.Cells(Rw, 7) = WS.Cells(Rw, 4).Value * (1 + (InputPercent / 100))
                WS.Cells(Rw, 11) = WS.Cells(Rw, 8).Value * (1 + (InputPercent / 100))
                WS.Cells(Rw, 12) = WS.Cells(Rw, 9).Value * (1 + (InputPercent / 100))
                WS.Cells(Rw, 13) = WS.Cells(Rw, 10).Value * (1 + (InputPercent / 100))
                WS.Cells(Rw, 15) = WS.Cells(Rw, 14).Value * (1 + (InputPercent / 100))
                WS.Cells(Rw, 17) = WS.Cells(Rw, 16).Value * (1 + (InputPercent / 100))
                WS.Cells(Rw, 19) = WS.Cells(Rw, 18).Value * (1 + (InputPercent / 100))
                'WS.Cells(Rw, 23) = "=LOOKUP($M$2,'Par-Indirects'!$O$10:$P$20)"
                WS.Cells(Rw, 25) = WS.Cells(Rw, 24).Value * (1 + (InputPercent / 100))
                WS.Cells(Rw, 28) = WS.Cells(Rw, 27).Value * (1 + (InputPercent / 100))
            End If
        Next Rw

Open in new window

and similarly for other subs.  Just exclude the rows not processed.

Also, the massive sections in the Worksheet_Change event for processing the upper section (M2, M3, M4) can be significantly reduced by looping through all sheets except the active sheet like so:
    Select Case Left(Target.Address, 4)
        Case "$M$2"
            CascadeSelections = True
            Range("M3:P3").ClearContents
            For Each ws In ActiveWorkbook.Worksheets
                If ws.Name <> Me.Name Then
                    ws.Range("M2").Value = Target.Value
                    ws.Range("M3:P3").ClearContents
                End If
            Next ws
        Case "$M$3"
            CascadeSelections = True
            For Each ws In ActiveWorkbook.Worksheets
                If ws.Name <> Me.Name Then
                    ws.Range("M3").Value = Target.Value
                End If
            Next ws
        Case "$M$4"
            CascadeSelections = True
            For Each ws In ActiveWorkbook.Worksheets
                If ws.Name <> Me.Name Then
                    ws.Range("M4").Value = Target.Value
                End If
            Next ws
    End Select

Open in new window


I've attached a modified copy for you to review.

Regards,
-Glenn
EESafe-ManualMode-Errors-mod.xlsm
0
-PolakAuthor Commented:

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 time
With 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.
0
Glenn RayExcel VBA DeveloperCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Glenn RayExcel VBA DeveloperCommented:
Polak,

I've updated and optimized the code to handle the Worksheet_Change event processing for multiple values in the lower section.  This will allow for multiple rows to be processed if cell values are deleted or if multiple cells are edited (ex., using [Ctrl]+[Enter] to apply the active cell value to all selected cells).

It also has a basic stop set up if a user edits/deletes across multiple columns.  

-Glenn
EESafe-ManualMode-Errors-mod.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
FWIW, typically what you would do for multiple amendments is loop through just the intersection of Target and the range of interest, not the whole of target:

    If Not Intersect(Target, Range("E15:E45")) Is Nothing Then
        For Each cl In Intersect(Target, Range("E15:E45")).Cells

Open in new window


and so on.
0
Glenn RayExcel VBA DeveloperCommented:
@Rory,  that certainly is more economical than what I coded:
    If Not Intersect(Target, Range("E15:E45")) Is Nothing Then
        Set rng = Range(Target.Address)
        For Each cl In rng

Open in new window


 ...even though it does only iterate though the active selection and not the entire test range.

That's good to know; thanks.

-Glenn
0
-PolakAuthor Commented:
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.
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

Open in new window

0
Glenn RayExcel VBA DeveloperCommented:
^Great Idea!  I need to remember to apply that in some of my own code.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.