VBA to Open a Hyperlink based on Specific Text in another cell

Question

I am trying to create a VBA to look in cell E for Monday, and then open the Hyperlink that is in the cell next to it in D.

I have tried the below But it is not working. Nothing is happening when I run it. The files are on my network that the hyperlinks are attached to.

enter image description here

Sub OpenFile()
         Dim oH As Hyperlink
    For Each oH In Hyperlinks
          If oH.Range.Cells(1, 2).Text = "Monday" Then If Dir(oH.Address) > "" Then Workbooks.Open oH.Address
    Next
End Sub

score 0 · Answer 1 · answered Apr 16 '21 at 01:43

0

Sub OpenFile()
    Dim i As Long
    Dim rowLast As Long
    Dim filePath As String
    'Dim loopLink As Hyperlink 'Uncomment if there can be multiple links in column D cells
    
    With Sheet1 'Change the worksheet name to the correct one
        rowLast = .Range("E" & .Rows.Count).End(xlUp).Row
    
        For i = 2 To rowLast 'Loop from 2nd row to the last row
            If .Cells(i, 5).Value = "Monday" Then 'Validation if cell in Column E is "Monday"
                With .Cells(i, 4)
                    'Single link version, change to below version for multiple links
                    If .Hyperlinks.Count = 1 Then
                        filePath = .Hyperlinks(1).Address
                        If Dir(filePath) <> vbNullString Then Application.Workbooks.Open filePath
                    End If
                    
                    'Multiple links version
                    'For Each loopLink In .Hyperlinks
                    '   filePath = loopLink.Address
                    '   If Dir(filePath) <> vbNullString Then Application.Workbooks.Open filePath
                    'Next loopLink
                End With
            End If
        Next i
    End With
End Sub

answered Apr 16 '21 at 01:43

Raymond Wu

3,357
2
7
20

Getting a runtime 424 error for rowLast = .Range("E" & .Rows.Count).End(xlUp).Row – mlott Apr 16 '21 at 18:39
@mlott - Is the worksheet object you are referring to correct? If so, can you print out whats .rows.count? Alternatively, if the last row is a constant number then you can simply assign the value directly. – Raymond Wu Apr 16 '21 at 22:47
I am still needing help on this. I cannot make this work. I want to click on a button control for the specific day example "Monday" and it open all hyperlinks listed in column D that have "Monday" listed in column E. – mlott Jul 19 '21 at 14:12
@mlott Please be more specific, what is not working? Is there an error? And if so, which line and what is the error description. Also update your question with your code. – Raymond Wu Jul 19 '21 at 14:31
It won't let me add my macro here as it says its too long. What happens is I click run and nothing happens. It worked fine and then just stopped. No error messages or nothing. I tried the step through and nothing stopped. – mlott Jul 22 '21 at 19:35
@mlott Too long to include in the question? That sounds crazy, this sounds like a new issue so try to trim down the code and post a new question. If this is a hyperlink to a file then chances are you have exceeded the [maximum length](https://learn.microsoft.com/en-us/windows/win32/fileio/maximum-file-path-limitation?tabs=cmd) – Raymond Wu Jul 22 '21 at 23:40

VBA to Open a Hyperlink based on Specific Text in another cell

1 Answers1