Office UserForm always on top

Excel and Visio windows are open, and transfer data etc between each other in early binding.
A UserForm is shown, from a macro in one or other application.

QUESTION:
I want the UserForm to be ON TOP of BOTH applications.
Is this possible? I am a VBA user (so cannot explore Task Panes in some alternative approach)

I would intend to add minimise / maximise buttons to the UserForm (http://www.cpearson.com/excel/formcontrol.aspx)
Kelvin4Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Visio_GuyConnect With a Mentor Commented:
We used to do this using Windows API calls. I don't know if you can get it to work with VBA, since forms belong to the application. I don't know if their scope can somehow be 'bumped up' to escape the owning application.

I didn't get it to work in VBA, but you might be able to keep tweaking it to get it to work. At any rate, this might point you down a path worth following.

Here's some stuff I cobbled together from several very Googlable sources. Put the code below in a Module, then call SetTopMostWindow from a form, or from code that launches a form.

Option Explicit

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

'// Remove 'PtrSafe' if you using 32-bit Office (I think...)
Declare PtrSafe 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

Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
        (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
        
Public Function SetTopMostWindow(ByRef frm As UserForm, bTopmost As Boolean) As Long

  Dim hWnd As Long
  '// Check if we are using XL97 or not
  If Val(Application.Version) < 9 Then
      hWnd = FindWindow("ThunderXFrame", frm.Caption)
  Else
      hWnd = FindWindow("ThunderDFrame", frm.Caption)
  End If
  
  If (bTopmost = True) Then '//...make the window topmost
     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

Open in new window

0
 
Kelvin4Author Commented:
VisioGuy:
Your code does exactly what I asked.
Breath-taking!

I attach a pair of files: Visio and Excel as evidence-

I suggest you open the excel file first, and follow the notes on the sheet: "visioEvents"

The ActiveX buttons on Visio page "CallUserForm1" and Excel Sheet "VisioEvents"
call user forms that remain ON TOP if and when bTopmost = true.

(When you open the visio file, you will notice some code of yours in ThisDocument (if you change the active visio page, Excel cell A2 reports the change.)

Delighted, thankyou!
Kelvin
TestGetxlObjectv3.vsd
VisAuthDev-Current.xlsm
0
 
Kelvin4Author Commented:
Delighted!
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Visio_GuyCommented:
Nice. I got it to work on my machine, I'll have to have a closer look one of these days.
0
 
Visio_GuyCommented:
Actually, the Visio file posted doesn't have a page called "CallUserForm1"...
0
 
Kelvin4Author Commented:
Apologies for wrong visio file. For the record I attach TestGetxlObjectv4.
TestGetxlObjectv4.vsd
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.