Display text link to open specific PDF page from Excel

I found a VBA code online that opens up an internal (shared drive) PDF document page in IE (e.g. goes to page 8 of PDF file). I would like to display text in the cell for a user to click (e.g. "Click here to view").

Problem: The cell currently displays '0' and I have to go to the function bar and hit [Enter] to execute.

Excel Version: 2003

Function call:

=GoToPDFpage("S:\...x_2011.pdf",8)

VBA Code:

Function GoToPDFpage(Fname As String, pg As Integer)
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Navigate Fname & "#page=" & pg
.Visible = True
End With
End Function

:EDIT:

I was able to display text, but it's still not a link like I wanted.

="Click to view" & GoT开发者_JAVA百科oPDFpage("S:\...x_2011.pdf",8)

Thank you for your help.

---

If you dont have a high complex workbook/worksheet you could try the following:

Turn the "Click to view" cell into a Hyperlink with following characteristics.

• Make it point to itself
• The text inside the cell must always be the string Page= plus the number that you what the pdf to open in. Eg.: Page=8

Then go to the workseet module and paste the following code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    If Left(ActiveCell.Value, 4) = "Page" Then

        GoToPDFpage Range("A1").Value, Mid(ActiveCell.Value, 6)
        'This code asumes that the file address is writen in the cell A1

    End If
    '
End Sub
'

The above written code will trigger every time you run a hyperlink in the worksheet.
As the hyperlink always point to itself, the "Activecell.Value" will always have the page number that you want to open.

I'm assuming that you can put the file address in the cell A1. You could modify this portion to point to any other cell. (including: The cell to the right of the current hyperlink, etc).

This might not be the best option, but if you only need a quick feature in a couple of cells, it might be enough.

Hope it helps !

---

EDIT: To make each HLink reference to itself, you can select all the cells where you have the links and then run this procedure:

Sub RefHLink()
    Dim xCell As Range

    For Each xCell In Selection

        ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:="", SubAddress:= _
        xCell.Address, ScreenTip:="Click Here", TextToDisplay:="Page="
    Next xCell

End Sub

---

how about letting excel write a batch file then running it?

*edit paths to pdf and AcroRd32.exe

Sub batfile()
  Dim retVal
  filePath = "path\pdf.bat"
  pg = 2
  Open filePath For Output As #1
  Print #1, "Start /Max /w " & Chr(34) & "Current E-book" & Chr(34) & " " & Chr(34) & "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe" & Chr(34) & " /a " & Chr(34) & "page=" & pg & Chr(34) & "  " & Chr(34) & "H:\Documents\RPG\Dragonlance\New folder\Sample File.pdf" & Chr(34) & ""
  Close #1
  retVal = Shell(strFilePath)
End Sub

---

Try Menu->Data->Data Validation. In the 2nd tab you can write your message.