Changing the parent workbook window of an add-in's userform

Question

I have been struggling with this question for quite some time now.

I have a userform that is called from an Excel add-in I've built which then tests whether the current active workbook contains a specific structure. If not, it creates a new workbook with that exact structure. I set this workbook as well as another workbook that I open as wb1 and wb2.

The issue is that the userform is initially called from the activeworkbook (could be any workbook) at the time of clicking the ribbon button and basically latches onto THAT workbook alone.

Is there any way I could detach the userform from that workbook and change it to show my wb2 in the background of my userform?

I have tried the following code, but it just closes my userform and doesn't work.

 Option Explicit
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal clsName As String, ByVal wndName As String) As Long
Private Declare PtrSafe Function SetParent Lib "user32" (ByVal hChild As Long, ByVal hParent As Long) As Long

Private Sub CommandButton1_Click()
  Static h As Long
  If h <= 0 Then h = FindWindow("ThunderDFrame", Me.Caption)
  If h <= 0 Then Exit Sub
  Dim wb As Workbook: Set wb = Workbooks.Add
  SetParent h, Application.Windows(wb.Name).Hwnd
  wb.Activate
End Sub

Any ideas?

score 1 · Accepted Answer · answered Nov 01 '19 at 13:32

1

Here is my code from my Addin to show all sheets in all opened workbooks in one modeless userform and activating (by dblcick) choosen one. Calling: Call ShowModeless

Class WinActivate in Addin file

' Class WinActivate
Public WithEvents AppEvents As Application

Private Declare PtrSafe Function SetParent Lib "user32" _
   (ByVal hWndChild As LongPtr, ByVal hWndNewParent As LongPtr) As LongPtr

Private Sub AppEvents_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
    'Change precedent object of UserForm for new created windows
    If Val(Application.Version) >= 15 Then SetParent UserFormHandle, Wn.hWnd
End Sub

Module1 in Addin file

'Module1 code
Option Explicit

Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
  (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr

Dim WA                  As New WinActivate

Public UserFormHandle   As Long

' DajArkusze is my Userform in Addin file
Sub ShowModeless()
    Set WA.AppEvents = Application
    DajArkusze.Show 0
    UserFormHandle = FindWindow("ThunderDFrame", DajArkusze.Caption)
End Sub

answered Nov 01 '19 at 13:32

barneyos

586
2
5
7

Thank you for your reply. I've tried to implement your code, but as soon as I open another workbook from my code, it closes my Userform. Am I missing anything? – Renier Wessels Nov 01 '19 at 15:17
Hmm, weird, I run an userform and when I change the active workbook or add a new one, run new Excel instance, always my userform is on top, that means, it behaves properly. I have Ex 2016 Prof. My macro file is placed in C:\Users\Bartek\AppData\Roaming\Microsoft\AddIns\Daj_Akrusze_Userform.xlam, as every Addin should be in. – barneyos Nov 01 '19 at 16:03
Have You been calling userform modelles with .Show 0 (or .Show vbModeless) ? – barneyos Nov 01 '19 at 16:10
I have managed to get it working by creating my new workbook first and then opening the workbook. So the answer was in the order of workings. Thank you for your help. It really is appreciated! – Renier Wessels Nov 01 '19 at 17:08

score 0 · Answer 2 · answered Nov 25 '21 at 21:16

I had to solve a similar problem with Forms opening on the wrong workbook when multiple screens are involved.

It turns out you can change which workbook "owns" a form by unloading the form then reloading it.

Change:

MyWorkbook.Activate
Load MyForm
MyForm.Show

To:

MyWorkbook.Activate
Unload MyForm
Load MyForm
MyForm.Show

Changing the parent workbook window of an add-in's userform

2 Answers2

Linked