Excel Sheet Change Event fires twice

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

Excel Sheet Change Event fires twice
Excel Sheet Change Event fires twice 
C-MOTS


Guest


Hi,

I am executing a procedure that fills data from database on Sheet Change Event.
i.e only when the Value in Cell D1 is Changed.
Problem is Sheet Change Event is fired twice why this is happening?

Also I am using a task pane it is also loaded again. i.e there are now two Task Panes
How to avoid this?

Regards,
Nadesh Thevar
Posted 07 Oct, 2014 04:47:42 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Hello Nadesh,

Do you modify the Excel sheet in that event? Do you modify the cell being modified (D1) or some other cell(s)? When you receive the event for the second time, does the range parameter points to the other cell(s)?


Andrei Smolin
Add-in Express Team Leader
Posted 07 Oct, 2014 05:17:36 Top
C-MOTS


Guest


Only When the Cell Value of Cell D1 is changed by way of user input.

procedure TAddInModule.ExlAppEventsSheetChange(ASender: TObject;const Sh: IDispatch; const Target: ExcelRange);
begin
if RunAutoUpdate(Target) then
begin
UpdateExcelSheet(ExlObj.ActiveWorkBook.ActiveSheet,Target.Value2);
end;

end;

RunAutoUpdate(Target) Checks if the Target.Address is Cell D1 it will return true.

UpdateExcelSheet Fills Data - procedure is executed till the end and then its fired again.

Cell D1 is not modified through Code here its by User input only
Posted 07 Oct, 2014 05:57:15 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Nadesh,

I suppose ExlAppEventsSheetChange gets called for the second time because UpdateExcelSheet modifies some cell(s). Is this the case?


Andrei Smolin
Add-in Express Team Leader
Posted 07 Oct, 2014 09:44:57 Top
C-MOTS


Guest


adxCOMAddInModuleCreate event is also getting fired on ExlAppEventsSheetChange.

on adxCOMAddInModuleAddInInitialize event
adxCMExcelAddIn := Self this line is there can this be a problem?
It was an auto generated code only
Posted 08 Oct, 2014 00:56:12 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Hello Nadesh,

A test add-in handling the SheetChange event as shown below demonstrates that modifying a cell when in the SheetChange event generates another SheetChange event. 

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;



Andrei Smolin
Add-in Express Team Leader
Posted 08 Oct, 2014 08:14:55 Top