Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2008

Tip: Looking for answers? Try searching our database.

switching window stops running macros

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
N+ - 05 Mar 2008 13:14 GMT
hi all !
i have a cyclic vbe program that bob gave to me and it works fine..
but i need to open it in more than one windows simoultaneously, and when i
switch to window 2, the timer in the  window 1 stops !!
maybe someone can suggest me how to do!  
this is the code:

TIMER

                      Add this to a standard code module

Option Explicit

Public nTime As Double

Public Sub StartTimer()
   On Error Resume Next
   Application.OnTime nTime, "RunTimer", , False
   On Error GoTo 0

   ActiveSheet.Range("A1").Value = 0
   RunTimer
End Sub

Public Sub StopTimer()
   Application.OnTime nTime, "RunTimer", , False
End Sub

Public Sub RunTimer()
   With ActiveSheet.Range("A1")
       .Value = .Value + TimeSerial(0, 0, 1)
       .NumberFormat = "hh:mm:ss"
   End With
   nTime = Now + TimeSerial(0, 0, 1)
   Application.OnTime nTime, "RunTimer"
End Sub
Barb Reinhardt - 05 Mar 2008 14:05 GMT
I'm guessing it's seeing a different activesheet when you go to a different
window.  You're going to have to specify the workbook and sheet you want, I
think.  

Try this

Option Explicit

Public nTime As Double

Public Sub StartTimer()
Dim aWB As Workbook
Dim aWS As Worksheet

Set aWB = ThisWorkbook
Set aWS = aWB.Worksheets("Sheet1")  'change sheet name as needed
   On Error Resume Next
   Application.OnTime nTime, "RunTimer", , False
   On Error GoTo 0

   aWS.Range("A1").Value = 0
   RunTimer
End Sub

Public Sub StopTimer()
   Application.OnTime nTime, "RunTimer", , False
End Sub

Public Sub RunTimer()
Dim aWB As Workbook
Dim aWS As Worksheet

Set aWB = ThisWorkbook
Set aWS = aWB.Worksheets("Sheet1")  'change sheet name as needed
   
   With aWS.Range("A1")
       .Value = .Value + TimeSerial(0, 0, 1)
       .NumberFormat = "hh:mm:ss"
   End With
   nTime = Now + TimeSerial(0, 0, 1)
   Application.OnTime nTime, "RunTimer"
End Sub

Signature

HTH,
Barb Reinhardt

> hi all !
> i have a cyclic vbe program that bob gave to me and it works fine..
[quoted text clipped - 32 lines]
>     Application.OnTime nTime, "RunTimer"
> End Sub
N+ - 05 Mar 2008 15:51 GMT
ty barb i will try as soon as possible and tell u how it works !

> I'm guessing it's seeing a different activesheet when you go to a different
> window.  You're going to have to specify the workbook and sheet you want, I
[quoted text clipped - 75 lines]
> >     Application.OnTime nTime, "RunTimer"
> > End Sub
N+ - 06 Mar 2008 12:42 GMT
hi barb it is working ok ty !!
but if i had to insert my macro , where should i put "her" for avoiding it
to run multiply in each code cycle,  when i open many times the same window ?
ty!

> hi all !
> i have a cyclic vbe program that bob gave to me and it works fine..
[quoted text clipped - 32 lines]
>     Application.OnTime nTime, "RunTimer"
> End Sub
N+ - 12 Mar 2008 18:49 GMT
done..modified all "range" with aws.range !! ty
but i cant avoid calculatin 3 times a ccle with something else than
application.calculate !

> hi barb it is working ok ty !!
> but if i had to insert my macro , where should i put "her" for avoiding it
[quoted text clipped - 37 lines]
> >     Application.OnTime nTime, "RunTimer"
> > End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.