Excel stealing keyboard focus from VCL Form (in AddIn)

Question

I have an Excel AddIn written in Delphi that has a VCL form with a TMemo on it. When I try to enter text into the Memo the input goes to Excel instead.

When I start the form modal (ShowModal), all works fine but obviously it's not possible to work with the main excel window and the addin's window concurrently.

The issue seems to be the exact similar to this question: Modeless form cannot receive keyboard input in Excel Add-in developed by Delphi

This answer suggests to handle WM_PARENTNOTIFY so I tried the following:

TMyForm = class(TForm)
...
 procedure OnParentNotify(var Msg: TMessage); message WM_PARENTNOTIFY;

And in that procedure tried things like SetFocus, WinApi.Windows.SetFocus(self.Handle), SetForeGroundWindows, SetActiveWindow but that doesn't appear to work.

Other suggestions I've read is to run the UI in a different thread (which is of course not possible with VCL) and to install a keyboard hook with SetWindowsHookEx. Obviously that will give us keypress events but not sure what to do with those.

I am not using 3rd party tooling such as Add-In Express but just implementing IDTExtensibility2.

EDIT: more research suggests that Office uses an interface called IMsoComponent and and IMsoComponentManager as a way of tracking the active component in the application. Visual Studio uses these as IOleComponent and IOleComponentManager.

This link and this one suggest to register a new empty IOleComponent/IMsoComponent.

EDIT: MCVE can be fetched here, it's the smallest possible Excel AddIn code that will launch a VCL Form with a TEdit on it. The edit looses keyboard focus as soon as a worksheet is active.

Answer 1

I finally found the solution to this after I decided to have another look at this...

Seems I was on the right track about needing IMsoComponentManager and IMsoComponent.

So first we need to retrieve the ComponentManager:

function GetMsoComponentManager(out ComponentManager: IMsoComponentManager): HRESULT;
var
  MessageFilter: IMessageFilter;
  ServiceProvider: IServiceProvider;
begin
  MessageFilter := nil;
  // Get the previous message filter by temporarily registering a new NULL message filter.
  Result := CoRegisterMessageFilter(nil, MessageFilter);
  if Succeeded(Result) then
  begin
    CoRegisterMessageFilter(MessageFilter, nil);
    if (MessageFilter <> nil) then
    begin
      try
        ServiceProvider := MessageFilter as IServiceProvider;
        Result := ServiceProvider.QueryService(IID_IMsoComponentManager,
          SID_SMsoComponentManager, ComponentManager);

        if Assigned(ComponentManager) then
        begin
        end;

      except
        on E: Exception do
        begin
          Result := E_POINTER;
        end;
      end;
    end;
  end;
end;

Then we need to register a dummy component using msocrfPreTranslateAll (or msocrfPreTranslateKey)

procedure TVCLForm.RegisterComponent;
var
  RegInfo: MSOCRINFO;
  //MsoComponentManager: IMsoComponentManager;
  hr: HRESULT;
  bRes: Boolean;
begin
  if FComponentId = 0 then
  begin
    FDummyMsoComponent := TDummyMsoComponent.Create;
    ZeroMemory(@RegInfo, SizeOf(RegInfo));
    RegInfo.cbSize := SizeOf(RegInfo);
    RegInfo.grfcrf := msocrfPreTranslateAll or msocrfNeedIdleTime;
    RegInfo.grfcadvf := DWORD(msocadvfModal);

    bRes := ComponentManager.FRegisterComponent(FDummyMsoComponent, RegInfo,
      FComponentId);
    Memo1.Lines.Add(Format('FMsoComponentManager.FRegisterComponent: %s (Component ID: %d)', [BoolToStr(bRes, True), FComponentId]));
  end
  else begin
    Memo1.Lines.Add(Format('Component with ID %d was already registered', [FComponentId]));
  end;

  if FComponentId > 0 then
  begin
    bRes := ComponentManager.FOnComponentActivate(FComponentId);
    Memo1.Lines.Add(Format('FMsoComponentManager.FOnComponentActivate: %s (Component ID: %d)', [BoolToStr(bRes, True), FComponentId]));
  end;

end;

Now in the Dummy Component implementation class we must handle FPreTranslateMessage:

function TDummyMsoComponent.FPreTranslateMessage(MSG: pMsg): BOOL;
var
  hWndRoot: THandle;
begin
  // this is the magic required to make sure non office owned windows (forms)
  // receive Window messages. If we return True they will not, however if we
  // return False, they will -> so we check if the message was meant for the
  // window owner
  hWndRoot := GetAncestor(MSG^.hwnd, GA_ROOT);
  Result := (hWndRoot <> 0) and (IsDialogMessage(hWndRoot, MSG^));
end;

Finally a good place to to (un)register the Dummy component is when receiving WM_ACTIVATE. For example:

procedure TVCLForm.OnActivate(var Msg: TMessage);
var
  bRes: Boolean;
begin
  case Msg.WParam of
    WA_ACTIVE:
    begin
      Memo1.Lines.Add('WA_ACTIVE');
      RegisterComponent;
    end;
    WA_CLICKACTIVE:
    begin
      Memo1.Lines.Add('WA_CLICKACTIVE');
      RegisterComponent;
    end;
    WA_INACTIVE:
    begin
      Memo1.Lines.Add('WA_INACTIVE');
      UnRegisterComponent;
    end
  else
    Memo1.Lines.Add('OTHER/UNKNOWN');
  end;

end;

This all seems to work well and does not require intercepting WM_SETCURSOR or WM_IME_SETCONTEXT nor does it need subclassing of the Excel Window.

Once cleaned up will probably write a blog and place all the complete code on Github.

Answer 2

I was having the same kind of problem. I am also implementing IDTExtensibility2 but as I am doing it on C++ I already managed to run the UI on a different thread. But anyway I was not fully happy with this solution. I would still have this problem if I wanted to use a VBA Userform as a TaskPane Window. I did try but as (I guess, didn´t check) the VBA userform will run on the native Excel Thread, just calling it on a different thread (to use as a TaskPane window) just marshalled it, didn´t mean that it was created on a different thread, so as I did try, there was this kind of problem.

I too did read and try to to handle WM_PARENTNOTIFY messages with SetFocus.. on my window but didn´t work.

This both interfaces IOleComponent and IOleComponentManager were new to me. Didn´t find the header files, but could write and implement from the descriptions at the link you shared.

How it worked for me was to register my IOleComponent implementation on every WM_SETCURSOR e WM_IME_SETCONTEXT at my Form Window. (I am not sure if this is exactly the best messages, but did work for me) and Revoke the component on every click back at EXCEL7 window.

The MSOCRINFO options I used to register was msocrfPreTranslateKey and msocadvfModal.

Hope that with this answer I will not receive tons of criticism. I know that it is a very specific issue, the question was with a -1 status when I read it, but was exactly what I needed to finish with this point. So I am just trying to be honest and share back something.