Solved

Help with a Change Event and Error Handling

Posted on 2014-10-16
8
234 Views
Last Modified: 2014-10-17
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
0
Comment
Question by:-Polak
  • 5
  • 2
8 Comments
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 450 total points
ID: 40385181

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
 
LVL 1

Author Comment

by:-Polak
ID: 40385259

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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40385456
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 27

Accepted Solution

by:
Glenn Ray earned 450 total points
ID: 40385585
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 50 total points
ID: 40386564
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40386846
@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
 
LVL 1

Author Closing Comment

by:-Polak
ID: 40387135
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40387161
^Great Idea!  I need to remember to apply that in some of my own code.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question