Forum Discussion
Recorded Macro does not capture a click on a web-link (URL)
start recording a Macro
click on a URL (cell B8) this link does work
web page does open just fine
stop recording
When I play it back it will move the the target cell (B8) but then no click (select) takes place, it just stops there. When I look at the code it shoes this, why does it not work when played back?
Sub Macro10()
'
' Macro10 Macro
' 10
'
'
Range("B8").Select
End Sub
The cell contained a formula to create the hyper-link and not the actual resultant, this was the key to searching for a solution.
I found an article that showed how to replace the formula in a cell with it's result
And now all works fine when recording a macro with a mouse click on a cell that has a calculated hype-link. I can't believe it was as simple as clicking F2 and then F9 as described here:
https://support.office.com/en-us/article/replace-a-formula-with-its-result-38001951-c0e8-4fbd-9048-37ef81e2891e
And best of all the new cells all copy down the column with variables in tackt and the resultant as intended.
4 Replies
Replicated your case. Recorded a similar macro. The code I get looks like this:
Sub Macro1() ' ' Macro1 Macro ' ' Range("A1").Select Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True End SubThank you for showing your test! That showed me somthing but I still need help. I am sorry I did not completely describe what I was doing, can we try again please.
A3 is a cell I type a search term into such as jump rope
B3 has a formula to create the link with the contents of A3 inserted as follows:
=HYPERLINK("https://www.amazon.com/s?k="&A3&"&ref=nb_sb_noss_2")
When I click on the link it works fine
When the recorded macro selects B3 is it seeing the formula and not the hyperlink?
THANKS!
With some help of
https://docs.microsoft.com/en-us/office/vba/api/Excel.Workbook.FollowHyperlink
this seems to work:
Sub FollowHLink() Dim Hlink As String Range("A1").Select Hlink = ActiveCell ActiveWorkbook.FollowHyperlink Address:=Hlink End SubYour hyperlink formula was entered in A1. Perhaps you can get it to work now.
