+ Reply to Thread
Results 1 to 3 of 3

Run time error with auto update VBA code

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    884

    Run time error with auto update VBA code

    Good morning happy campers,

    I have the following codes:
    Module 1
    Public Wb As Workbook
    Public RunWhen As Date
    Public RunWhat As String
    Public Executed As Boolean
    Public Sub GetSavesStarted()
    Set Wb = ThisWorkbook
    RunWhat = "SaveThis"
    Executed = True
    Call SaveThis
    End Sub
    Public Sub SaveThis()
    Application.DisplayAlerts = False
    Wb.UpdateLink Name:=Wb.LinkSources
    Application.DisplayAlerts = True
    RunWhen = Now + TimeValue("00:00:05")
    Application.OnTime EarliestTime:=RunWhen, Procedure:=RunWhat, _
            Schedule:=True
    End Sub
    This Workbook -
    Private Sub WorkBook_Open()
        Call GetSavesStarted
    End Sub
    Private Sub WorkBook_BeforeClose(Cancel As Boolean)
    If Executed = True Then Application.OnTime EarliestTime:=RunWhen, Procedure:=RunWhat, _
            Schedule:=False
    End Sub
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Call GetSavesStarted
    End Sub
    For the most part, all the codes work fine, in short they refresh the values on the sheet every 5 seconds, triggered by opening the workbook AND when a change is made to the workbook.
    However, at times i get a run time error - 'Method "UpdateLink' of Object '_Workbook' failed
    highlighting the following in yellow (from Module1)
    Wb.UpdateLink Name:=Wb.LinkSources
    Could anyone please shed some light for me please?
    Kind regards.

  2. #2
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Run time error with auto update VBA code

    Couple of possibilities

    the wb variable could have lost its focus. Try replacing the line with

    Thisworkbook.UpdateLink Name:=Thisworkbook.LinkSources
    one of the links to be updated may be locked by user activities. If you keep getting this error it may be worth it to change your VBA code to loop through all existing links and update them individually. The advantage would be that you get to see which link is causing a problem. Is it always the same one, or a different one every time. What were you doing at the time of the error etc.
    If you like my contribution click the star icon!

  3. #3
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    884

    Re: Run time error with auto update VBA code

    Good morning OllieB,

    Thank-you for the suggestion, i will change the code to your idea and trial it, fingers crossed it works fine!
    If not i will report back and seek further assistance!

    Kind regards (again)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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