Virtual Clock

[Scott R]

Hi Guys,
Im trying to get a virtual clock in excel and found this prev answer from
11/17/2007 which apparently works but I unfortunately, dont know anything
about 'forms' and am a little confused as to how to do this.. is there
another way to explain this procedure?

any help would be great as i really would like this to work.. below is the
prev answer i found..

Answer

hi
create a small form in excel with a single text box. then put this code in
the form.
Private Sub CB1_Click()
Load UserForm1
UserForm1.Show 0
Call xlClock
End Sub
Private Sub ShowxlClock()
If UserForm1.Visible = True Then
UserForm1.tb1.Value = Format(Now, "hh:mm:ss AM/PM")
Application.OnTime Now + TimeSerial(0, 0, 1), "xlClock"
Else
Exit Sub
End If
End Sub
you can install it as an addin. i have it in my personal.xls
i run this from a custom icon on my custom toolbar.

regards
FSt1

 

[JLatham]

I'm not sure that FSt1 included everything you need, or he changed some
routine names when he put the code up.
First, you'd need to go into the VB Editor (press [Alt]+[F11] to do so) and
then choose Insert | Form
That form will automatically be named UserForm1. From the toolbox, grab a
text box control and place it on the form and size it large enough to hold
the time entry later. You'll need to rename the text box "tb1". You also
need a command button on the form and name it CB1. Then add the code, BUT it
looks like where he has Call xlClock
and
Application.OnTime Now + TimeSerial(0,0,1), "xlClock"
you need to change xlClock to ShowxlClock

Somewhere in your own workbook, you'll need a way to get the form displayed,
that can be done by creating a standard code module (in the VB Editor, choose
Insert | Module) and copy and paste this code into it and run it later from
Tools | Macro | Macros
Sub ShowClock()
UserForm1.Show 0
End Sub

I know these are brief explanations, but perhaps they'll get you started.

 

[JLatham]

I don't think I guessed well at what was happening with FSt1's code, so I put
together something that should be close to what he had going on and you can
grab the workbook it is in and look at it all from:
http://www.jlathamsite.com/uploads/ExcelClockForm.xls
Just save it to your system and run it. There's some explanatory text in it
to help you see it working and to get into the VB Editor to learn more.

Hope it helps a bit.

 

[Scott R]

Thankyou so much for that! It works great. I really appreciate your time to
do that and help me out.

 

[JLatham]

Glad I could help. Keep in mind that you could make copies of that workbook
as the basis of other workbooks that you want to have that clock in. Or if
you'd like info on how to "separate out the clock and associated code" so
that you could pull it into an existing workbook, I believe my email addy was
in the workbook, feel free to get in touch if you need to for that.