Solved

Office UserForm always on top

Posted on 2013-06-03
6
730 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

MongoDB Through a MySQL Lens

This article looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
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…

623 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