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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Roxxien

Active Member
Joined
Jul 5, 2017
Messages
289
Hello,

First I suggest you to declare your variable.

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

chickenchester

New Member
Joined
Jun 30, 2017
Messages
16
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
 

chickenchester

New Member
Joined
Jun 30, 2017
Messages
16
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,256
Messages
5,527,656
Members
409,780
Latest member
Sudheer121

This Week's Hot Topics

Top