Userform in add-in must be clicked before it is "active"

[glfiedler]

I have created an add-in that functions perfectly except for one annoying thing.

The add-in creates a new workbook by copying the add-in's first sheet. This new workbook is then populated with data from an archived workbook that the add-in has opened. This works well and I can watch the data being transferred since the new workbook sheet is displayed. After the add-in has transferred the data the add-in shows a Userform requesting additional data that is not contained in the archived workbook.

Now the quirk! This Userform is not "active", for lack of a better word. In order to type data into the textbox I must first click the Userform. Then all is well and later the add-in shows another Userform but it is alive and well without clicking.

It appears the application is "focused" on the newly opened WB where the data has just been transferred and I must click the Userform to get the app to focus back to the add-in. I have not been able to find anything that addresses the "focus" of workbooks.

I suspect there is one or two lines of code to fix this very minor problem. Any ideas?

 

[Jaafar Tribak]

Is the userform modal or modeless ?

 

[glfiedler]

Currently it is Modal.

 

[Jaafar Tribak]

I remember experiencing a similar problem in the past but I can't recall what was the cause or how I addressed the issue.

Anyway, try the following code in the UserForm Module and see what happens :

Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function WindowFromAccessibleObject Lib "oleacc" (ByVal pacc As IAccessible, phwnd As LongPtr) As Long
    Private Declare PtrSafe Function SetFocus Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function WindowFromAccessibleObject Lib "oleacc" (ByVal pacc As IAccessible, phwnd As Long) As Long
    Private Declare Function SetFocus Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


Private Sub UserForm_Layout()

    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
        Dim hwnd As LongPtr
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Dim hwnd As Long
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

    WindowFromAccessibleObject Me, hwnd
    SetForegroundWindow hwnd
    SetFocus hwnd

End Sub

 

[glfiedler]

Thanks for trying but that did not work. I still have to click the title bar of the userform to "activate" it.