I am very new to marcos and am having trouble.

As part of a macro I was working on, I wanted to open the hyperlink of a selected cell (contains link to another workbook.)

When I run the macro it gives me the usual warning to make sure the link is from a trustworthy source, and asks if Id like to open the file. I click ok, then the hourglass comes up for a few seconds and then it goes away. Nothing happens though, the file does not open.

But, If I click the actual cell that contains the hyperlink, it gives me the warning, I click ok, and then the file opens. Its frustrating that it worked before I saved and closed it.

If I have it link to a word document, then the Macro will work. Or if I open a second Excel session and run the macro, then it will open the linked workbook, but in the first excel session if that makes sense.

I created the code using the Macro Recorder. While recording, when I click on the cell with the hyperlink it will open, but not after Ive finished and tried to run the macro. Please help.

Sub Macro1()
'
' Macro1 Macro
Range("B7").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub

Perhaps you'll have more luck with this method:

Code:

---

ThisWorkbook.FollowHyperlink (ThisWorkbook.Sheets("Sheet1").Range("B7").Value)

---

This presumes the value in B7 is actually a text string representation of the file's location. Something like: C:\MyDocuments\MyExcelFile.xls

If that is not the case and the Hyperlink is hidden in the cell's formatting (CTRL+K hyperlink) then maybe this:

Code:

---

ThisWorkbook.FollowHyperlink (ThisWorkbook.Sheets("Sheet1").Range("B7").Hyperlinks(1).Address)

---

Thanks for taking a look. I tried both variatioins and both returned a Run-time error '9': Subscript out of range.

SUBSCRIPT OUT OF RANGE errors typically refer to something being named incorrectly. My example listed the hyperlink being in cell B7 of a sheet called "Sheet1". You would naturally need to edit that to the actual name of your worksheet.

Oops, I changed the cell reference, but not the sheet name. It did run after I made the edit, but after clicking OK on the "trustworthy" dialog box, it did the same things as before.

I did find a glimmer of hope. While in the VB editor, if I click the pause/break button, then hit resume, the macro will run just fine. It only work if I run it in VB, I tried to map the macro to a button after it was working, but outside of VB it behaves the same as before. Have you ever heard of anything like that? I did some searching, but couldn't find a conclusive answer.