Run-time error "1004: Application-defined or object-defined error" while adding hyperlink with file open dialog

Question

The general thrust of what I'm trying to do here is to write a VBA macro for an excel form that uses the File Pick dialog ("Application.FileDialog(msoFileDialogOpen)", specifically) to create a hyperlink in the active cell. I'm doing it this way so I can use parameters like ".InitialFileName" in order to bring users right down to the folder where I mean for them to link from. The intended audience here isn't 100% computer-savvy, and I don't expect even mediocre things from them.

Trying to do a lot of the "heavy lifting" for them, so the results of this are consistent.

Anyway, the code:

Sub InsHyperlink()
Dim ws As Worksheet

Dim rng As Range
Set rng = ActiveCell

Set ws = Sheets("Log")

Dim fd As FileDialog
Dim selectedPaths() As String
Dim I As Integer

Set fd = Application.FileDialog(msoFileDialogOpen)

With fd
    .AllowMultiSelect = False
    .Title = "Select your File(s)"
    .InitialFileName = "\\CMI-FILE\tsk_SeamSpotWeld_TestLog _        
    \CompletedTestForms\"
If .Show = -1 Then
    ReDim selectedPaths(.SelectedItems.Count - 1)
    For I = 0 To .SelectedItems.Count - 1
        selectedPaths(I) = .SelectedItems(I + 1)
        With ws
        .Hyperlinks.Add Anchor:=rng, Address:=selectedPaths(I), _
        TextToDisplay:="File Link"
        End With
        Next I
    End If
End With

Set fd = Nothing

End Sub

The problem of course is the error that starts this question,

1004, application defined or object-defined error

While testing this on my own machine, this didn't crop up at all. It was only when I moved it to the machine that would be performing the operation that I saw this.

I've since tried two different versions of the above code, both of which run fine on my machine, but error out on the target machine in the same way. Both times it points to the line beginning with .Hyperlinks.Add Anchor:=rng

I've read through a lot of error threads starting with this, but I get the sense that the error itself is very general. Most of them suggest defining the range as something more specific.

And of course, the problem is that I ~need~ the reference to be dynamic- the address of the cell the user currently has highlighted, so... that solution really isn't workable- unless there's something I'm not understanding here.

Answer 1

I have tried this, it works quite ok for me:

Option Explicit

Sub InsHyperlink()

    Dim ws As Worksheet
    Set ws = Worksheets(1)

    Dim fd As FileDialog
    Dim selectedPaths() As String
    Dim I As Long

    Set fd = Application.FileDialog(msoFileDialogOpen)
    With fd
        .AllowMultiSelect = True
        .Title = "Select your File(s)"
        If .Show = -1 Then
            ReDim selectedPaths(.SelectedItems.Count - 1)
            For I = 0 To .SelectedItems.Count - 1
                selectedPaths(I) = .SelectedItems(I + 1)
                With ws
                    .Hyperlinks.Add Anchor:=ws.Cells(I + 1, 1), _ 
                               Address:=selectedPaths(I), TextToDisplay:="File Link"
                End With
            Next I
        End If
    End With
End Sub

Take a look at the rng. I am using. ws.Cells, to make sure that it gives different ranges every time.

Answer 2

I've tested the following and it also works fine, as Vityata's answer, but instead of using ws.Cells, I simply offset the rng one row down each iteration of the loop:

Option Explicit

Sub InsHyperlink()
Dim ws As Worksheet: Set ws = Sheets("Log")
Dim rng As Range: Set rng = ActiveCell
Dim fd As FileDialog
Dim I As Long

Set fd = Application.FileDialog(msoFileDialogOpen)

With fd
    .AllowMultiSelect = True
    .Title = "Select your File(s)"
    .InitialFileName = "\\CMI-FILE\tsk_SeamSpotWeld_TestLog\CompletedTestForms\"
    If .Show = -1 Then
        For I = 0 To .SelectedItems.Count - 1
            ws.Hyperlinks.Add Anchor:=rng, Address:=.SelectedItems(I + 1), TextToDisplay:="File Link"
            Set rng = rng.Offset(1, 0)
        Next I
    End If
End With
Set fd = Nothing
End Sub