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

glfiedler

New Member
Joined
Apr 1, 2019
Messages
36
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,427
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

New Member
Joined
Apr 1, 2019
Messages
36
Thanks for trying but that did not work. I still have to click the title bar of the userform to "activate" it.
 

Forum statistics

Threads
1,078,440
Messages
5,340,293
Members
399,364
Latest member
farhan11941234

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top