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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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

J
  • Question Question
Using VBA to insert Hyperlinks
Replies
1
Views
652
Jeffrey Mahoney
Jeffrey Mahoney
E
  • Question Question
VBA - Create a new sheet + hyperlink name
Replies
3
Views
400
excelnewbie990
E
S
  • Question Question
Workbook Index of Sheet name Hyperlinks + additional information
Replies
1
Views
304
stuartgb100
S
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
202
ipbr21054
I
O
  • Solved
ActiveX checkbox that changes color
2
Replies
15
Views
1K
John_w
J

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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