Closed Thread
Results 1 to 3 of 3

Module1 Problem

Hybrid View

imrvlad Module1 Problem 12-26-2013, 02:41 AM
AndyLitch Re: Module1 Problem 12-26-2013, 07:20 AM
Fotis1991 Re: Module1 Problem 12-26-2013, 10:11 AM
  1. #1
    Registered User
    Join Date
    09-30-2013
    Location
    Israel
    MS-Off Ver
    Excel 2010
    Posts
    9

    Module1 Problem

    Hi!

    I used the next code to have a running clock in one of my workbooks:

    In Module1:

    Dim SchedRecalc As Date
    
    Sub Recalc()
    
    With Sheet2.Range("D2")
    
    .Value = Format(Time, "hh:mm:ss AM/PM")
    
    End With
    
    Call SetTime
    
    End Sub
    
    Sub SetTime()
    
    SchedRecalc = Now + TimeValue("00:00:30")
    
    Application.OnTime SchedRecalc, "Recalc"
    
    End Sub
    
    Sub Disable()
    
    On Error Resume Next
    
    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
    
    End Sub
    and as ThisWorkbook:

     
    Private Sub Workbook_WindowActivate(ByVal Wn As Window)
        Module1.Recalc
    End Sub


    The Problem is that now, when the workbook is closed and another excel is running, every time when the clock updates (I assume) it opens up the workbook with the clock. The only way I stop it is by closing all excel workbooks and opening the one I need (without the module). How can I stop the module from running when I close the workbook with the Macro\Module?

    Please note: I have no any clue in VB. Every code I've done was with the help of nice peaple on this forum. So if you help, please do that in details.

    Thank you in advance!

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Module1 Problem

    You need to stop the Ontime sub when the workbook closes by running the disable sub.Try this in the This.Workbook module

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Disable
    End Sub
    Elegant Simplicity............. Not Always

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Module1 Problem

    Welcome to the Forum, unfortunately:

    This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

    Thread Closed.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Cannot Call UDF written in Module1
    By slash_gnr3k in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-17-2012, 05:53 AM
  2. Deleting Module1
    By braydon16 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-06-2009, 12:43 PM
  3. Transfer Module1 from PC to Mac
    By Jim May in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-05-2006, 01:45 PM
  4. [SOLVED] Run Module1 first then automatically run Module2
    By Bowbender@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-20-2005, 01:05 PM
  5. Module1 vs Thisworkbook for Macro
    By Craigm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-28-2005, 08:17 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1