Solved

Office UserForm always on top

Posted on 2013-06-03
6
686 Views
Last Modified: 2013-06-03
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)
0
Comment
Question by:Kelvin4
  • 3
  • 3
6 Comments
 
LVL 11

Accepted Solution

by:
Visio_Guy earned 500 total points
ID: 39216182
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
 

Author Comment

by:Kelvin4
ID: 39216847
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
 

Author Closing Comment

by:Kelvin4
ID: 39216850
Delighted!
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 11

Expert Comment

by:Visio_Guy
ID: 39217268
Nice. I got it to work on my machine, I'll have to have a closer look one of these days.
0
 
LVL 11

Expert Comment

by:Visio_Guy
ID: 39217426
Actually, the Visio file posted doesn't have a page called "CallUserForm1"...
0
 

Author Comment

by:Kelvin4
ID: 39217972
Apologies for wrong visio file. For the record I attach TestGetxlObjectv4.
TestGetxlObjectv4.vsd
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delete all empty columns using VBA 7 42
And OR formula 5 23
Excel VBA 4 27
Excel, covert column titles to row titles and back 2 13
This collection of functions covers all the normal rounding methods of just about any numeric value.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question