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

Question

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~

Karan · Accepted Answer · 2020-08-03 11:45:57Z

1

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

Use Applcation.inputbox and set it's type=8 to return a range.
You need to use 'Set' when assigning a range to myvalue.

answered Aug 3, 2020 at 11:45

Karan

513 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

R35P3K7 · Accepted Answer · 2020-08-03 11:54:21Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related