VBA Form Is (automatically) Tied/Bound To ActiveWorksheet When calling Form.Show vbmodeless

Question

It's been difficult to describe to google, but the scenario is as follows..

I have a button on the ribbon which is linked to a sub which will launch my form:

Sub Start()
    MyForm.Init
    MyForm.Show vbModeless
End Sub

The code called by my form does some things and create a new workbook. When the new workbook is created the form disappears. When bring the form back, it pulls the workbook that was active when the form was shown. I want to have the new workbook active and the form active in order to perform more actions from the form with the new worksheet.

Is there another way to call/load the form in order to have it not "linked" to a workbook?

Answer 1

Yes it can be achieved but with using APIs SetWindowPos and FindWindowA. You can read about these APIs Here

Is this what you are trying?

Code:

Option Explicit

Private Const SWP_NOMOVE = 2
Private Const SWP_NOSIZE = 1    
Private Const FLAGS = SWP_NOMOVE Or SWP_NOSIZE
Private Const HWND_TOPMOST = -1
Private Const HWND_NOTOPMOST = -2

Private Declare Function SetWindowPos Lib "user32" _
(ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _
ByVal x As Long, ByVal y As Long, _
ByVal cx As Long, ByVal cy As Long, _
ByVal wFlags As Long) As Long

Private Declare Function FindWindowA _
Lib "user32" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Sub Sample()
    Dim hwnd As Long

    UserForm1.Show vbModeless

    '~~> Find the handle of the userform
    hwnd = FindWindowA("ThunderDFrame", UserForm1.Caption)

    '~~> Set the form as the top most window
    SetTopMostWindow hwnd, True
End Sub

Private Function SetTopMostWindow(hwnd As Long, Topmost As Boolean) As Long
    If Topmost = True Then
        SetTopMostWindow = SetWindowPos(hwnd, HWND_TOPMOST, 0, 0, 0, 0, FLAGS)
    Else
        SetTopMostWindow = SetWindowPos(hwnd, HWND_NOTOPMOST, 0, 0, 0, 0, FLAGS)
        SetTopMostWindow = False
    End If
End Function

Testing

1. Paste the code in the module of the workbook which has the userform.
2. Open two workbooks.
3. Replace Userform1 with your userform name.
4. Run the proc Sample.