You are here:

Excel/Code required to bring Pop-Up message to the front of any application

Advertisement


Question
QUESTION: Hello Tom,

Dont have any knowledge about Window API, but, after googling around I came across below code -

------------------

Dim AlarmMessage As String
Private Declare Function MessageBox _
       Lib "User32" Alias "MessageBoxA" _
         (ByVal hWnd As Long, _
         ByVal lpText As String, _
         ByVal lpCaption As String, _
         ByVal wType As Long) _
       As Long

Sub AlarmGoneOff()
   Beep
   MessageBox &H0, "This is a native Message Box", "My Box", vbSystemModal
End Sub

------------------

I incorporated above code to my existing code which looks as below -

------------------

Dim AlarmMessage As String
Private Declare Function MessageBox _
       Lib "User32" Alias "MessageBoxA" _
         (ByVal hWnd As Long, _
         ByVal lpText As String, _
         ByVal lpCaption As String, _
         ByVal wType As Long) _
       As Long

Sub AlarmGoneOff()
   Beep
   MessageBox &H0, "This is a native Message Box", "My Box", vbSystemModal
End Sub

Sub AlarmSet()
   alarmDelayTime = Application.InputBox(Prompt:="How many minutes?", Type:=1, Default:=15, Title:="Set your alarm")
   AlarmMessage = Application.InputBox(Prompt:="Type the reminder message?", Type:=2, Default:="Reminder", Title:="Reminder Message")
   Application.OnTime Now + TimeSerial(0, alarmDelayTime, 0), "AlarmGoneOff"
End Sub

------------------

I'm able to view the pop-up box upfront even if multiple applications are running.

The problem is with regards to reminder message.

I think below code needs to be changed to reflect the reminder message, but don't know how to do it (I could be wrong also)

------------------

Sub AlarmGoneOff()
   Beep
   MessageBox &H0, "This is a native Message Box", "My Box", vbSystemModal
End Sub

------------------

ANSWER: Bimmy,

Look at your declaration

Private Declare Function MessageBox _
      Lib "User32" Alias "MessageBoxA" _
        (ByVal hWnd As Long, _
        ByVal lpText As String, _
        ByVal lpCaption As String, _
        ByVal wType As Long) _
      As Long


so in your messagebox command you show
MessageBox &H0, "This is a native Message Box", "My Box", vbSystemModal

that means the &H0 is the handle to the parent window (hwnd)
"This is a native Message Box"  is the lpText so that is what will appear in the messagebox as your message. Make that string your message

"My Box"   is going to be in the caption of the message box   lpCaption  so make it whatever you want the caption to be.

the vbSystemModal should stay the same assuming this is working as you say.  

Windows API is not what I do - I only support internal Excel as I state in my profile.  Anything I have just said above is my best guess.

All that said, I thought you were prompting for entries from the user.  A message box usually just broadcasts an alert - it doesn't gather input -- but you know what you are doing better than I.  

--
Regards,
Tom Ogilvy




---------- FOLLOW-UP ----------

QUESTION: Hello Tom,

Thanks for responding.

Is it possible to bring a specific workbook to the front of any application after the user inserts specific time.

Let me explain.

Code will be stored in workbook by the name Reminder. Macro should prompt the user to enter time in minutes. After the specified time, code should bring the Reminder workbook to the front of any application.

Can this be achieved or something similar.

I want the user to be updated with reminder messages as per the time they mention. The user should be able to view the reminder message if they are working on multiple applications.

Answer
Bimmy,

You are running the code from excel.  So in response to your first question posted today I provided a URL that showed how to use the appactivate functionality to make excel the active application.  then you can have your code make the workbook and worksheet active.  

In case you missed the response, here is the URL again:

http://www.mrexcel.com/forum/excel-questions/66245-ensure-msgbox-pops-up-top.htm

dk says:
One way might be to put this line just before the msgbox code:-

AppActivate Application.Caption


from help on AppActivate:

The AppActivate statement changes the focus to the named application or window but does not affect whether it is maximized or minimized. Focus moves from the activated application window when the user takes some action to change the focus or close the window.

In determining which application to activate, title is compared to the title string of each running application. If there is no exact match, any application whose title string begins with title is activated. If there is more than one instance of the application named by title, one instance is arbitrarily activated.

------------
Now once you have excel in the foreground using the above command, you can make whatever internal workbook and worksheet active that you want.

whether that will work for you or not, I can't say.   This command has been around a long time, so I don't know how it will work with excel 2013 if you have multiple excel windows open.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.