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

glfiedler

Board Regular
Joined
Apr 1, 2019
Messages
65
Office Version
365
Platform
Windows
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?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,788
Office Version
2016
Platform
Windows
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 :
Code:
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

Board Regular
Joined
Apr 1, 2019
Messages
65
Office Version
365
Platform
Windows
Thanks for trying but that did not work. I still have to click the title bar of the userform to "activate" it.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,426
Messages
5,450,347
Members
405,606
Latest member
greatness1987

This Week's Hot Topics

Top