Modifying CommandBarButton State

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

Modifying CommandBarButton State
Excel appears to duplicate CommandBars when opening multiple workbooks 
Posts 1 - 10 of 11
First | Prev. | 1 2 | Next | Last 
Chris Bishop




Posts: 13
 Joined: 2016-01-25
Hi,

I am using the Add-in-Express version:adxvcl-v801-b1432-std
Delphi 10.1 Berlin Update (2)
Office 2016

I just noticed that when I have multiple workbooks open in the same session that updating CommandBarButtons doesn't behave as I expected.

I keep references to the CommandBarButtons that I have created so I can modify them based on user action.
This seems to work fine until I open a second workbook in the same session. The second workbook actions modify the menu items in the first session but do not affect the second session.

What I had done was simply update the ToolTipText with Click + IntToStr(Clicked). Clicking the button in either workbook updates the ToolTipText just in the first workbook.

If I change my code to not use a reference but to search the ExcelApp.CommandBars to find the button it works as expected. The ToolTipText of the button click is updated only. The other workbook is not affected.

I tested Office2010 and it appears to work as I expected. But I also noticed that it behaves as MDI where Office 2013 and 2016 behave as SDI. Office 2013 and 2016 appear to duplicate the CommandBars when opening a workbook based on the previously selected workbook.

I didn't notice any reference to this in your help documentation. If this is true you may wish to update your help documentation as I spent a day chasing my tail trying to figure out what I was doing wrong.

Here is my test code: 

unit test_IMPL;

interface

uses
  SysUtils, ComObj, ComServ, ActiveX, Variants, Office2000, adxAddIn, test_TLB,
  System.Classes, adxHostAppEvents, Excel2000, Dialogs;

type
  Tcotest = class(TadxAddin, Icotest)
  end;

  TAddInModule = class(TadxCOMAddInModule)
    CmdBars: TadxCommandBar;
    adxExcelAppEvents1: TadxExcelAppEvents;
    procedure OnWorkbookActivate(ASender: TObject; const Wb: _Workbook);
    procedure OnWorkbookAddInInstall(ASender: TObject; const Wb: _Workbook);
  private
    popup: TadxCommandBarPopup;
    button : TadxCommandBarButton;
    clicked : Integer;
    created : boolean;
    procedure OnButtonClick(Sender : TObject);
  protected
  public
  end;

implementation

{$R *.dfm}

procedure TAddInModule.OnButtonClick(Sender: TObject);
var i,j, k: integer;
  popup3: CommandBarPopup;
begin
  // this works
  for i:=1 to ExcelApp.CommandBars.Count do
      if (ExcelApp.CommandBars[i].Name = 'Worksheet Menu Bar') then
      begin
          for j := 1 to ExcelApp.CommandBars[i].Controls.Count do
          begin
              popup3 := CommandBarPopup(ExcelApp.CommandBars[i].Controls.Item[j]);
              if (not popup3.BuiltIn) and (popup3.Tag = 'popuptest') then
              begin
                for k := 1 to popup3.Controls.Count do begin
                  if (popup3.Controls.Item[k].Tag = 'testthis') then begin
                    popup3.Controls.Item[k].TooltipText := 'clicked ' + IntToStr(clicked);
                    clicked := clicked + 1;
                  end;
                end;
              end;
          end;
      end;
end;
{
// this would work in Office2010 but not 2016
var
  popup2: TadxCommandBarPopup;
  button2 : TadxCommandBarButton;
begin
  popup2 := ExcelApp.CommandBars.Item[1] . ControlByOfficeTag('popuptest').AsPopup;
  button2 := popup2.ControlByOfficeTag('testthis').AsButton;
  button2.ToolTipText := 'clicked ' + IntToStr(clicked);
  clicked := clicked + 1;
end;
}
procedure TAddInModule.OnWorkbookActivate(ASender: TObject;
  const Wb: _Workbook);
begin
  if (created) then Exit;

   popup := Self.CmdBars.Controls.Add(TadxControlType.adxPopup, 'popuptest', -1, true).AsPopup;
  popup.Caption := 'testing';
  popup.Visible := true;

  button := popup.Controls.Add(TadxControlType.adxButton, 'testthis',-1, true).AsButton;
  button.Caption := 'test me';
  button.OnClick := OnButtonClick;
  clicked := 0;
  created := true;
end;

procedure TAddInModule.OnWorkbookAddInInstall(ASender: TObject;
  const Wb: _Workbook);
begin
  ShowMessage('hi');
end;

initialization
  TadxFactory.Create(ComServer, Tcotest, CLASS_cotest, TAddInModule);

end.
Posted 12 Apr, 2017 09:48:35 Top
Andrei Smolin


Add-in Express team


Posts: 18823
 Joined: 2006-05-11
Hello Chris,

Alas, you description and code sample give room for interpretation.

