VBA If then hyperlink

[srosk]

I would like to create code.. If sheet VDR exists, then update cells D8:D11 with a hyperlink to sheet VDR. If it does not exist, put a value of N/A. Here is code for the hyperlink. I need to do this for 4 sep sheets and update 4 different ranges of cells with the hyperlink or N/A value.

[COLOR=#1f497d][FONT=Calibri][SIZE=3]ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",SubAddress:= _[/SIZE][/FONT][/COLOR]

[COLOR=#1f497d][FONT=Calibri][SIZE=3]"'VDR'!A1", TextToDisplay:= _[/SIZE][/FONT][/COLOR]

[COLOR=#1f497d][FONT=Calibri][SIZE=3]"Review impacted participants"[/SIZE][/FONT][/COLOR]

Basically, I want it t look like this... (and work)

    If Evaluate("ISREF('VDR'!A1)") = True Then
[COLOR=#1f497d][FONT=Calibri][SIZE=3]Range("D8:D11").Value = [/SIZE][SIZE=3]ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",SubAddress:= _[/SIZE][/FONT][/COLOR]

[COLOR=#1f497d][FONT=Calibri][SIZE=3]"'VDR'!A1", TextToDisplay:= _[/SIZE][/FONT][/COLOR]

[COLOR=#1f497d][FONT=Calibri][SIZE=3]"Review impacted participants"   [/SIZE][/FONT][/COLOR]


Else: Range("D8:D11").Value = "N/A"
              End If

I imagine I need to make a few changes to how hyperlinks create. Any help is appreciated!! Ty!!

 

[srosk]

I tested with:

=HYPERLINK("VDR!A1","Review impacted participants")

The sheet exists, but errors: Cannot open the specified file. The other issue with this code is that I would like to be able to copy/paste special and only have the link there.. and possibly remove the the formula.

 

[JoeMo]

Untested, but this should do it.

Sub srosk()
Dim n As Variant
With ActiveWorkbook
    On Error Resume Next
    n = Len(Sheets("VDR").Name)
    If Not IsEmpty(n) Then
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'VDR'!A1", TextToDisplay:= _
        "Review impacted participants"
    Else
        Range("D8:D11").Value = "N/A"
    End If
    On Error GoTo 0
End With
End Sub

 

[srosk]

Thanks Joe. I tested this, and one issue is that it doesn't update D8:D11 with the link, it only updates the active sheet.

Any idea? ty!

 

[JoeMo]

Thanks Joe. I tested this, and one issue is that it doesn't update D8:D11 with the link, it only updates the active sheet.

Any idea? ty!

Change the Anchor to the address you want the link in.

 

[srosk]

Change the Anchor to the address you want the link in.

So, replace the word selection with...?

Range("D8:D11")

 

[srosk]

So I put the range in there, overwriting the selection. What works good, is that all 4 cells are a link. The only problem, is that the text 'Review impacted participants' only displays on D8. Is there a way to show the text on all cells?

 

[JoeMo]

So I put the range in there, overwriting the selection. What works good, is that all 4 cells are a link. The only problem, is that the text 'Review impacted participants' only displays on D8. Is there a way to show the text on all cells?

Like this:

Sub srosk()
Dim N As Variant
With ActiveWorkbook
    On Error Resume Next
    N = Len(Sheets("VDR").Name)
    If Not IsEmpty(N) Then
        ActiveSheet.Hyperlinks.Add Anchor:=Range("D8"), Address:="", SubAddress:= _
        "'VDR'!A1", TextToDisplay:= _
        "Review impacted participants"
        Range("D8:D11").FillDown
    Else
        Range("D8:D11").Value = "N/A"
    End If
    On Error GoTo 0
End With
End Sub

 

[srosk]

Thanks Joe! That worked great.. thank you!! Since I have 4 sheets, trying to apply the code w/o copying the code 4x. Here's a sample of what I've modified the code to read.

Sub Report6SummaryPage(p)
Dim n As Variant
Dim z As Variant
Dim r As Variant
Dim m As Variant

If p = 1 Then z = "D8:D11" And r = "VDR" And m = "'VDR'!A1"
If p = 2 Then z = "D12:D22" And r = "DDR" And m = "'DDR'!A1"
With ActiveWorkbook
    On Error Resume Next
    n = Len(Sheets(r).Name)
    
    If Not IsEmpty(n) Then
        ActiveSheet.Hyperlinks.Add Anchor:=Range("D8"), Address:="", SubAddress:= _
        m, TextToDisplay:= _
        "Review impacted participants"
        Range(z).FillDown
        Else
        Range(z).ClearFormats
        Range(z).Value = "N/A"
        Range(z).Select
        With Selection.Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
            .Value = "N/A"
        End With
    End If
    On Error GoTo 0
End With
End Sub

Earlier in my macro I will call the script with... Report6SummaryPage(1),Report6SummaryPage(2), etc. I receive an error: type mismatch. Any way to clean this up? Ty!!

 

[JoeMo]

Hard to diagnose w/o seeing the code and you pointing to the line that throws the error.