Macro link issue

[mmmreece]

Hi. I've been having consisent problems with Excel for months now. I have autosave enabled but sometimes it simply doesn't save. I've woken up this morning to find all of yesterdays work missing. I have gone into version history and found the missing work and restored the version deleting the version that opened missing the work. Now, when I try to run a macro from a button I get an error message saying "We can't connect to ;https:// ..... version" (I've omitted the details). Please make sure you're using the correct web address.
I can fix this issue by clicking the button that runs the macro, selecting assign macro and finding the macro in the current document.
My question is this... Can I update all these links in one go. I have over 100 sheets in this workbook and 10 to 12 buttons on every sheet so updating all these macros manually is a complete whole day task.
There is nothing showing in the links tab in excel but, then, I wasn't expecting it as I would have thought it would be a VBA issue.

 

[Kerryx]

I have used this code below to update multiple sheets in the past

Sub Optimise(Flag As Boolean)
On Error Resume Next
F = Not Flag
Application.ScreenUpdating = F
Application.DisplayAlerts = F
Application.EnableEvents = F
Application.DisplayStatusBar = F
ActiveSheet.DisplayPageBreaks = F
If F = True Then
Application.Calculation = xlCalculationAutomatic
Else
Application.Calculation = xlCalculationManual
End If
On Error GoTo 0
End Sub


Sub Update()
'switch off updating to stop screen flickering

Optimise (True)

  'Refreshall **NB** make sure the option in the connection
  'Enable background refresh is disabled
  'as this would run the rest of the code before the refresh has completed
  
         DoEvents
         
    Dim MySheets, i As Long

' List of sheets you want to update
    MySheets = Array("Sheet1", "Sheet2", "Sheet3")
    For i = 0 To UBound(MySheets)

    ' Update Macro - Download and update from the website   
        ActiveWorkbook.RefreshAll
    
    Sheets(MySheets(i)).Activate

    Next

Optimise (False)

 

[mmmreece]

Thankyou so much for your reply, however, the problem is is that the Sheet textboxes are now referencing macros in the now-deleted version. Therefore, I have to right click on every textbox and then select the macro that is stored in THIS version. I've done about 3 sheets but I still keep on finding more all the time. I have spent a lot of this morning trying to find where the code for sheet textboxes is stored so that I could just do a control-H and replace the lot in one go, but, unfortunately, I can't seem to find it.

 

[Kerryx]

Sorry but unable to assist on the issue as i have limited knowledge on vba myself, hoefully someone with more indepth knowledge can help but would advise giving them some more information on the existing macros you are running.

 

[mmmreece]

Thankyou for your time. If I can find a few hours I'll go through it all manually but it's a huge job so was hoping for a find/replace kind of solution but, not knowing where the base code for sheet textboxes is stored, makes it quite an issue