Chris Bishop writes:
I keep references to the CommandBarButtons that I have created so I can modify them based on user action.


Have you created these CommandBarButtons using Add-in Express components or not?

Would it be possible for us to have a project demonstrating the issue using Add-in Express components?


Andrei Smolin
Add-in Express Team Leader
Posted 12 Apr, 2017 10:11:18 Top
Chris Bishop




Posts: 13
 Joined: 2016-01-25
Well,

So I got myself half way there.

In my Add-in I make sure not to create the menu more than once as it fires multiple events. But if I do create the menu more than once and keep a reference to each menu created for each work book then modifying the state per workbook works.

In my code ( not the test code above) I have created a WorkBook State Manager. Each time a workbook opens I get the state associated with that workbook. So I moved the references to the COmmandBarButtons from a global object to workbook state object. This worked in modifying the right commandbarbutton but now multiple events fire.

I am thinking on the additional menu creation that I remove the click handler so I have references to menus per workbook but only one event fires per button.

I don't know this is is relevant but the code I inherited has the menu creation in the WorkBookActivate Event instead of the WorkBookOpen event. I am going to look at refactoring that.

Chris
Posted 12 Apr, 2017 10:12:08 Top
Chris Bishop




Posts: 13
 Joined: 2016-01-25
Oh, just saw your response.

I drap and dropped the TadxCommandBar; "Worksheet Menu Bar"
I did not use the designer but used code to add the popup and button.
Posted 12 Apr, 2017 10:14:12 Top
Chris Bishop




Posts: 13
 Joined: 2016-01-25
Yes I have test project you can use. Do you wish for me to email it somewhere?
Posted 12 Apr, 2017 10:15:36 Top
Andrei Smolin


Add-in Express team


Posts: 18823
 Joined: 2006-05-11
You can find the support email address in {Add-in Express installation folder}\readme.txt. Please make sure your email contains a link to this topic.


Andrei Smolin
Add-in Express Team Leader
Posted 12 Apr, 2017 10:19:24 Top
Chris Bishop




Posts: 13
 Joined: 2016-01-25
Project has been sent.
Posted 12 Apr, 2017 10:28:35 Top
Chris Bishop




Posts: 13
 Joined: 2016-01-25
So I think I found the approach that will work.

First I create my menu and button programmatically 


  Self.popup := Self.CmdBars.Controls.Add(TadxControlType.adxPopup, 'popuptest', -1, true).AsPopup;
  Self.popup.Caption := 'testing';
  Self.popup.Visible := true;

  Self.button := popup.Controls.Add(TadxControlType.adxButton, 'testthis',-1, true).AsButton;
  Self.button.Caption := 'test me';
  Self.button.OnClick := OnButtonClick;


Then in the OnButtonClick event if use the TAG to find the control and modify it: 


procedure TAddInModule.OnButtonClick(Sender: TObject);
var
  popup2: TadxCommandBarPopup;
  button2 : CommandBarButton;
begin
 // Self.button.OfficeTag = 'testthis'
  button2 := ExcelApp.CommandBars.Item[1].FindControl(MsoControlButton,EmptyParam,Self.button.OfficeTag,true, true) as CommandBarButton;
  button2.Caption := 'clicked ' + IntToStr(clicked);

  clicked := clicked + 1;
end;


For Office 2016 with multiple workbooks it updates the button associated with the workbooks that is open. Example: book one the button caption is Clicked 1 while book two the button caption is clicked 2. There is two buttons begin updated.

For Office 2010 with multiple workbooks it updates the one button as it is shared among workbooks.
Example: book one the button caption is Clicked 1 and book two the button caption is clicked 2. There is only one button being updated.
Posted 12 Apr, 2017 11:30:31 Top
Andrei Smolin


Add-in Express team


Posts: 18823
 Joined: 2006-05-11
Hello Chris,

For Add-in Express command bar components to work in Excel 2007-2016, you need to set TadxCommandBar.UseForRibbon=true. Still, you should use Add-in Express Ribbon components to provide the user with the now familiar Ribbon UI. To update command bar controls, you use the WindowActivate event. To update Ribbon components, you use the OnPropertyChanging event; find more details in section Updating Ribbon Controls at Run-Time, see the PDF file in the folder {Add-in Express}\Docs on your development PC.


Andrei Smolin
Add-in Express Team Leader
Posted 13 Apr, 2017 05:44:08 Top
Chris Bishop




Posts: 13
 Joined: 2016-01-25
Hi Andrei,

We plan on moving to the Ribbon soon. We just upgraded from an Add-in-Express 2009 version to your 2017 version of Add-in-Express. Meaning we are looking to update things.

Thanks for the UseForRibbon explanation. I didn't clue in to that.

And I was able to modify my code to do a FindControl when looking for the menu item and that worked.
Posted 13 Apr, 2017 08:53:18 Top

Posts 1 - 10 of 11
First | Prev. | 1 2 | Next | Last