2

I have an application that loads data (using a custom button in ribbon) from a cube into Excel.

When a row is updated it fires the Worksheet_Change event and adds this row to an array to write back to the cube (instead of rewriting all the rows).

As part of business requirements, after changes are made, a VBA function resorts non-zero values to the top of the sheet in alphabetical order. This is done by a button with a macro assigned.

The problem is, when a user edits another cell AFTER the macro has run the worksheet change event handler does not fire. When I click the save button (custom button in ribbon) to write the data back to the cube, the array of modified rows is null and my function to handle that returns a message saying "No changes have been made" even though I have edited a cell.

I checked my macro code and I'm not setting Application.EnableEvents to false.

My theory is, since the code for the load/save data is in the ribbon, and the code for the resort function is in VBA, it's causing some issue with scope and references so the application doesn't know a cell updated.

I tried debugging the whole solution, and put breakpoints in my event handlers for Sheet Change events, they aren't firing.

I will try rewriting the Resort code from VBA to include it in the ribbon in C#, and see if that magically fixes this issue.

Sub Resort()
'
' Macro1 Macro
'

Dim LastRow As Long
ActiveSheet.Unprotect "hello"

Application.ScreenUpdating = False
Application.Calculation = xlAutomatic
Sheets("Hidden_Lookup").Visible = True

Sheets("Input").UsedRange
Sheets("Input").Range("$D$14:$I$1000000").AutoFilter Field:=1
Sheets("Input").Range("$D$14:$I$1000000").AutoFilter Field:=2
Sheets("Input").Range("$D$14:$I$1000000").AutoFilter Field:=3
Sheets("Input").Range("$D$14:$I$1000000").AutoFilter Field:=4
Sheets("Input").Range("$D$14:$I$1000000").AutoFilter Field:=5
Sheets("Input").Range("$D$14:$I$1000000").AutoFilter Field:=6
LastRow = Sheets("Input").Cells(Sheets("Input").Rows.Count,                 
"D").End(xlUp).Row

Sheets("Hidden_Lookup").Activate

    Columns("A:E").Select
    Selection.Delete Shift:=xlToLeft

'Sub Cat
Range("A15").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(IFERROR(IF(AND(Input!RC[8] = ""Working"", Input!RC[7] =     
""Demand Dollars"", Input!RC[21] <>0), 1,0),0)=1,Input!RC[4],"""")"

'Brand ID
Range("B15").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(IFERROR(IF(AND(Input!RC[7] = ""Working"", Input!RC[6] =     
""Demand Dollars"", Input!RC[20] <>0), 1,0),0)=1,Input!RC[-1],"""")"

'Demand Type
Range("C15").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(IFERROR(IF(AND(Input!RC[6] = ""Working"", Input!RC[5] =     
""Demand Dollars"", Input!RC[19] <>0), 1,0),0)=1,Input!RC[4],"""")"

'Concat
Range("D15").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-3]&""-""&RC[-2]&""-""&RC[-1]"

'Flag
Range("E15").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(AND(Input!RC[4] = ""Working"", Input!RC[3] = ""Demand     
Dollars"", Input!RC[17] <>0), 1,0),0)"

ActiveSheet.Range("A15:E15").Select
Selection.AutoFill Destination:=Range("A15:E" & LastRow),     
Type:=xlFillDefault
Calculate

Sheets("Input").Activate

Columns("AD:AD").Select
Selection.ClearContents

Range("AD15").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-25]&""-""& RC[-29]&""-    ""&RC[-23],Hidden_Lookup!C[-26]:C[-25],2,FALSE),0)"

Selection.AutoFill Destination:=Range("AD15:AD" & LastRow),     Type:=xlFillDefault
Calculate

ActiveWorkbook.Worksheets("Input").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Input").Sort.SortFields.Add Key:=Range( _
        "AD15:AD" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending,     DataOption:= _
        xlSortNormal

    ActiveWorkbook.Worksheets("Input").Sort.SortFields.Add     Key:=Range("E15:E" & LastRow _
        ), SortOn:=xlSortOnValues, Order:=xlAscending,     DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Input").Sort.SortFields.Add     Key:=Range("F15:F" & LastRow _
        ), SortOn:=xlSortOnValues, Order:=xlAscending,     DataOption:=xlSortNormal

    ActiveWorkbook.Worksheets("Input").Sort.SortFields.Add     Key:=Range("A15:A" & LastRow _
        ), SortOn:=xlSortOnValues, Order:=xlAscending,     DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Input").Sort.SortFields.Add     Key:=Range("G15:G" & LastRow _
        ), SortOn:=xlSortOnValues, Order:=xlAscending,     DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Input").Sort.SortFields.Add     Key:=Range("H15:H" & LastRow _
        ), SortOn:=xlSortOnValues, Order:=xlAscending,     DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Input").Sort.SortFields.Add     Key:=Range("I15:I" & LastRow _
        ), SortOn:=xlSortOnValues, Order:=xlAscending,     DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Input").Sort
        .SetRange Range("A14:AD" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    ActiveSheet.Range("$D$14:$I$" & LastRow).AutoFilter Field:=5,     Criteria1:=Array( _
        "Demand Dollars", "GM %", "GM Dollars", "Hours", "TS Count"),     Operator:= _
        xlFilterValues

    Columns("AD:AD").Select
    Selection.ClearContents

Sheets("Hidden_Lookup").Activate

    Columns("A:E").Select
    Selection.Delete Shift:=xlToLeft

Sheets("Hidden_Lookup").Visible = False

Sheets("Input").Activate

ActiveWindow.ScrollColumn = 10
Range("E14").Select
Application.ScreenUpdating = True

ActiveSheet.Protect Password:="hello", AllowFiltering:=True,     AllowFormattingColumns:=True

End Sub

After the Resort function is run, it should still allow worksheet change event handlers in my C# code to be triggered and therefore added to the modifiedRows array and saved/written to the cube.

Community
  • 1
  • 1
Benny
  • 155
  • 3
  • 15
  • 1
    That `Resort` procedure has a lot of cell manipulation, and almost each one would trigger a `Worksheet_Change` from the middle of `Resort`. If that does not happen either, then you probably don't have a Worksheet_Change handler to begin with, just a sub that is called Worksheet_Change and that you call manually from the add-in, but that is located in a place where Excel does not recognize it as an event handler. – GSerg Dec 21 '18 at 17:23
  • Probably (but who knows) unrelated to the problem, but you should really look through https://stackoverflow.com/q/10714251/11683 and fix your `Resort` accordingly. Unrelated to the problem yet again, you [don't need to unprotect each time](https://stackoverflow.com/questions/53453611/vba-excel-how-do-i-use-a-function-as-a-parameter#comment93779918_53453611). – GSerg Dec 21 '18 at 17:24
  • Rewriting it in C# will not magically fix anything. It will introduce many more opportunities for memory leaks though. – Mathieu Guindon Dec 21 '18 at 18:01
  • @GSerg If that was the case, then when I click the save/write to the Cube button from the add in it should trigger the the Worksheet_Change function. – Benny Dec 21 '18 at 18:52
  • Not if VBA code is already running in an endless loop. You can only run one thread at a time in VBA land. You want your functions to run quick and end neatly or you get this state where the user is modifying the sheet while your code is running. – HackSlash Dec 21 '18 at 22:42

0 Answers0