HyperLink with VBA

[Carl Clements]

Hi,

I have a spreadsheet where I would like to double click on a cell, which then opens another saved spreadsheet.

Within VBA, I understand that I can select 'Worksheet' from the drop down menu bar at the top but after that, I'm not sure if I need to select 'BeforeDoubleClick' and record a macro to open the file, or 'FollowHyperlink' and code the file directory?

I've never used these drop down menus before, so any guidance will be helpful.

Thanks,
Carl

 

[Gary McMaster]

Hi Carl,

It sounds like "BeforeDoubleClick" is what you want. If there were something in the cell, you could just hyperlink to the file you wanted to open and it would open with a single click and you wouldn't need anycode. The sample below assumes the cell does not have a hyperlink and may not work properly if it does.

Right click on one of the worksheet tabs and paste the code into the code window. You will have to change the file path/name in the code to point to files that exist on your system. As written the code is looking for double clicks in A1 or C1 on whatever sheet you associate the code to.

Hope it helps.

Gary

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Select Case Target.Address
    Case "$A$1"
        Application.Workbooks.Open ("C:\MyPath\MyFile_For_A1.xls")
    Case "$C$1"
        Application.Workbooks.Open ("C:\MyPath\MyFile_For_A1.xls")
Case Else
    MsgBox "No file associated with this cell"
    
End Select

End Sub

 

[Carl Clements]

Thanks Gary. What would be the code if I want it to apply the link to each cell in a column, rather than an indivudual cell? Would it be similar to the below:
Case Range.("S:S").Select
Application.Workbooks.Open ("C:\MyPath\MyFile_For_A1.xls")

Regards,
Carl

 

[Gary McMaster]

Directly answering your question I would probably do something like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Column = 19 Then
    'Application.Workbooks.Open ("C:\MyPath\MyFile_For_A1.xls")
End If

End Sub

If you still wanted to be selective you could still use Select Case something like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

MsgBox Target.Column ' Remove this line

Select Case Target.Column

    Case 1, 3, 5, 7, 9, 11, 13
        Application.Workbooks.Open ("C:\MyPath\MyFile_For_Odd_Columns.xls")
    Case 2, 4, 6, 8, 10, 12
        Application.Workbooks.Open ("C:\MyPath\MyFile_For_Even_Columns.xls")
    Case 19
    'Add file open for column S
        MsgBox "Column S was double clicked"

Case Else

    MsgBox "No file associated with this column"

End Select

End Sub

There are a number of other ways depending on exactly what you need. For example: does the "Target" cell intersect a certain row & column ... things like that.

Gary

 

[Carl Clements]

Coincidently, I want it to work on case 19 also (Column S), however I tried both as below, but when I double click on the cell nothing happens. Am i missing something?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 19 Then

Application.Workbooks.Open ("P:\Daily P&V\Ad hoc\Corporate Action Movement Template.xls.xls")

End If

End Sub

AND

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Column

Case 19
Application.Workbooks.Open ("P:\Daily P&V\Ad hoc\Corporate Action Movement Template.xls.xls")

Case Else

MsgBox "No file associated with this column"

End Select

 

[Gary McMaster]

What you have should either open the specified file or issue an error message stating that the file was not found.

The only thing I can think of that could be wrong is that you have placed the code in the "Double Click" event for a sheet other than the one you are working with. One other possibility is that you have other code in this project that has disabled the events.

There is also a "workbook wide event" named Workbook_SheetBeforeDoubleClick. If you paste the code in that event it should work for every sheet in the workbook.

In the "Project" browser (left side of IDE), double click on "ThisWorkbook" and look for the event in there.

First thing to try is double click a cell in column S on all the other sheets in the workbook. One of them should respond with something.

Gary