Is there a way to display a user form in an IDE instead of a host application?

Question

I created a user form in the * .xlam add-in and created a new command line and button in the IDE, but when I click the button, the user form opens in Excel and the focus is squeezed out of the IDE. Is there a way to open a user form in the IDE instead of the host application without resorting to the .Net COM add-in?

Here is the code that creates the command and button and handles the button click event.

Option Explicit Public WithEvents cmdBarEvents As VBIDE.CommandBarEvents Private Sub Class_Initialize() CreateCommandBar End Sub Private Sub Class_Terminate() Application.VBE.CommandBars("VBIDE").Delete End Sub Private Sub CreateCommandBar() Dim bar As CommandBar Set bar = Application.VBE.CommandBars.Add("VBIDE", MsoBarPosition.msoBarFloating, False, True) bar.Visible = True Dim btn As CommandBarButton Set btn = bar.Controls.Add(msoControlButton, , , , True) btn.Caption = "Show Form" btn.OnAction = "ShowForm" btn.FaceId = 59 Set cmdBarEvents = Application.VBE.Events.CommandBarEvents(btn) End Sub Private Sub cmdBarEvents_Click(ByVal CommandBarControl As Object, handled As Boolean, CancelDefault As Boolean) CallByName Me, CommandBarControl.OnAction, VbMethod End Sub Public Sub ShowForm() Dim frm As New UserForm1 frm.Show End Sub 

PS You may need this line of code to remove the command line ...

 Application.VBE.CommandBars("VBIDE").Delete 

Answer 1

Here is an alternative.

Put the button in your custom form. For demo purpose, I use this

Then put this code in a custom form

 Private Sub CommandButton1_Click() Unload Me Application.Visible = True End Sub 

Then paste this on top of your class module

 Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _ (ByVal lpClassName As String, ByVal lpWindowName As String) As Long Dim Ret As Long, ChildRet As Long 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 Const HWND_TOPMOST = -1 Private Const SWP_NOACTIVATE = &H10 Private Const SWP_SHOWWINDOW = &H40 

Finally, change your Sub ShowForm() to

 Public Sub ShowForm() Dim frm As New UserForm1 Dim Ret As Long frm.Show vbModeless Application.Visible = False Ret = FindWindow("ThunderDFrame", frm.Caption) SetWindowPos Ret, HWND_TOPMOST, 100, 100, 250, 200, _ SWP_NOACTIVATE Or SWP_SHOWWINDOW End Sub 

This is what you get

EDIT

More thoughts. To prevent the user from creating more custom forms when the user clicks on the emoticon, change Sub ShowForm() to below. (An alternative would be to disable the emoticon and enable it when unloading the form?)

 Public Sub ShowForm() Dim frm As New UserForm1 Dim Ret As Long Dim formCaption As String '~~> Set Userform Caption formCaption = "Blah Blah" On Error Resume Next Ret = FindWindow("ThunderDFrame", formCaption) On Error GoTo 0 '~~> If already there in an instance then exit sub If Ret <> 0 Then Exit Sub frm.Show vbModeless frm.Caption = formCaption Application.Visible = False Ret = FindWindow("ThunderDFrame", frm.Caption) SetWindowPos Ret, HWND_TOPMOST, 100, 100, 250, 200, _ SWP_NOACTIVATE Or SWP_SHOWWINDOW End Sub