Solved

Office UserForm always on top

Posted on 2013-06-03
6
667 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now