Macro link issue

mmmreece

New Member
Joined
Apr 8, 2015
Messages
22
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Kerryx

Board Regular
Joined
May 6, 2016
Messages
101
I have used this code below to update multiple sheets in the past

VBA Code:
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

New Member
Joined
Apr 8, 2015
Messages
22
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

Board Regular
Joined
May 6, 2016
Messages
101
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

New Member
Joined
Apr 8, 2015
Messages
22
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,198
Messages
5,600,280
Members
414,374
Latest member
akbir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top