+ Reply to Thread
Results 1 to 4 of 4

With ThisWorkbook command not working?

Hybrid View

  1. #1
    Registered User
    Join Date
    12-24-2014
    Location
    la, usa
    MS-Off Ver
    2003
    Posts
    22

    With ThisWorkbook command not working?

    I'm running a macro in a particular workbook to display a real-time clock, and I'm trying to restrict it to this one workbook. I'm using the code With ThisWorkbook.Worksheets("") , but the Macro is still running in all other open workbooks.

    Below is the macro I'm using:

    Sub Recalc()
    
    With ThisWorkbook.Worksheets("Break")
    Range("A4").Value = Format(Time, "hh:mm:ss AM/PM")
    
    Call SetTime
    
    End With
    End Sub
    
    Sub SetTime()
    
    With ThisWorkbook.Worksheets("Break")
    SchedRecalc = Now + TimeValue("00:00:01")
    Application.OnTime SchedRecalc, "Recalc"
    
    End With
    End Sub
    
    Can someone tell me what I'm doing wrong?
    Last edited by bvercher; 12-24-2014 at 02:01 PM.

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: With ThisWorkbook command not working?

    perhaps missing the point (before Range)
    With ThisWorkbook.Worksheets("Break")
       .Range("A4").Value = Format(Time, "hh:mm:ss AM/PM")

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: With ThisWorkbook command not working?

    This doesn't have a worksheet or workbook reference.
    Range("A4").Value = Format(Time, "hh:mm:ss AM/PM")
    Try this.
    Sub Recalc()
    
        With ThisWorkbook.Worksheets("Break")
            .Range("A4").Value = Format(Time, "hh:mm:ss AM/PM")
            Call SetTime
        End With
    End Sub
    
    Sub SetTime()
    
        SchedRecalc = Now + TimeValue("00:00:01")
        Application.OnTime SchedRecalc, "Recalc"
    
    End Sub
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    12-24-2014
    Location
    la, usa
    MS-Off Ver
    2003
    Posts
    22

    Re: With ThisWorkbook command not working?

    Wow, that was incredibly simple. Thanks, you guys!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. copy paste funtion with Thisworkbook and ActiveSheet not working?
    By trillium in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-25-2011, 04:02 PM
  2. ThisWorkbook.SaveAs not working?
    By sweep in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-16-2010, 08:09 AM
  3. ThisWorkbook macros stopped working
    By smited01 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-09-2008, 08:20 AM
  4. [SOLVED] ThisWorkbook.Path not working?
    By roadie.girl@gmail.com in forum Excel General
    Replies: 1
    Last Post: 01-17-2006, 04:35 PM
  5. [SOLVED] ThisWorkbook.Path not working...
    By rmullen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-17-2006, 04:20 PM

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