Solved

Office UserForm always on top

Posted on 2013-06-03
6
650 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

744 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

12 Experts available now in Live!

Get 1:1 Help Now