Hyperlinking in Excel

[naraujo]

I'm trying to link a folder to a picture in excel. I know how to link a program and with the hyperlink command but it will not let me link a folder. I want to folder to open when I click on the link. Does anybody know how I may be able to do this? Any ideas would be appreciated. Thanks

 

[SkipVought]

hi, naraujo,

A hyperlink cannot get you to a Folder. But you can, using VBA code.

You will need to do some things in preparation..
1. Name the CELL that you wanted to have the hyperlink in. My example has named it "FolderLink"

2. On a separate sheet, in cell A1, enter the path to the folder that you want to be displayed. In my example, the folder is _Download on the D: drive root, so I would enter it as...

D:\_Download

3. Name that cell "MyFolder"

The following code needs to be in the Worksheet Object code for the worksheet for which you named the cell. There is a general explanation below the code of how to find where to put event driven code, because, whenever you select this special cell, a window will open for the folder that you designated

Sub ShowFolder()
    Dim sFileName
    sFileName = Application.GetSaveAsFilename(InitialFilename:=Range("MyFolder"), fileFilter:="All Files (*.*), *.*")
    If sFileName <> False Then
        MsgBox &quot;Open &quot; & sFileName
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    For Each t In Target
        Set isect = Intersect(t, Range(&quot;FolderLink&quot;))
        If Not isect Is Nothing Then
            ShowFolder
        End If
        
    Next
End Sub

Hope this helps

There are various Chart, Worksheet and Workbook Events in Excel 97+. There is a skeleton procedure already set up for each event. Here's how to find these Event Procedure Skeletons...

1. alt+F11 - activates the Visual BASIC Editor

2. ctr+R - activates the Project Explorer

3. In the Project Explorer, Open Microsoft Excel Objects

4. Right click the object of choice (Chart, Worksheet or Workbook) and select View Code from the Pop-Up Menu

5. Select the Object DropDown in the upper left-hand corner of the Code Window (F7 activates Code Window)
6. From the Object DropDown, select either Chart, Workbook or Worksheet Object.

7. Select the Procedure DropDown in the upper right-hand corner of the Code Window
8. From the Procedure DropDown, select the Event that you want to control.

9. In the Event Skeleton in the Code Window, enter your code.
Skip,
metzgsk@voughtaircraft.com

 

[naraujo]

Thank you so much.

-Nelson

 

[HotEye]

Hi all,

I am generating Hyperlinks in Sheet 1 of a Excel 2000 workbook, sheet 2 is used for manual entry, sheets 3 to Workbook.Count are used for stock logging.


For intSheet = 3 To Worksheets.Count
Rem Copy the partnumbers from the label into the spreadsheets
Worksheets(intSheet).Activate
strPN = Worksheets(intSheet).Name
Worksheets(intSheet).Range("C1&quot = strPN
RecalcSheet

Worksheets(1).Activate
Range("A" + Format(intSheet + 3)).Select
Range("A" + Format(intSheet + 3)) = strPN

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" + strPN + "'!B5", TextToDisplay:=strPN
Next


The hyperlink puts you on cell B5 which is used for quantity entry, so entry can start as soon as you click the link.

This all works well the first time, but if you click on a hyperlink a second time, it takes you to the right cell, but all keyboard input (except return) is ignored. This is cleared by mouse clicking on the worksheet.

Any fixes?

Thanks

 

[HotEye]

Oops, sorry, that was supposed to be a new question.