Excel VB open hyperlink

September 3, 2009 at 06:29:03
Specs: win xp 5.1.2600, 3mhz/2 gig

Hi, I just need this to open the selected cell (which is a hyperlink). Please would you assist? Thank you. Lee
Private Sub textbox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Dim SearchBox1 As String

If KeyCode = 13 Then

For Each sht In ActiveWorkbook.Sheets
If sht.Name = "Master" Then GoTo nxtSht
With Sheets(sht.Name)
Set c = Sheets(sht.Name).Cells.Find(What:=Worksheets("Master").TextBox1, _
After:=ActiveCell, LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not c Is Nothing Then
Sheets(sht.Name).Activate
ActiveSheet.Range(c.Address).Select
ActiveCell.Previous.Select
'// The line below should run the above selected cell which is a hyperlink. NOT WORKING.
'Worksheets(sht.Name).ActiveCell.Hyperlinks(1).Follow
'ActiveCell.Run
Exit Sub
End If
End With
nxtSht:
Next
End If
End Sub


See More: Excel VB open hyperlink

Report •


#1
September 3, 2009 at 08:36:55

"Not Working" isn't a very descriptive account of what is happening.

Does the code throw up an error?
If so, what does it say?
Is the link addess not found?
etc.

I assume you have single stepped through the code using F8 to ensure that the code is actually getting to that line without an error.

All that said, try replacing your line with:

Selection.Hyperlinks(1).Follow

Another option that involves selecting 1 less cell is this:

If Not c Is Nothing Then
   Sheets(sht.Name).Activate
   ActiveSheet.Range(c.Address).Offset(0, -1).Select
   Selection.Hyperlinks(1).Follow
   Exit Sub
End If


Report •

#2
September 4, 2009 at 10:56:40

DerbyDad03:
   Sheets(sht.Name).Activate
   ActiveSheet.Range(c.Address).Offset(0, -1).Select
   Selection.Hyperlinks(1).Follow
I should probably mention this can be shortened down to:
c.Offset(0, -1).Hyperlinks(1).Follow


Report •

#3
September 4, 2009 at 12:33:55

I was taking it one step at a time. :-)

Actually, I assumed there were other reasons he was activating the sheet, but your method is the most efficient if all that needs to be done is to follow the link.

Nice! You got my vote.


Report •

Related Solutions

#4
September 14, 2009 at 03:17:23

Hi guys, thank you for your replies. I would like to apologise for not replying for ages but I was in South France on a fantastic holiday. (I highly recommend it!)

