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

Question

I created a userform in an *.xlam add-in and created a new commandbar and button in the IDE, but when I click the button, the user form is opened in Excel, and focus is forced away from the IDE. Is there a way to open the user form in the IDE instead of the host application without resorting to a .Net COM Add-in?

Here is the code that creates the commandbar 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

P.S. You may need this line of code to remove the commandbar...

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

Pita

Yes: make it a VBE add-in instead of an Excel add-in! ;) – Inadequate

Do you mean a floating userform which doesn't steal the focus? – Barchan

Well, I would like the userform to get the focus, without Excel stealing the focus from the IDE. I have no idea if it's even possible @SiddharthRout. – Pita

Ah I understand now what you are trying. – Barchan

Do you need Excel Instance Visible at the same time? Can it be hidden while the user is working in VBE and then show it later? I have an idea – Barchan

Not necessarily.... I'm happy to hear any ideas @SiddharthRout. – Pita

+ 1 For a nice question. I should have been in bed (It's 3:27 AM) but this kept me awake. The challenge of not using .Net COM Add-in was just too much :D – Barchan

I have added an edit. You may want to refresh the page. I am off to bed. Will look at in the morning if you have any questions :) – Barchan

Answer 1

Here is an alternative.

Put a button on your user form. For demonstration purpose, I am using this

Next put this code in the userform

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

Next 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 this

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 userforms when the user clicks on smiley, change the Sub ShowForm() to the below. (Alternative would be to disable the smiley and re enable it when the form unload?)

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

Barchan

@RubberDuck: kool :) Gnite! – Barchan

+1 for the effort! ...looks almost easier to go with a COM add-in though ;) – Inadequate

@retailcoder: Easier than Com Add-In? Nope. Definitely not. This is way too easy :). Having said that would I prefer a COM Add-In or this? Ummm, it will ultimately depend on what I am trying to do. If it is just a small functionality then I will stay with VBA. I only use Com-Addins when I want to "extend" the functionality of Office applications for custom task. Specially things which I can't handle from VBA. – Barchan

Beautiful hack. Thank you! Ultimately I do think the com add-in will be the way to go though. There's a point where we're really just pushing vba past what we should. – Pita