How to Use Input Data Results in Hyperlink Code

chickenchester

New Member
Joined
Jun 30, 2017
Messages
16
Hello,

I created the top half of the coding below to trigger an input box. What I'm trying to do is to use that result in the second half of the coding in the red text below. The box successfully appears and let's me enter the data then disappears. Nothing else happens. I have tried quotations around it and still nothing. Any help is appreciated. Thank you.

Sub example()
Dim result As String

InputData = Application.InputBox("Enter the tab name to add the hyperlink to.", "Enter Tab Name", "Enter Tab Name") 'The variable is assigned the value entered in the InputBox
If result <> "" Then 'If the value anything but "" the result is displayed
MsgBox result

Range("B" & indexRow).Select
ActiveSheet.hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"InputData!A1", ScreenTip:="Select this link to access the tab.", TextToDisplay:="QTS"

Range("C" & indexRow).Select
ActiveSheet.hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'UAT Test Cases Sample (2)'!A1", ScreenTip:="Select this link to access the tab.", TextToDisplay:="UAT"
End If
End Sub
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hello,

First I suggest you to declare your variable.

Second, try this syntax
Code:
InputData & "!A1"
 
Upvote 0
Hello,

First I suggest you to declare your variable.

Second, try this syntax
Code:
InputData & "!A1"

Hello. I am newer to VBA, just FYI. Here is what I added and it still doesn't work:

Sub example()
Dim result As String
Dim InputData As String


InputData = Application.InputBox("Enter the tab name to add the hyperlink to.", "Enter Tab Name", "Enter Tab Name") 'The variable is assigned the value entered in the InputBox
If result <> "" Then 'If the value anything but "" the result is displayed
MsgBox result

InputData = result

Range("B" & indexRow).Select
ActiveSheet.hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"InputData & " '!A1'", ScreenTip:="Select this link to access the tab.", TextToDisplay:="QTS"

Range("C" & indexRow).Select
ActiveSheet.hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'UAT Test Cases Sample (2)'!A1", ScreenTip:="Select this link to access the tab.", TextToDisplay:="UAT"
End If
End Sub
 
Upvote 0
This is where I am now. It finds the cell and enters QTS, but the link does not work. For some reason, it isn't linking up with the results from the question.

Sub hyperlink()
Dim sh1 As Worksheet, newSh As String
Dim rngFinalRow As Range
Dim indexRow As Integer
Dim result As String

Const MAX_ROWCOUNT = 400 ' Change this to increase the max sheet count

Set sh1 = Sheets("TRM Summary")

Application.ScreenUpdating = False ' This stops it from flashing as much between screens

Set rngFinalRow = Range("A3")
indexRow = 1

Do Until indexRow >= MAX_ROWCOUNT Or rngFinalRow.Text = "finalrow"
Set rngFinalRow = rngFinalRow.Offset(1, 0)
indexRow = indexRow + 1
Loop

If indexRow < MAX_ROWCOUNT Then
indexRow = rngFinalRow.Row - 2

result = InputBox("Enter the QTS tab name as it appears that you would like a hyperlink made for.", "Add Hyperlink", "Enter Tab Name") 'The variable is assigned the value entered in the InputBox

If result <> "" Then 'If the value anything but "" the result is displayed

Range("B" & indexRow).Select
ActiveSheet.hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
result & "!A1", ScreenTip:="Select this link to access the tab.", TextToDisplay:="QTS"

End If

MsgBox "If the tab name is modified, the hyperlink will no longer work."
Else
MsgBox "Unable to find the final row. Count exceeds " & MAX_ROWCOUNT - 1
End If

End Sub
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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