Solved

Update Excel VBA-based time display even when workbook is inactive

Posted on 2010-11-22
16
527 Views
Last Modified: 2012-05-10
Help with installing VBA code in Excel please (already have the code)
11/20/10 09:20 PM, ID: 26629410
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26629410.html

Hello,

This is a new question but relates to the above thread (which may or may not be needed).  It refers to Excel (2007) VBA code that creates a time-of-day display in cell A1 which updates every minute automatically (it's essentially a clock).

Part of the code is in the Workbook folder:
 
Private Sub Workbook_Open()
DoTick
End Sub

Open in new window

and part is in a module:
 
Option Explicit

Public Sub DoTick()
Application.Range("A1").Value = Now()
Application.OnTime Now() + TimeValue("00:01"), "DoTick"
End Sub

Open in new window


The clock works well as long as the workbook in which it is located is the active window.  If another application or even a different instance of Excel is activated, the clock stops updating.  I suppose that would be OK if when it is reactivated, it would immediately update.  However, it does not.  Moreover, even pressing the re-calculate button does not do it.  The only way I can find to get it updated is to click on cell A1 and press Enter.

Questions:

1)  Is there any way to modify the code so it will keep running (updating) even when behind other windows (i.e. when it is inactive)?

2)  If not, is there a way get it to update immediately whenever that workbook is re-activated?

Thanks
0
Comment
Question by:Steve_Brady
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +1
16 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 34191995
It seems to keep working for me.. but I would use

Public Sub DoTick()
Worksheets(1).Range("A1").Value = Now()
Application.OnTime Now() + TimeValue("00:00:01"), "DoTick"
End Sub

Instead of Application, which could go to any sheet...
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34192081
I would go one step ahead...

Public Sub DoTick()
Worksheets("WorksheetName").Range("A1").Value = Now()
Application.OnTime Now() + TimeValue("00:00:01"), "DoTick"
End Sub

That way I am assured of the code running in the background whatever be the order of the opening of worksheets.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34192094
Sorry I meant actually meant Workbook ... so....

Public Sub DoTick()
application.workbooks("WorkbookName").Worksheets(1).Range("A1").Value = Now()
Application.OnTime Now() + TimeValue("00:00:01"), "DoTick"
End Sub
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:Steve_Brady
ID: 34192435
Thanks for the responses.

>>application.workbooks("WorkbookName").Worksheets(1).Range("A1").Value = Now()

So for this line, if I have a file called xyz.xlsm with a sheet tab titled Data A, would it look like this:

    application.workbooks("xyz").Worksheets(Data A).Range("A1").Value = Now()

or this:

    application.workbooks(xyz).Worksheets(Data A).Range("A1").Value = Now()

or this:

    application.workbooks("xyz").Worksheets("Data A").Range("A1").Value = Now()

or none of the above?

i.e. are parens, quotes, etc. critical and when do you use them?  Also, are caps (case sensitivity) important to use/not use, etc.?


0
 

Author Comment

by:Steve_Brady
ID: 34192488
>>>>application.workbooks("WorkbookName").Worksheets(1).Range("A1").Value = Now()

Oh, and what if I want it to show up on more than one spreadsheet, but not all Excel files (i.e. how do you include multiple something)?  

e.g. xyz.xlsm, sheets: Data A, Data B but not Data C

or

xyz.xlsm, sheet:  Data A and 123.xlsm, Sheet 1

(I just want to start getting this VBA syntax figured out.)

Thanks
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34192491
3rd one.
Quote the names because they are strings
0
 
LVL 13

Assisted Solution

by:gbanik
gbanik earned 250 total points
ID: 34192501
application.workbooks("xyz.xlsm").Worksheets("Data A").Range("A1").Value = Now()
0
 

Author Comment

by:Steve_Brady
ID: 34192515
...one more (in 60 seconds or less):

What's the difference between dots (periods), commas, and plus or minus signs in VBA code?
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34192518
cyberkiwi ...i think it wouldnt work without the file extension.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34192611
I am not able to find the context of your question. They are all different characters used for disjoint purposes.
Like "+" can be used for addition, but can be used for concatenation also.
"." is a separator used to connect a method/property to its object
"," is normally used to separate parameters of a function/subroutine
"-" is used for subtraction only ... i think.

But again... whats the context???
0
 

Author Comment

by:Steve_Brady
ID: 34192890
>>But again... whats the context???

I'm pretty comfortable with parens and commas in Excel formulas for example.  And +-*/ are always the four math fxns, and & and "" are pretty straight forward.  Sometimes parens are empty like =NOW() for example which I don't get but most other stuff is pretty intuitive after looking at lots of formulas.

So just want to start getting similar understanding of VBA.  For example, periods always seem to be present, e.g.

    application.workbooks("xyz.xlsm").Worksheets("Data A").Range("A1").Value = Now()

Here the periods seem to enable you to drill down, so to speak.  Is that kind of what they always do?

Also, I started reading a bit about expressions.  That's what the word "application" is right, but what does it mean?  I don't think it's the normal meaning I'm used to (i.e. software program).  

Pretty much just a few random questions.  Not a big deal tho.

Thanks for the help.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34193113
First... the complete VBA is around the Office Object Model, that is, Word, Excel, Powerpoint, etc... consider these as the "Application" objects. Technically speaking it is the
Excel.Application
object

VBA is very tolerant, hence when we write "Application" it interprets it as "Excel.Application" (or for that matter "Word.Application" depending on the context)

Still with me? ok.

According to this object model, everything u see in Excel, be it a Workbook, worksheet, cell, column, row, hyperlink, chart, etc etc etc are objects. These objects are hierarchically linked with each other..... like Application.Workbooks
It means the "Application" object holds the collection of Workbook objects.

There with me? fine.

Now how do you get a workbook out of this Workbooks collection? We "pull" it using the Index like
application.workbooks(1)  ... means we are requesting for the 1st workbook object from the Workbooks collection which is lying on the Application object. Similarly, we can ask for say the worksheet object "Sheet1" from the Worksheets collection
Application.Workbooks(1).Worksheets("Sheet1")
Notice that the "index" can be a number or a name.

If you have read till here, u r ready to explore some good articles, not like this one ;)
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34195475
Not really on point, but I thought I'd address your comment:
"Sometimes parens are empty like =NOW() for example which I don't get"

If you use a function in a worksheet cell, you have to use parentheses, even if there are no arguments, otherwise Excel will think it is a defined name.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34195606
@rorya, :) I just tried to give a start.

As far as that question is concerned why parenthesis is used in some cases and sometimes u dont, is still a mystery to me right from the early VB days :) ..... nor do I want to know :) ...  What I know for sure is that VB and VBA are very forgiving and everything works ;)
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34195826
I was addressing the OP's comment about parentheses for formulas, not VBA. :)
0
 

Author Closing Comment

by:Steve_Brady
ID: 34208886
multiple good responses
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question