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.