CODEWITHSUNDEEP
vbaexcel-2010
Binnnnn5
Binnnnn5

Reputation: 187

VBA: add a hyperlink with inputted variable to a selected cell

I want to add a hyperlink with a cell destination to a selected cell.
For example, first select range("H3"), then came out an input chatbox, I input "2", the VBA program will insert a hyperlink that links to cell "H2" to cell "H3".
I have tried the code as follows: 

Sub test1()
Dim myValue As Variant
myValue = InputBox("Input the cell that you want to link to!", "Please input", H2)
ActiveSheet.Range.Hyperlinks.Add Anchor:=Selection, Address:=Sheets("Selected_Formated").Range("F" & myValue.Value)
End Sub

or

Sub test2()
Dim myValue As Variant
myValue = InputBox("Input the cell that you want to link to!", "Please input", H2)
ActiveSheet.Range.Hyperlinks.Add Anchor:=Range("H3"), SubAddress:="Selected_Formated!" & myValue.Value & ""
End Sub

However, both of the 2 codes reported error like "Require object".

I want to know how to use an inputted variable in this "Hyperlinks.Add" function?
Thank you~

Upvotes: 0

Views: 2038

Answers (2)

R35P3K7
R35P3K7

Reputation: 96

In your example H2 was not quoted as string, so VBA thought it is variable. See my example below it should do what you want, but you can only type in address and you can type in address between sheets in current workbook, but you cannot reference like this to another workbook.

Also record macro might help you if you want to do something a bit different. Working code:

Dim xTargetRange As Range:   Set xTargetRange = Selection 'here you could create function out of this or something
Dim sAnswer As String 'output needs to be string (or variant)

'dont forget to include name of sheet for between sheet referencing - when you dont include it it refers only to cells in current sheet.
sAnswer = InputBox("Input the cell that you want to link to!", "Please input", "'" & xTargetRange.Parent.Name & "'!H2") 

'macro record showed that SubAddress is used for referencing within workbook (address is propably used for using URL to reference webpages etc.).
xTargetRange.Parent.Hyperlinks.Add Anchor:=xTargetRange, Address:="", SubAddress:=sAnswer, TextToDisplay:=sAnswer

Upvotes: 1

Karan
Karan

Reputation: 51

What you want can be achieved by either of the two types of inputbox in VBA.

1: Application.inputbox()

   Sub test1()

 Dim myValue As Range

    Set myValue = Application.InputBox("Input the cell that you want to link to!", Title:="Please input", Default:="H2", Type:=8)
    ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=myValue.Address

End Sub

2: Inputbox()

Sub test2()

 Dim myValue As Range, i As Integer

    i = InputBox("Input the cell that you want to link to!", "Please input", 2)
    Set myValue = ActiveSheet.Cells(i, ActiveCell.Column)
    ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=myValue.Address

End Sub

  1. Use Applcation.inputbox and set it's type=8 to return a range.

  2. You need to use 'Set' when assigning a range to myvalue.

Upvotes: 1

Related Questions