I have tried your suggestions and cant get them to work. I keep getting "run time error 9".
So, do you have any suggestions? Maybe I need to send a copy of my excel document. (Only problem is I can't send the PDF files, maybe I could send 1 as an example?)
Thank all, again, sorry for the late reply, I didn't have a computer there.
Ciao


Report •

#5
September 14, 2009 at 05:43:55

re: "South France...Didn't have a computer there."

Good for you! We all need a break every now and then.

A Runtime Error 9 is a "Subscript out of range" error, which typically means that VBA cannot find what it is looking for.

In Response 1 I mentioned this:

"I assume you have single stepped through the code using F8 to ensure that the code is actually getting to that line without an error."

It would help is we knew exactly which line causes the error so that we can troubleshoot further.


Report •

#6
September 14, 2009 at 06:09:06

Hi, argggg sorry I cant get F8 to do anything. How do I start the F8 process?

I opened my excel document, pressed 'Alt + F11" and then pressed F8..... nothing happened!

Please excuse my ignorance but I just don't know how to start it!!!

Cheers


Report •

#7
September 14, 2009 at 06:46:09

To use F8, you typically place your cursor anywhere within the code and begin pressing F8 to single step. This will highlight each line just prior to execution.

That said, there are certain types of subs that F8 doesn't work with directly. The KeyUp event is one of those.

What you can do in these cases is simply turn the sub into a "normal" routine by changing its name to something like Sub MyTest().

You may have to comment out lines like If KeyCode = 13 Then and it's associated End If, but once your turn it into a "normal" routine, it should single step fine.

BTW...You can create watches by opening the Watch window and dragging variables (e.g. sht.Name) into it to see their value as you step through the code. Further, you can create Watches on "variables" that aren't in the code to show you more detail.

For example, you could add a watch for ActiveCell.Address so you could tell which cell Excel will be working on at any given point.

Finally, there are certain variables that you can see the value of by just hovering your cursor over while single stepping. e.g. "c" in your code is one of those.


Report •

#8
September 14, 2009 at 08:27:48

Hi there.
Thanks for the tips on adding watches! They really are great.
I added one for activesheet.range(c.address).offset(0, -1) and that actually brings up the file name that I am looking for. Because of the watch I think I understand what is wrong now.

You see, the cell it points to in the end is actually a clever hyperlink that points to other cells to make it up.
An example of the cell is this: =HYPERLINK(Master!$B$1&"\"&$C$1&"\"&A7,A7)

Now on my master sheet I type which drive my PDF files are in. Then this HYPERLINK automatically detects the rest of the file’s path.

Maybe what is happening (I am guessing here) is that “activesheet.range(c.address).offset(0, -1)” gives only the file’s name and not the path.

I am sorry this is so complex. If you think this is the reason then I will just abort the mission! But this is definitely the line where it fails: “ActiveSheet.Range(c.Address).Offset(0, -1).Hyperlinks(1).Follow”

Thanks for all your help.


Report •

#9
September 14, 2009 at 11:28:44

May I ask why you are attempting this via Hyperlinks?

It looks like you are building the Hyperlink in Excel and then using VBA to follow it.

Do you really need the Hyperlink in the sheet? Even if you do, you could still build the Hyperlink in the sheet the way you are, but open the "link" by building the path within VBA and opening it from there:

Sub OpenLink_SortOf()
 myFile = Worksheets("Master").Range("B1") & "\" & Range("C1") & "\" & Range("A7")
 Workbooks.Open Filename:=myFile
End Sub


Report •

#10
September 17, 2009 at 05:37:32

DerbyDad, firstly thank you again for the "watches"! You don't know how that helps me.

Your latest script for "myFile" is great. It gets alot done. I am playing around with it and feel I am so close to cracking it. I will continue to try and WILL get it in the end (Thanks to the "Watch" function.

You are right in how I am building the hyperlink in Excel and running the VB script from that. I understand that this may be causing a problem but seeing your script helps me get a little further.

To answer your question why I am doing this via hyperlinks in excel... Please try follow what I say because I am not that good at explaining: I have a load of PDF files in C:\Example\PDF\ and that folder changes on diferent peoples computers. What I try to do is create a hyperlink list using clever hyperlinks. Then the whole purpose of the script I asked you for is like a general search function. There are a few worksheets and each worksheet has a load of PDS associated. So on my MASTER worksheet I have a search bar where I type in the numbers of the PDF i am searching for and press enter.... then I want that PDF to open.

Thanks to you I am right there on the brink and I am really enjoying this!

I will keep you posted.


Report •

#11
September 17, 2009 at 07:36:19

Hi there. GOT IT!!!!!! Thanks for all your help.
Also thanks to a mate Peter Z who helped me work out a few bumps especially towards the end. Here is the final script.
------------------------------------------
Sub textbox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Dim SearchBox1 As String

If KeyCode = 13 Then

For Each sht In ActiveWorkbook.Sheets
If sht.Name = "Master" Then GoTo nxtSht
With Sheets(sht.Name)
Set c = Sheets(sht.Name).Cells.Find(What:=Worksheets("Master").TextBox1, _
After:=ActiveCell, LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not c Is Nothing Then
Sheets(sht.Name).Activate
myfile = Worksheets("Master").Range("B1") & sht.Name & "\" & ActiveSheet.Range(c.Address).Offset(0, -1)
ActiveSheet.Range(c.Address).Offset(0, -1).Select
Shell "C:\Program Files\Adobe\Acrobat 7.0\Reader\Acrord32.exe " & myfile, vbMaximizedFocus
Exit Sub
End If
End With
nxtSht:
Next
End If
End Sub


Report •

#12
September 17, 2009 at 09:03:29

I'm glad to see that you have abandoned the use of Hyperlinks. Using Shell in this instance is a lot cleaner.

P.S. Don't take this as a reprimand, just as a tip for posting future questions…

The fact that you were opening PDF's was a key piece of information. Since your original question related to simply "following a hyperlink" that is where our efforts were focused.

Had you told us earlier that the hyperlinks were being used to open files and that the files were PDF's, we may have gotten to your answer a lot quicker.

I know that it can be hard to figure out every single detail that we might need to provide assistance and I know that you can't post everything without the question becoming too cumbersome. Perhaps a review of this thread will help you determine which key pieces of information will provide enough data to get you an answer sooner.
.


Report •


Ask Question