Displaying a userform using Application.onTime
Results 1 to 10 of 10

Thread: Displaying a userform using Application.onTime
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2006
    Posts
    535
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Displaying a userform using Application.onTime

    Is it possible to display a custom userform message at two different times on the
    same day using 1) one userfrom 2) not being available to close the earlier form ?

    The earlier userform time to display blocks the later userform display from displaying.

    What I want is to be able to show BOTH instances on the screen, earlier and later, without having to close out the earlier form displays.

    If this is impossible without using two different userforms please tell me.

    Comment: Application.ontime works, but I have to be present to close each display.

    The procedure just calls itself with changes to the textbox message.

    Thx for anyone's help. Not one response the last time I asked for help on this

    cr
    Last edited by chazrab; Jul 19th, 2019 at 06:10 PM.

  2. #2
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,315
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Displaying a userform using Application.onTime

    It is not clear what you want from your description.

    Is it one userfrom or 2 userforms ?

    If you just want to change the text displayed in the userform textbox after a set time, you could certainly use the OnTime Method .

    Something like this maybe:

    In a standard module
    Code:
    Option Explicit
    
    Private oForm As UserForm1
    
    Sub ShowUserForm()
        Set oForm = New UserForm1
        oForm.TextBox1.Text = "First Message."
        Application.OnTime Now + TimeSerial(0, 0, 2), "OnTimeMacro" 'change text box text after 2 seconds.
        oForm.Show
    End Sub
    
    
    Sub OnTimeMacro()
        oForm.TextBox1.Text = "Second Message."
    End Sub
    Last edited by Jaafar Tribak; Jul 19th, 2019 at 11:26 PM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  3. #3
    Board Regular
    Join Date
    Oct 2006
    Posts
    535
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Displaying a userform using Application.onTime

    Hi Jaafar - thanks for the response...
    changing the text, adding or deleting after a set time is not the issue. I'll try to explain as simply as possible.
    I have a custom userform reminder opened from a button. It is 7/21/2019. The userform is opened.
    The user form has
    Code:
    DTPicker1 = Date for user to enter to display the form = any date can be entered from the drop down calendar, i.e., user enters 07/23/2019
    DTPicker2 = The Time on 07/23/2019 to display the form on the screen, i.e., user enters 10:00 AM
    Textbox1.Value = "Buy apples"
    Userform saves the values and closes the form.  The data is saved on a new row in the DATA sheet.
    The DATA sheet has 3 cols: col A = Date, col B = TIme, col C = Message.  All of these are formatted correctly.
    
    On the same day a little later, 7/21/2019, user opens the form again and enters a NEW MESSAGE TO BE DISPLAYED ON 07/23/2019 AT 2:00 PM. Textbox1.Value for that message = "Buy oranges".   That data is saved on a new row in the DATA worksheet and again the form closes.
    Time passes. The current day is now 07/23/2019.
    [code]
    10 AM arrives. The form set to display at 10 AM pops up as it should with Textbox1 displaying "Buy apples".
    HOWEVER: the user is not present to close this earlier form.
    2:00 PM arrives. The form instance set to display at 2:00 PM with Textbox1 displaying "Buy oranges"
    never displays.

    This will never be satisfactory for the obvious reason of missing important messages that never appear.
    What I want is to develop code that will allow for displaying BOTH MESSAGES.

    In other words, when the
    user returns at 6 PM on 07/23/2019, he/she will see TWO USERFORM INSTANCES(for lack of a better term)
    appear side by side on the screen, NOT ONE BEHIND THE OTHER, one form instance showing
    Code:
    1 userform image on the screen with textbox1 saying "Buy apples"
    1 ujserform image on the screen on the side or below  the first userform saying "Buy oranges"
    This is about as simple as I can make it. I can't see doing this without TWO uuserforms, but if there's a
    way to display two userform images using ONE userform input, that's what I prefer. The word image is the
    best word I can think of to describe what should be happening.

    Thx for all your help. At least, someone has responded in this effort in trying to help me figure this out.
    This is done all the time in apps outisde of Excel - Remind Me, the Outlook calendar reminder, and the best
    one -CUTE REMINDER(cutereminder.com) I could use an external app, but I want to create the exact same thing WITHIN Excel with a userform using Application.onTime.

    Thx for all your help.
    cr
    Last edited by chazrab; Jul 20th, 2019 at 01:48 PM.

  4. #4
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,315
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Displaying a userform using Application.onTime

    What happens if the user is present and closes the first userform or the second one ?

    And will the times (10:00 AM and 02:00 PM) be always the same every day ?

    Regards.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  5. #5
    Board Regular
    Join Date
    Oct 2006
    Posts
    535
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Displaying a userform using Application.onTime

    Quote Originally Posted by Jaafar Tribak View Post
    What happens if the user is present and closes the first userform or the second one ?

    And will the times (10:00 AM and 02:00 PM) be always the same every day ?

    Regards.
    If the user is present to close all appearing forms, they will all appear at the specified times. The earlier form is released.
    Times and dates will vary.
    Having to close displayed userforms in order to display later forms in inefficient. The user is forced to sit
    and wait, then click a close button for later forms to appear.

  6. #6
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,315
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Displaying a userform using Application.onTime

    Hi,

    See if this works for you: The idea is to start the timers upon opening the workbook and stop them upon closing.

    At 10:00 AM UserForm1 should popup with TextBox1 displaying 'Buy apples' and at 02:00 PM, Userform2 pops up with TextBox1 displaying 'Buy oranges'.

    If at 02:00 PM, UserForm1 is still on display, UserForm2 will be located to the side of UserForm1 otherwise, UserForm2 will be shown at the center of the screen as normal.

    If the workbook is left open, the userforms will be displayed again in the same fashion the next day.

    Note: Both UserForms must be displayed Modeless.


    Code in the ThisWorkbook Module
    Code:
    Option Explicit
    
    Private Const RunWhenForm1 As String = "10:00:00"  '<== change this time const to suit
    Private Const RunWhenForm2 As String = "14:00:00"  '<== change this time const to suit
    
    Private cRunWhatForm1 As String
    Private cRunWhatForm2 As String
    
    
    Private Sub Workbook_Open()
        Call StartTimer("UserForm1")
        Call StartTimer("UserForm2")
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Call StopTimer("UserForm1")
        Call StopTimer("UserForm2")
    End Sub
    
    
    Private Sub StartTimer(ByVal FormName As String)
        If FormName = "UserForm1" Then
            cRunWhatForm1 = "'" & Me.CodeName & ".ShowForm """ & FormName & "'"
            Application.OnTime EarliestTime:=RunWhenForm1, Procedure:=cRunWhatForm1, Schedule:=True
        ElseIf FormName = "UserForm2" Then
            cRunWhatForm2 = "'" & Me.CodeName & ".ShowForm """ & FormName & "'"
            Application.OnTime EarliestTime:=RunWhenForm2, Procedure:=cRunWhatForm2, Schedule:=True
        End If
    End Sub
    
    Private Sub StopTimer(ByVal FormName As String)
        On Error Resume Next
        If FormName = "UserForm1" Then
            Application.OnTime EarliestTime:=RunWhenForm1, Procedure:=cRunWhatForm1, Schedule:=False
        ElseIf FormName = "UserForm2" Then
            Application.OnTime EarliestTime:=RunWhenForm2, Procedure:=cRunWhatForm2, Schedule:=False
        End If
    End Sub
    
    Private Sub ShowForm(ByVal FormName As String)
        AppActivate Application.Caption
        Application.WindowState = xlNormal
        If FormName = "UserForm1" Then
            With UserForm1
                .StartUpPosition = 0
                .Left = Application.Width / 5
                .Top = Application.Height / 2.5
                .TextBox1 = "Buy apples"
                .Show vbModeless
            End With
        ElseIf FormName = "UserForm2" Then
            With UserForm2
                If IsForm1Loaded Then
                    .StartUpPosition = 0
                    .Left = UserForm1.Left + UserForm1.Width + 10
                    .Top = UserForm1.Top
                End If
                .TextBox1 = "Buy oranges"
                .Show vbModeless
            End With
        End If
        Call StartTimer(FormName)
    End Sub
    
    Private Function IsForm1Loaded() As Boolean
        Dim i As Long
        For i = 0 To VBA.UserForms.Count - 1
            If VBA.UserForms(i).Name = "UserForm1" Then
                IsForm1Loaded = True
                Exit For
            End If
        Next i
    End Function
    Last edited by Jaafar Tribak; Jul 22nd, 2019 at 12:36 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  7. #7
    Board Regular
    Join Date
    Oct 2006
    Posts
    535
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Displaying a userform using Application.onTime

    Quote Originally Posted by Jaafar Tribak View Post
    Hi,

    See if this works for you: The idea is to start the timers upon opening the workbook and stop them upon closing.

    At 10:00 AM UserForm1 should popup with TextBox1 displaying 'Buy apples' and at 02:00 PM, Userform2 pops up with TextBox1 displaying 'Buy oranges'.

    If at 02:00 PM, UserForm1 is still on display, UserForm2 will be located to the side of UserForm1 otherwise, UserForm2 will be shown at the center of the screen as normal.

    If the workbook is left open, the userforms will be displayed again in the same fashion the next day.

    Note: Both UserForms must be displayed Modeless.


    Code in the ThisWorkbook Module
    Code:
    Option Explicit
    
    Private Const RunWhenForm1 As String = "10:00:00"  '<== change this time const to suit
    Private Const RunWhenForm2 As String = "14:00:00"  '<== change this time const to suit
    
    Private cRunWhatForm1 As String
    Private cRunWhatForm2 As String
    
    
    Private Sub Workbook_Open()
        Call StartTimer("UserForm1")
        Call StartTimer("UserForm2")
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Call StopTimer("UserForm1")
        Call StopTimer("UserForm2")
    End Sub
    
    
    Private Sub StartTimer(ByVal FormName As String)
        If FormName = "UserForm1" Then
            cRunWhatForm1 = "'" & Me.CodeName & ".ShowForm """ & FormName & "'"
            Application.OnTime EarliestTime:=RunWhenForm1, Procedure:=cRunWhatForm1, Schedule:=True
        ElseIf FormName = "UserForm2" Then
            cRunWhatForm2 = "'" & Me.CodeName & ".ShowForm """ & FormName & "'"
            Application.OnTime EarliestTime:=RunWhenForm2, Procedure:=cRunWhatForm2, Schedule:=True
        End If
    End Sub
    
    Private Sub StopTimer(ByVal FormName As String)
        On Error Resume Next
        If FormName = "UserForm1" Then
            Application.OnTime EarliestTime:=RunWhenForm1, Procedure:=cRunWhatForm1, Schedule:=False
        ElseIf FormName = "UserForm2" Then
            Application.OnTime EarliestTime:=RunWhenForm2, Procedure:=cRunWhatForm2, Schedule:=False
        End If
    End Sub
    
    Private Sub ShowForm(ByVal FormName As String)
        AppActivate Application.Caption
        Application.WindowState = xlNormal
        If FormName = "UserForm1" Then
            With UserForm1
                .StartUpPosition = 0
                .Left = Application.Width / 5
                .Top = Application.Height / 2.5
                .TextBox1 = "Buy apples"
                .Show vbModeless
            End With
        ElseIf FormName = "UserForm2" Then
            With UserForm2
                If IsForm1Loaded Then
                    .StartUpPosition = 0
                    .Left = UserForm1.Left + UserForm1.Width + 10
                    .Top = UserForm1.Top
                End If
                .TextBox1 = "Buy oranges"
                .Show vbModeless
            End With
        End If
        Call StartTimer(FormName)
    End Sub
    
    Private Function IsForm1Loaded() As Boolean
        Dim i As Long
        For i = 0 To VBA.UserForms.Count - 1
            If VBA.UserForms(i).Name = "UserForm1" Then
                IsForm1Loaded = True
                Exit For
            End If
        Next i
    End Function
    ...Hi Jaafar - thanks - you really put some thought into this and a appreciate it. I can tell by this code you
    have a solid understanding of what I want to do and why I'm having some issues - the main one being addressed by you that I can see is that you suggest two forms for two different timed displays. Between my last reply and this post, I actually created 4 separate userforms, all identical with different names. I also created 4 separate macros for each Application.onTIme sequence to run. I positioned each form in a minature version to pop up at the proper time. All works OK - but not perfectly.
    Question 1: The problem with this is, suppose I or any user has 20 or more things for him/her to be reminded about in the future. Does that mean 20 or more userforms and macros would have to be created ?

    I need to first correct an answer I made to your logical question above - when the second later userform tries to display at its later time, an error message is generated saying the macro may not be available...etc., I used the SAME macro for both timed displays, which would explain the error message - the way I interpret the message is that Excel is trying to use the SAME macro which is already being used by the earlier userform display. So, for each new form, must come a new unique macro for it and for it alone.
    Question 2: Is that what's happening here ?

    I need to study your code. Please continue to collaborate with me on this, as I feel we are getting closer to the truth of being able to make this work error free. I'd like your comments on my questions and comments above.

    Thanks a mil for all your help in this,
    cr
    Last edited by chazrab; Jul 22nd, 2019 at 01:57 PM.

  8. #8
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,315
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Displaying a userform using Application.onTime

    Hi,

    If there are many message reminders, using one userform for each and every reminder is not going to be practical.

    How about using a single userform that has various labels ?... Each label will will hold a reminder message displayed at its set time.

    But I have the following questions:

    1- You said, the total number of messages is not known in advance.... So if that is the case, how are we going to know how many labels we need in the userform?!

    2- If you don't know the total number of messages in advance, how do you know the time each label is supposed to be shown and well as the message which is to be displayed?


    Regards.
    Last edited by Jaafar Tribak; Jul 22nd, 2019 at 06:52 PM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  9. #9
    Board Regular
    Join Date
    Oct 2006
    Posts
    535
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Displaying a userform using Application.onTime

    Quote Originally Posted by Jaafar Tribak View Post
    Hi,

    If there are many message reminders, using one userform for each and every reminder is not going to be practical.

    How about using a single userform that has various labels ?... Each label will will hold a reminder message displayed at its set time.

    But I have the following questions:

    1- You said, the total number of messages is not known in advance.... So if that is the case, how are we going to know how many labels we need in the userform?!

    2- If you don't know the total number of messages in advance, how do you know the time each label is supposed to be shown and well as the message which is to be displayed?


    Regards.
    ..Thanks again. You may not want to do this, but it'll solve all these issues and save time with long back and forth thread comments. For the last 10 years I've been using CuteReminder. This is EXACTLY what I wish to create, but within Excel using a customized form. The software is a free download, and I assure you it's not virus infested. If you would be willing to download this, enter a few messages at various days and times, go away for a while and return after the times have elapsed, you'll see the messages all displayed one uider the other. You can set small time intervals, repeat a message and much more. What I like so much about it, is that it's not a huge display. Just a small reminder about 1 in. square on the right side of the screen which can be expanded. This again, is waht I'd like very much to create in Excel. I wish I knew how they are able to so accurate make the messages display, postpone, repeat, etc., I've wondered for years if this app is tied to the windows clock an developed in c or c++, which I know absolutely nothing about. Anyway the link is www.cutereminder.com.
    I understand if you do not want to download it - I've no problem with that. But if you do, seeing the beauty of how it works would help greatly in using it as a model to develop in Excel, which many people could find useful. Just some thoughts. Let me know after a while what you think.

    Again, thx for all your help.
    cr
    Last edited by chazrab; Jul 22nd, 2019 at 08:09 PM.

  10. #10
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,315
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Displaying a userform using Application.onTime

    I like the idea but I am afraid, that would be a rather involved project and I don't think excel is the ideal tool for it... In my humble opinion, what is suitable for this is a propper executable.

    Hopefully, if I or anybody else can think of some 'solution', I am sure they will post it here.

    Regards.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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