VBA Find external links in unusual places (E.g. Buttons, Charts, conditional formatting etc)

[fatboy_1989]

Hi

How can I use VBA to find and list on a new sheet where external links exist in:

• Buttons (ones used to run macros when clicked)
• Charts
• Pictures
• Conditional Formatting
• Data Validation (e.g. lists)
• Shapes
• Pivot Tables
• Named Ranges
• Cells (likely to be the "easier" ones to find)

Thanks

 

[Tim_Excel_]

This is too complicated for me to write a code to help you out (that, and the reason it does not seem you have tried any code yourself), but I can help you out by giving you some tips.

The FIND() function, nor the MATCH() function, can go through all those possible objects you listed. There is no one simple function to do what you are trying to accomplish.

What you could / should do, is to loop through all those individual objects and ranges you listed. Something like the example code as follows:

Sub searchCF() 'this is not real code. Do not try to execute this

Dim frmula As String
Dim exlinkstr As String

exlinkstr = "https://www.google.com"

For each frmula in ThisWorkbook.Sheets(1).ConditionalFormatting
    if frmula LIKE "*" & exlinkstr & "*" then MsgBox frmula
Next frmula

End Sub

Try to search for getting the right information from these objects. E.g., this link here should help you out getting links from conditional formatting.

The FIND() function is easily usable if you want to find a certain string in a range. Look at this MDOCS page to learn about the FIND() function.


Good luck

 

[fatboy_1989]

Thanks Tim_Excel_ for the reply.

I did try one code i found which was quite useful. It just didn't find the external links within buttons (the ones that VBA macros can be run when pressed).
It was from this place: https://stackoverflow.com/questions/48337861/removing-external-links

Would anyone know how to update this or use something similar for the buttons?

Thanks

 

[Tim_Excel_]

Where are these links located, in the code or in one of the button's properties, such as the caption?

If it does not work for buttons, try going through the buttons in a worksheet. Code below goes through the buttons' captions.

Dim btn as OLEObject
Dim exlinkstr As String

exlinkstr = 'link here

With Workbooks(REF).Sheets(REF)

For each btn in .OLEObjects
    If btn.Text Like "*" & exlinkstr & "*" Then
        MsgBox btn.Name
       
    '>>>>alternatively, change the link
        'startn = Instr(1, btn.Text, exlinkstr)
        'strlength = LEN(exlinkstr)
        'newlinkstr = 'new link
        'btn.Text = Left(btn.Text, startn - 1) & newlinkstr & Right(btn.Text, Len(btn.Text - (strlenght + startn - 1))
    End If
Next btn
End With

 

[fatboy_1989]

The external links are found when right clicking the button.
The assigned macro that is trying to be run refers to a macro within an external spreadsheet
Usually when i try to assign a macro, i choose from the VBA within the exisiting spreadsheet, but i need some VBA code that tells me what buttons have been assigned macro names from other spreadsheets....
So the external link is not within the title/text of the button but actually within the "Macro name:" entry of the "Assign Macro" Window.
I would now know what the external link i am looking for would be, i would need it to be listed for me to show me where is the external link and what it is.