Enable / Disable Ribbon tab buttons

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

Enable / Disable Ribbon tab buttons
Button state does not change when the enabled property is set 
Darryl Holloman




Posts: 4
 Joined: 2012-09-29
I have a ribbon tab with 5 buttons in an Excel Add-In. 4 of the buttons enable / disable fine depending on the state of an external application (COM connected). The 5th button should enable / disable when a cell is sel ected, based on the content of the cell's comment. 

procedure TAddInModule.CellSelected;
var
  id: string;
  bstate: boolean;
begin
  bstate := false;
  try
    if not (haspc) or (excelapp.ActiveCell.Comment = nil) then exit;
    id := excelapp.ActiveCell.Comment.Shape.AlternativeText;
    if (id = '') or not (pos('PSTakeoff=', id) = 1) then exit;
    delete(id, 1, 10);
    bstate := not (pc.GetItem(id) = nil);
  finally
    PlanSwiftribbontab.Controls[0].AsRibbonGroup.Controls[4].AsRibbonButton.Enabled := bstate;
    if bstate then
      PlanSwiftribbontab.Controls[0].AsRibbonGroup.Controls[4].AsRibbonButton.ScreenTip := id
    else
      PlanSwiftribbontab.Controls[0].AsRibbonGroup.Controls[4].AsRibbonButton.ScreenTip := '';
    // Output state to A1 and A2 for debug...
    excelapp.Cells.Item [1,1].formula := booltostr(bstate, true);
    excelapp.Cells.Item [2,1].formula := PlanSwiftribbontab.Controls[0].AsRibbonGroup.Controls[4].AsRibbonButton.ScreenTip;
  end;
end;


I can tell fr om cells A1 and A2 then the code is running and the values are correct but the state of the button never changes and the screen tip does not show.

Any help??
Posted 01 Oct, 2012 17:53:25 Top
Andrei Smolin


Add-in Express team


Posts: 18823
 Joined: 2006-05-11
Hi Darryl,

Here's the simplified version of your code; it works fine for me: 

procedure TAddInModule.adxExcelAppEvents1SheetSelectionChange(
  ASender: TObject; const Sh: IDispatch; const Target: ExcelRange);
begin
  CellSelected;
end;

procedure TAddInModule.CellSelected;
var
  id: string;
  bstate: boolean;
begin
  bstate := false;
  try
    if (excelapp.ActiveCell.Comment = nil) then exit;
    id := excelapp.ActiveCell.Comment.Shape.AlternativeText;
    bstate := not (id = 'True');
  finally
    adxRibbonTab1.Controls[0].AsRibbonGroup.Controls[0].AsRibbonButton.Enabled := bstate;
    if bstate then
      adxRibbonTab1.Controls[0].AsRibbonGroup.Controls[0].AsRibbonButton.ScreenTip :=
        excelapp.ActiveCell.AddressLocal[False, False, xlA1, EmptyParam, EmptyParam]
    else
      adxRibbonTab1.Controls[0].AsRibbonGroup.Controls[0].AsRibbonButton.ScreenTip := '';
    // Output state to A1 and A2 for debug...
    excelapp.Cells.Item [1,1].formula := booltostr(bstate, true);
    excelapp.Cells.Item [2,1].formula := adxRibbonTab1.Controls[0].AsRibbonGroup.Controls[0].AsRibbonButton.ScreenTip;
  end;
end;



Andrei Smolin
Add-in Express Team Leader
Posted 02 Oct, 2012 02:44:39 Top
dholloman




Posts: 4
 Joined: 2012-09-29
Thanks for the quick response.

It appears that the problem comes calling "CellSel ected" from a taskpane window, not from within the TAddInModule unit. I have just found that I can call it from the AddInModule unit and it works correctly. I also found that if I call it from the taskpane window twice it works.

Code from the taskpane window . 
procedure TCoDefineTemplate.CellSelectionChange(ASender: TObject; const Sh: IDispatch; const Target: ExcelRange);
begin
  if not (CurrentCell = nil) then
    CellSelectionBeforeChange(nil, sh);
  CurrentCell := Target;
  adxCoSwiftXL10.CellSelected;
  adxCoSwiftXL10.CellSelected;
end;


Any idea why this would be? Now that I know a workaround I can make it work but it seems like a bit of a hack.
Posted 02 Oct, 2012 03:25:40 Top
dholloman




Posts: 4
 Joined: 2012-09-29
OK, now that is not working anymore....
Posted 02 Oct, 2012 03:45:44 Top
Andrei Smolin


Add-in Express team


Posts: 18823
 Joined: 2006-05-11
The below works for me:

Module: 
procedure TAddInModule.adxExcelAppEvents1SheetSelectionChange(
  ASender: TObject; const Sh: IDispatch; const Target: ExcelRange);
begin
  (adxExcelTaskPanesManager1.Items[0].TaskPaneInstance as TadxExcelTaskPane1).SheetSelectionChange(ASender, Sh, Target);
end;

procedure TAddInModule.CellSelected;
var
  id: string;
  bstate: boolean;
begin
  bstate := false;
  try
    if (excelapp.ActiveCell.Comment = nil) then exit;
    id := excelapp.ActiveCell.Comment.Shape.AlternativeText;
    bstate := not (id = 'True');
  finally
    adxRibbonTab1.Controls[0].AsRibbonGroup.Controls[0].AsRibbonButton.Enabled := bstate;
    if bstate then
      adxRibbonTab1.Controls[0].AsRibbonGroup.Controls[0].AsRibbonButton.ScreenTip :=
        excelapp.ActiveCell.AddressLocal[False, False, xlA1, EmptyParam, EmptyParam]
    else
      adxRibbonTab1.Controls[0].AsRibbonGroup.Controls[0].AsRibbonButton.ScreenTip := '';
    // Output state to A1 and A2 for debug...
    excelapp.Cells.Item [1,1].formula := booltostr(bstate, true);
    excelapp.Cells.Item [2,1].formula := adxRibbonTab1.Controls[0].AsRibbonGroup.Controls[0].AsRibbonButton.ScreenTip;
  end;
end;


Pane: 
procedure TadxExcelTaskPane1.SheetSelectionChange(ASender: TObject;
  const Sh: IDispatch; const Target: ExcelRange);
begin
  (self.AddinModule as TAddinModule).CellSelected;
end;



Andrei Smolin
Add-in Express Team Leader
Posted 02 Oct, 2012 05:24:57 Top
dholloman




Posts: 4
 Joined: 2012-09-29
Thank you for your help, I do have it working now.
Posted 02 Oct, 2012 06:02:39 Top