Announcement

Collapse
No announcement yet.

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

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.
    Defeat Easy, Win Hard

  • #2
    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

    Comment


    • #3
      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.
      Defeat Easy, Win Hard

      Comment


      • #4
        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?
        Defeat Easy, Win Hard

        Comment


        • #5
          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:

          Code:
          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

          Comment


          • #6
            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
            Defeat Easy, Win Hard

            Comment


            • #7
              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:

              Code:
                  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, 00:45.

              Comment


              • #8
                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
                Defeat Easy, Win Hard

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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 Files
                    Defeat Easy, Win Hard

                    Comment


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

                      Give my sample file a try, it will show you how to use the alert.

                      Comment


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

                        Oh Great LeastAction.

                        It is worked and will be useful for me. I have slightly changed the code for using my userforms.

                        Thank you...................

                        ............. will not be enough.

                        Great Work.
                        Defeat Easy, Win Hard

                        Comment


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

                          I, too, am very impressed by this widget and appreciate the time and effort that went into coding it. I find the code dense with functionality but not just lines and lines of stuff. Thanks for keeping it small and easy to manage. I ran your "Alert.xls", but how do I add the "V2" .bas file into my own workbook? I'm just that new to all this.

                          Comment


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

                            Hi LeastAction, MJ,
                            I am a novice to VB, however good at excel, while searching for the same need to have pop-up when one cell value changes or reaches some threshold.
                            Downloaded your script, etc. however i dont know how to use the same.
                            Can somebody provide step by step instructions on using this when cell value changes from 10 to 11, a pop alert should appear in desktop.

                            Comment


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

                              Welcome to the Forum

                              Please start your own post. Posting in another member's Thread is known as hijacking and is not allowed here. By all means add a link to a Thread that may be related to your question.
                              Hope that Helps

                              Roy

                              New users should read the Forum Rules before posting

                              For free Excel tools & articles visit my web site

                              If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                              RoyUK's Web Site

                              royUK's Database Form

                              Where to paste code from the Forum

                              About me.

                              Comment

                              Working...
                              X