Ask Experts Questions for FREE Help !
Ask
    jdutle's Avatar
    jdutle Posts: 4, Reputation: 1
    New Member
     
    #1

    Dec 9, 2015, 02:31 PM
    Excel Macro Hyperlink Not Working
    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
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    Dec 11, 2015, 06:17 PM
    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)
    jdutle's Avatar
    jdutle Posts: 4, Reputation: 1
    New Member
     
    #3

    Dec 15, 2015, 03:29 PM
    Thanks for taking a look. I tried both variatioins and both returned a Run-time error '9': Subscript out of range.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #4

    Dec 17, 2015, 11:01 PM
    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.
    jdutle's Avatar
    jdutle Posts: 4, Reputation: 1
    New Member
     
    #5

    Dec 18, 2015, 07:52 AM
    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.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Excel hyperlink in new window? [ 8 Answers ]

Any idea how to edit an hyperlink on Excel to open up in a new window? Thanks.

Hyperlink in excel [ 3 Answers ]

Is it possible to create a hyperlink that stays on the current page even if the worksheet tab is renamed?

If statement in excel macro not working correctly [ 1 Answers ]

I tried to insert the following code into an Excel macro If Range("m1") = "" Then MsgBox ("Number of Units is blank") End If I am doing something wrong, because I get the msgbox whether cell m1 is empty or not. Can anyone help me with the correct syntax?

Hyperlink between two excel pages [ 2 Answers ]

How do I link 2 excel pages in the same document?

Hyperlink in Excel [ 1 Answers ]

Dear Ask me, Could you please tell me how to create a hyperlink In Excel so that it opens a specific word in a Word Document? Thanks, Cris


View more questions Search