Thread: Refresh userform periodically

Hi everyone,

I was wondering how i could refresh a userform every every 3 seconds or so?

I'm using

application.ontime now + timevalue("00:00:03")

within the userform_activate module?

am i pi55in in the wind with that one?

Cheers

Hey Magoo, nice 1337.

I worry about your eyesight and a userform model or not model. It always happen to me that when a userform is showing, I can't do much else but work on it. If you do a refresh, is it because something on the sheet is changing? I don't think that can happen while the userform is displayed. I may be wrong with a modeless userform.

Look at the section in http://www.tushar-mehta.com/publish_...rinterface.htm titled A userform design model for forms shown modeless It may help answer your question.

Hi Marv,

OK bit confused lol, Whats a 1337? also why the eyesight ref?

Anyway back to the point , I have created a mini "Chat app" in a userform that works by sending the messages to a access DB and also reading from the DB. I need to refresh the form every so often to update any new messages.

I have a refresh button that works fine but i would prefer not to have to refresh it manually.

any ideas?

not sure that link helped much buddy?

Cheers

Is your form modal or modeless?

answer: userform1.show 0

Bump?! any ideas folks?

You could use something akin to this

In a normal module

Option Explicit
Private Declare Function SetTimer Lib "user32" ( _
                          ByVal hwnd As Long, ByVal nIDEvent As Long, _
                          ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Private Declare Function KillTimer Lib "user32" ( _
                           ByVal hwnd As Long, ByVal uIDEvent As Long) As Long

Dim lRet                 As Long

Private Const TimeoutSecond = 3

Sub loadform()
   UserForm1.Show False
End Sub
Public Function TimerProc(ByVal hwnd As Long, ByVal uMsg As Long, _
                          ByVal idEvent As Long, ByVal dwTime As Long) As Long
   
   On Error Resume Next
   ' replace this with whatever code you like
   ' you may wish to put all the code as a public sub in the userform and call it from here
   UserForm1.Label1.Caption = Now
End Function

Sub StartTimer()
   lRet = SetTimer(0, 0, TimeoutSecond * 1000, AddressOf TimerProc)
End Sub
Sub StopTimer()
   On Error Resume Next
   KillTimer 0, lRet
End Sub

Userform code:

Private Sub UserForm_Initialize()
   StartTimer
End Sub

Private Sub UserForm_Terminate()
   StopTimer
End Sub

Why don't you use the double_click event or any other event to refresh the data ?
You didn't mention any periodicity in refreshing.

I don't want the user to have to refresh it that's why mate

keep it simple

In the useform:

Private Sub UserForm_Initialize()
 Thisworkbook.snb5
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 Application.OnTime CDate(Tag), "Thisworkbook.snb5", , False
End Sub

In the 'thisworkbook' module

Sub snb5()
 MsgBox "cccc"

 UserForm1.Tag = DateAdd("s", 3, Now)
 Application.OnTime CDate(UserForm1.Tag), "thisworkbook.snb5"
End Sub

Replace 'MsgBox "cccc"' by your refreshing code, 'mate'.

Hi Mr. Magoo,

OK bit confused lol, Whats a 1337? also why the eyesight ref?

1. You wrote pi55ed-off which uses numbers for similar shaped letters. This is called LEET spelling or 1337 converted.
2. Mr Magoo was one of my favorite cartoon, the blind old guy, along with Marvin the Martian.

I see that others have jumped into the answer. I'll read what they say in a minute after my very early morning coffee. (It's 5 am here!)