VBA If then hyperlink

S

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
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.

Code: 
[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)

Code: 
    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!!
 
Last edited:
Sort by date Sort by votes
I created a button to just execute this sub on the summary page for testing purposes - so I am not using any other code. For whatever reason, it does not highlight any code or indicate the line in question. However.. I know the error occurs as a result of me adding code for the If statement. Your code was good. When I had your code look for z/r/m, that worked.. but the IF statement an P value threw it off. The following works... but I would like to have it run or p = 1 2 3 and 4. I tried in the earlier code but it doesn't work

Code: 
Sub Report6SummaryPage()
Dim n As Variant
Dim z As Variant
Dim r As Variant
Dim m As Variant


'If p = 1 Then Set z = "D8:D11" And r = "VDR" And m = "'VDR'!A1"
'If p = 2 Then Set z = "D12:D22" And r = "DDR" And m = "'DDR'!A1"
With ActiveWorkbook
    On Error Resume Next
    z = "D8:D11"
    r = "VDR"
    m = "'VDR'!A1"
    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 - There is no data to review."
        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
 
Last edited:
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
p needs to be passed to your sub from some other routine, otherwise the routine you posted has no knowledge of what the value of p is. Where is the code that passes this variable?
 
Upvote 0
Originally it was Report6SummaryPage(p) and earlier in my macro, I pasted Report6SummaryPage(1) which is executable. Same results. For testing purposes, can't I just "set p=1" earlier in the sub to test and execute? That's what I did earlier.. but didn't work lol!
 
Upvote 0
srosk said:
Originally it was Report6SummaryPage(p) and earlier in my macro, I pasted Report6SummaryPage(1) which is executable. Same results. For testing purposes, can't I just "set p=1" earlier in the sub to test and execute? That's what I did earlier.. but didn't work lol!
Click to expand...
Yes, you can set it in your sub but in that case set the Sub line to this:

Sub Report6SummaryPage()

and uncomment the If p=1 .... line(s)
 
Last edited:
Upvote 0
Maybe I am being dense.. lol! but, now it says argument not optional.. with no line #error

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

With ActiveWorkbook
    On Error Resume Next
    p = 1
    
    If p = 1 Then z = "D8:D11" And r = "VDR" And m = "'VDR'!A1" And 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 - There is no data to review."
        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
 
Upvote 0
As I said in post #14 , you must remove the argument (p) from
Report6SummaryPage(p)
 
Upvote 0
JoeMo said:
As I said in post #14 , you must remove the argument (p) from
Report6SummaryPage(p)
Click to expand...

I see.. so I just did that with the following code:

Code: 
Sub Report6SummaryPage()Dim n As Variant
Dim z As Variant
Dim r As Variant
Dim m As Variant


With ActiveWorkbook
    On Error Resume Next
    p = 1
    
    If p = 1 Then z = "D8:D11" And r = "VDR" And m = "'VDR'!A1" And 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 - There is no data to review."
        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

It did not update D8:D11. I then tried...

Code: 
Sub Report6SummaryPage()
Dim n As Variant
Dim z As Variant
Dim r As Variant
Dim m As Variant


With ActiveWorkbook
    On Error Resume Next
 
     z = "D8:D11" And r = "VDR" And m = "'VDR'!A1" And 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 - There is no data to review."
        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

Which also did not work. No errors.. just didn't update any cells. The following does work...

Code: 
Sub Report6SummaryPage()
Dim n As Variant
Dim z As Variant
Dim r As Variant
Dim m As Variant


With ActiveWorkbook
    On Error Resume Next
 
     z = "D8:D11"
     r = "VDR"
     m = "'VDR'!A1"
     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 - There is no data to review."
        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

However.. Ideally.. I need this to run for 4 different sheets. Is the solution to call 4 different subs? Or is there a better way to do this?

Thanks Joe!
 
Last edited:
Upvote 0
The solution is to get ALL the code right. This:

z = "D8:D11" And r = "VDR" And m = "'VDR'!A1" And n = Len(Sheets(r).Name)

is not proper syntax as you discovered.

Put the p back in so you can call the

Sub Report6SummaryPage(p)

Using 1, 2 ,3, or 4 and an If p = ... statement to set the right parameters for each sheet.
 
Last edited:
Upvote 0
Right.. that's what I was getting at with #9 , but was struggling to get it to work. I re-wrote it as follows:

Code: 
Sub ZZ()

srosk (1)


End Sub


Sub srosk(p)
Dim n As Variant
Dim b As Variant
Dim c As Variant
Dim q As Variant
Dim r As Variant




If p = 1 Then b = "D8" And c = "D8:D11" And q = "VDR" And r = "'VDR'!A1"


With ActiveWorkbook
    On Error Resume Next
    n = Len(Sheets(q).Name)
    If Not IsEmpty(n) Then
        ActiveSheet.Hyperlinks.Add Anchor:=Range(b), Address:="", SubAddress:= _
        r, TextToDisplay:= _
        "Review impacted participants"
        Range(c).FillDown
    Else
        Range(c).Value = "N/A"
    End If
    On Error GoTo 0
End With
End Sub

I was getting along well with the code, until I defined C / Q / R... type mismatch. I tried to define them as variants as well.. no dice. Why is it that the variables work fine without the IF statement.. but once that's added, it breaks the script. Ugh!



JoeMo said:
The solution is to get ALL the code right. This:

z = "D8:D11" And r = "VDR" And m = "'VDR'!A1" And n = Len(Sheets(r).Name)

is not proper syntax as you discovered.

Put the p back in so you can call the

Sub Report6SummaryPage(p)

Using 1, 2 ,3, or 4 and an If p = ... statement to set the right parameters for each sheet.
Click to expand...
 
Last edited:
Upvote 0
As I said in post #18 , this line:
Code: 
If p = 1 Then b = "D8" And c = "D8:D11" And q = "VDR" And r = "'VDR'!A1"
is NOT PROPER SYNTAX.
Try this:
Code: 
If p = 1 Then 
      b = "D8"
      c = "D8:D11"
      q = "VDR"
      r = "'VDR'!A1"
End If
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
VBA Help: Retaining Hyperlinks
Replies
0
Views
476
NeoSez
N
Anigito
  • Question
Get Webpage Title Text by URL Generated in MS Excel
Replies
1
Views
415
Anigito
Anigito
C
  • Solved
VBA code to create a word document, link it to a cell and save the document using the cell value
Replies
3
Views
331
cmannp
C
J
  • Solved
Image Hyperlink Subaddress - Variable Cell Reference (VBA)
Replies
5
Views
1K
JHusk
J
F
  • Question
Adding Hyperlinks via Userform
Replies
1
Views
337
FlavorFlav
F

Forum statistics

Threads
1,214,788
Messages
6,121,575
Members
449,039
Latest member
Arbind kumar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top