Solved

Help with a Change Event and Error Handling

Posted on 2014-10-16
8
219 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now