Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Create Popup alert Form without buttons (like outlook desktop alert)

  1. #1
    Join Date
    14th April 2011
    Posts
    45

    Create Popup alert Form without buttons (like outlook desktop alert)

    Hi Guys,

    I want to create a Pop-up without buttons in my macro (like outlook desktop alert).
    it will show after a process completed when i am working on some other applications like browser, or MS Access.
    When I surf for this stuff all forums and websites I've browsed are giving idea only with message box. But I want to create a Pop-up like outlook desktop alert. Can I have a solution for this?

    I hope I will have solution.

    Thanks.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    10th August 2011
    Location
    Canada
    Posts
    22

    Re: Create Popup alert Form without buttons (like outlook desktop alert)

    Hi udhaya_k,

    So if I understand correctly you will be running a macro in excel and while it is running you're going to working in some other application. What you want to happen is at certain points in your macro code you want an alert to pop-up somewhere on the screen that does not require feedback from the user (ie will not stop the code from continuing). If possible, would you like it to disappear after some time or not?

    Is this correct?

    Cheers,
    MJ

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    14th April 2011
    Posts
    45

    Re: Create Popup alert Form without buttons (like outlook desktop alert)

    Hi LeastAction,

    Yes. Your understanding is very correct. And also as you said, I would like it to disappear after 5 or 6 seconds.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th April 2011
    Posts
    45

    Re: Create Popup alert Form without buttons (like outlook desktop alert)

    Also is it possible to show this popup while working within excel, without disturbing my job, and disappear after few seconds?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    10th August 2011
    Location
    Canada
    Posts
    22

    Re: Create Popup alert Form without buttons (like outlook desktop alert)

    Hi udhaya_k,

    Well, I've been working on this for a bit now (it's actually quite interesting). I have managed two methods: the first isn't exactly what you want and the second one I am in the process of coding (deceivingly difficult). The first method is this:

    VB:
    CreateObject("Wscript.Shell").popup "Hello World!", 3, "Title", vbSystemModal + 64 
    
    
    The '3' represents how many seconds you want it stay before disappearing. The problem with this is that your code will pause until the popup disappears.

    For the second method I have been attempting to create my own alert with the functionality you described. Essentially what I did was create a modeless userform consisting of just a Label control, in a subroutine I Load the form, pass it variables (such as text, etc...) and then show it. At this point the activation of the form calls a few Windows API that find its window handle and slowly increment its transparency to create the fade in effect. Up to this point everything is golden, now comes the trouble. To create the disappear after N seconds functionality I thought of adding an Application.OnTime event inside the subroutine to Unload the form (which fades it out), this is fine except for this one point:

    Excel is a single threaded application when performing macros or user actions, if a macro is running the user is blocked out and vice versa. So, even though the Application.OnTime is created in a separate thread it's attempting to run the Unload routine in the macro thread and thus has to wait until the initial code has fully completed before unloading the form. I can't seem to find a way to make the OnTime method pause the initial code and take precedence (nor do I think it's possible). The only way I can think of that might work (and I stress might) is to make a COM Interface that when connected to can create the popup on its own thread server side (which can still be local keep in mind).

    I'll give it a try at some point, I can't say how long it will take as I'd have to look at it a bit more first. Hopefully the first method is good enough. If someone is interested in my half finished Userform popup I can post it. Otherwise if you have any question, feel free to ask.

    Cheers,
    MJ

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th April 2011
    Posts
    45

    Re: Create Popup alert Form without buttons (like outlook desktop alert)

    LeastAction,

    You have opened a new way. Thanks a lot for it. And I really appreciate that you are trying different methods.

    The CreateObject Vb code is good. But it is not disappearing. Should I do in someother way like create a .vbs file?

    I understand the reason you are giving that Excel is single threaded.

    I think the modeless userform will be a good idea.

    Please post the half finished userform popup. Let it be a try.

    Thanks.

    udhaya_k

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    10th August 2011
    Location
    Canada
    Posts
    22

    Re: Create Popup alert Form without buttons (like outlook desktop alert)

    Hi udhaya_k,

    I figured it out! Import the following module into your VBE and it will expose a subroutine called Alert (or modAlert.Alert) which has as arguments: (Message,Title,Number of seconds before it disappears).

    If the number of seconds is 0 or missing it will stay on screen until you click OK. This does exactly as you wish, it will pop up on top of any open application and will run in a separate thread than your macro so it will not halt your code at all (except for the slight time it takes to run its code of course).

    modAlert.zip

    IMPORTANT: In Excel 2002 and newer you will have to explicitly trust access to your VBA Projects. Go to Tools, Macro, Security. Click on the 2nd tab: Trusted Publishers. In the lower left, choose Trust Access to Visual Basic Project. In Excel 2007, Go to Office Icon, Excel Options, Trust Center, Trust Center Settings, Macro Settings, and choose Trust access to the VBA project object model.

    If you have any questions, feel free to ask.modAlert_V.2.zip

    Cheers,
    MJ

    EDIT: Changed subroutine in modAlert as I forgot to do some cleaning up after the code was run, this should fix that. Replace subroutine CreateAlertObject with this:

    VB:
    Public Sub CreateAlertObject(sText As String, sTitle As String, lNumSeconds As Long) 
        CreateObject("Wscript.Shell").popup sText, lNumSeconds, sTitle, vbSystemModal + 64 
        ThisWorkbook.Close False 
        Application.Quit 
    End Sub 
    
    
    or use this updated .bas file:

    modAlert_V.2.zip
    Last edited by LeastAction; August 13th, 2011 at 00:45.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    14th April 2011
    Posts
    45

    Re: Create Popup alert Form without buttons (like outlook desktop alert)

    LeastAction,

    Thanks for update the modAlert_v.2.zip. the file modAlert.zip does not work out for me. That is why I hesitate to ask.

    But V.2 also does not work for me. I have attached my excel with v.2

    Can you check that If I am wrong?

    Thanks.

    Udhaya_K
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    10th August 2011
    Location
    Canada
    Posts
    22

    Re: Create Popup alert Form without buttons (like outlook desktop alert)

    I noticed you copied the code in two separate places. The code should just be located in modAlert not in Module1. To use the notification in Module1 you would type modAlert.Alert(text,title,seconds).

    What do you mean by not work? Is it erroring?

    Cheers,
    MJ

    EDIT: Try my sample file

    Alert.xls

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    14th April 2011
    Posts
    45

    Re: Create Popup alert Form without buttons (like outlook desktop alert)

    LeastAction,

    I meant not the Error. I gave 3 as seconds. But after 3 seconds the popup does not disappear.

    Now I discovered that my code should be in ModAlert.

    But it shows error. How to code it?. I am glad that you are taking it as an experience instead feel annoying.
    I have attached the screen shot.
    Attached Images

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Create User Form Shorcut on Desktop
    By harid in forum EXCEL HELP
    Replies: 6
    Last Post: September 18th, 2010, 15:19
  2. Alert pop up box
    By richywilson in forum EXCEL HELP
    Replies: 2
    Last Post: October 26th, 2005, 19:43
  3. Open an Alert on a certain Day
    By nicelad_uk in forum EXCEL HELP
    Replies: 12
    Last Post: September 3rd, 2005, 13:34
  4. Alert if cell has ever been a particular value
    By sj_der in forum EXCEL HELP
    Replies: 5
    Last Post: March 21st, 2005, 21:47
  5. VBA : Keep Alert Box
    By rpaulson in forum EXCEL HELP
    Replies: 2
    Last Post: April 30th, 2004, 22:46

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno