OnSheetChange event won't fire

Add-in Express™ Support Service
That's what is more important than anything else

OnSheetChange event won't fire
 
Michael Hovdan




Posts: 37
Joined: 2010-06-26
The Excel OnSheetChange event (in the TadxExcelAppEvents component) will not fire. Other events work fine, such as OnWorkbookActivate, OnWorkbookBeforeClose and others.

What could the problem be?
Posted 09 Sep, 2015 12:38:05 Top
Andrei Smolin


Add-in Express team


Posts: 14140
Joined: 2006-05-11
Hello Michael,

Here's the event procedure that work for me in Excel 2010-2013 32bit:

procedure TAddInModule.adxExcelAppEvents1SheetChange(ASender: TObject;
  const Sh: IDispatch; const Target: ExcelRange);
var
  address: String;
  range: ExcelRange;
begin
  address := Target.Address[false, false, xlA1, EmptyParam, EmptyParam];
  OutputDebugString(PAnsiChar('!!!' + address));
  if address = 'D1' then
  begin
    range := (ExcelApp.ActiveSheet as Excel2000._Worksheet).Range['A1', EmptyParam];
    range.Value := Random(100000);
    range := nil;
  end;
end;


Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 10 Sep, 2015 01:59:26 Top
Michael Hovdan




Posts: 37
Joined: 2010-06-26
Thanks Andrei

But my problem is that a change in a spreadsheet cell doesn't fire the event, so the code inside the procedure is never run.

FYI: In this code the variable Rng is a global variable of type ExcelRange.


procedure TAddInModule.adxclpvnts1SheetChange(ASender: TObject; const Sh: IDispatch; const Target: ExcelRange);
begin
  ExcelApp.EnableEvents := False;
  if ExcelApp.Intersect(Target, Rng) <> Null then
    SetCell(MyWorkbook.Worksheets.Item[1], 'A1', 'Haha'); // 
  ExcelApp.EnableEvents := True;
end;
Posted 10 Sep, 2015 05:32:27 Top
Andrei Smolin


Add-in Express team


Posts: 14140
Joined: 2006-05-11
Hello Michael,

Please test this project:

http://temp.add-in-express.com/support/SheetChangeTester-Delphi7Excel.zip

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 10 Sep, 2015 09:30:56 Top
Michael Hovdan




Posts: 37
Joined: 2010-06-26
Nothing happened. The strange thing is that some events get triggered, while others don't. Can you do a teamviewer session?
Posted 10 Sep, 2015 09:46:29 Top
Andrei Smolin


Add-in Express team


Posts: 14140
Joined: 2006-05-11
I'm sorry, I have to leave for home in a couple of minutes. Tomorrow, I'll send you an email. Or you can ping me; I plan to be in the office all the day long.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 10 Sep, 2015 09:53:32 Top
Michael Hovdan




Posts: 37
Joined: 2010-06-26
It will have to wait until Monday, then. I'll send you an email. Have a nice weekend.
Posted 10 Sep, 2015 09:54:56 Top
Michael Hovdan




Posts: 37
Joined: 2010-06-26
One more piece of information:

Excel fires the OnSheetChange event properly in VBA, so the problem must be on the "receiving end" (Delphi/Add-In for VCL).
Posted 10 Sep, 2015 13:45:51 Top
Andrei Smolin


Add-in Express team


Posts: 14140
Joined: 2006-05-11
Please ping me on Monday.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 11 Sep, 2015 09:23:14 Top
Michael Hovdan




Posts: 37
Joined: 2010-06-26
Andrei.

I rewrote the project from scratch and the problem went away (I couldn't reproduce it). Case closed. :)
Posted 21 Sep, 2015 03:56:23 Top