VBA Macro Not Running

BlakeT

New Member
Joined
Apr 23, 2020
Messages
9
Office Version
  1. 365
Platform
  1. MacOS
Hey everyone, thanks for any time or assistance, I'm a complete novice when it comes to this stuff.

I'm attempting to run a script in Excel that automatically opens a large amount of hyperlinks. Both scripts I'm attempting to use are from here: 3 Easy Ways to Batch Open Multiple Hyperlinks in Your Excel - Data Recovery Blog

The scripts I've tried are this, for a selected highlighted range in the file

VBA Code:
Sub BatchOpenHyperLinks_SelectedRanges()
    Dim objSelectedRange As Excel.Range
    Dim objHyperlink As Excel.Hyperlink

    'Get selected ranges
    Set objSelectedRange = Excel.Application.Selection
    For Each objHyperlink In objSelectedRange.Hyperlinks
        objHyperlink.Follow
    Next
End Sub

and
VBA Code:
Sub BatchOpenHyperLinks_Workbook()
    Dim objWorksheet As Excel.Worksheet
    Dim objSelectedRange As Excel.Range
    Dim objHyperlink As Excel.Hyperlink

    'Process each worksheet
    For Each objWorksheet In ThisWorkbook.Sheets
        For Each objHyperlink In objWorksheet.Hyperlinks
            objHyperlink.Follow
        Next
    Next
End Sub

I've made sure to change the security preferences to allow for VBA scripts/macros to run, but I'm still unsuccessful. I've tried implementing the code in a Module, in the Workbook itself, and in the individual Worksheet, and I'm still not getting anywhere.

Does anyone know what could be causing these to fail? I'm not getting any kind of error message that would begin to point me in the right direction. Every time I click "Run" (from VBA or from the Excel document), simply nothing happens.

Again, any help is appreciated.
 
FWIW, I created a range of links including cells with hyperlink formulas similar to the example you posted, and for me the code I posted recognizes ALL links, including the formula hyperlinks.

Are you doing this with Excel run on an Apple product? Could that be why? Because I tried formulating the link in multiple ways and got nothing.
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
No I'm using windows. But I would not think that would make any difference. The key would be to inspect the variable HLAddr. If it is accurately extracting the address formula, then the code should work. The address line in the msgbox would be another way to inspect whether the formula was correctly evaluated.
 
Upvote 0
At this point, this might be above my head completely, sadly. I don't know enough to inspect the lines myself and make adjustments. Your macro prompt did work but would only provide the message box you're talking about for the manually typed hyperlinks.
 
Upvote 0
Hi, I'm having the same problem, the Code can't open my hyperlinks, I made this links to do advanced google search, B4 are the filters and A7 are the words I have to look for.

=IF(ISBLANK([@Name]);"-";HYPERLINK(IF(B2=C4;"Fourth of July 2022" & A7 & "&as_oq=" & $B$4 &"&as_eq=&as_nlo=&as_nhi=&lr=&cr=&as_qdr=all&as_sitesearch=&as_occt=any&safe=images&as_filetype=&tbs=";"Fourth of July 2022" & A7 & "&as_oq=" & $B$3 &"&as_eq=&as_nlo=&as_nhi=&lr=&cr=&as_qdr=all&as_sitesearch=&as_occt=any&safe=images&as_filetype=&tbs=");"Google Advanced search " & A7))

I would like to run a macro to open up all my created links but all the macros I found don't recognize my formulas.

Do you guys found a solution?
 
Upvote 0
@BlakeT & @Noodletack - I was answering a similar question here:
VBA Code Stopped Working
And I believe the solution provided by @lrobbo314 here will most likely work for you too.
HYPERLINK() function not working with VBA .Follow

VBA is not recognising a formula generated Hyperlink as a Hyperlink and Selection.Hyperlinks.Count comes up as 0.
Here is lrobbo's suggesed method.

Rich (BB code):
Sub OpenLinks()
Dim cel As Range
For Each cel In Selection
    ActiveWorkbook.FollowHyperlink cel.Value
Next cel
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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
Back
Top