Displaying a userform using Application.onTime

[chazrab]

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

 

[Jaafar Tribak]

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

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" [COLOR=#008000][B]'change text box text after 2 seconds.[/B][/COLOR]
    oForm.Show
End Sub


Sub OnTimeMacro()
    oForm.TextBox1.Text = "Second Message."
End Sub

 

[chazrab]

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

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.

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

 

[Jaafar Tribak]

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.

 

[chazrab]

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.

 

[Jaafar Tribak]

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

Option Explicit

Private Const RunWhenForm1 As String = "10:00:00"  [COLOR=#008000]'<== change this time const to suit[/COLOR]
Private Const RunWhenForm2 As String = "14:00:00"  [COLOR=#008000]'<== change this time const to suit[/COLOR]

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

 

[chazrab]

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

Option Explicit

Private Const RunWhenForm1 As String = "10:00:00"  [COLOR=#008000]'<== change this time const to suit[/COLOR]
Private Const RunWhenForm2 As String = "14:00:00"  [COLOR=#008000]'<== change this time const to suit[/COLOR]

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

 

[Jaafar Tribak]

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.

 

[chazrab]

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

 

[Jaafar Tribak]

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.