There are at least two ways to show a userform from within the VBE. The easy method is to hide the main Excel application. The userform will still be a child of the main Excel window, but nobody will be the wiser. That is, except when their Excel and VBE windows have switched positions on the taskbar.
|
1 2 3 |
Application.Visible = False UserForm1.Show Application.Visible = True |
The more complicated method uses the FindWindow and SetParent APIs. It’s only real benefit is that it doesn’t reorder the windows in the taskbar, but it’s not really that much more complicated, so I prefer it. It sets the userform’s window as a child of the VBE window instead of as a child of Excel’s main window.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub ShowUFFromVBE() Dim lHwForm As Long Dim lHwVbe As Long lHwVbe = FindWindow("wndClass_desked_gsk", vbNullString) lHwForm = FindWindow("ThunderDFrame", vbNullString) SetParent lHwForm, lHwVbe UserForm1.Show End Sub |
The hwnd of the VBE Window can also be got from Application.VBE.MainWindow.hWnd, which is safer if you have more than one VBE open at the same time (e.g. Excel and Word, or two Excels)!
Regards
Stephen Bullen
D’Oh. Of course, you already knew that!
Sorry
Stephen Bullen
Showing a UserForm from the VBE
Can I have a excel workbook example? I am have problems getting the functions to work.
http://www.dicks-blog.com/archives/2004/10/26/showing-a-userform-from-the-vbe