Prompt to Select Cell

[Guest]

I need a Macro command line to prompt the user to select a cell (in this case
a hyperlink), and then continue with my program.

(User starts Macro, Macro prompts for cell/link selection, Macro uses the
link to continue)

No luck finding this in the Posts, Thanks in advance for any ideas.

 

[RB Smissaert]

Try something like this:

Sub test()

Dim rng As Range

On Error Resume Next
Set rng = Application.InputBox(Prompt:="Click in a cell with a
hyperlink.", _
Title:="picking a hyperlink", _
Type:=8)

If rng Is Nothing Then
On Error GoTo 0
Exit Sub
End If

On Error GoTo 0

MsgBox rng.Hyperlinks(1).Address, , "selected hyperlink"

End Sub


RBS

 

[Guest]

Thank You RBS
It mostly worked... Please note I am new at this...

The selection works, but then I can't step back into my program. The rest of
the steps worked previously with a set cell name.

This is what I have: It kicks out on"Selection.Hyperlinks..." and if I
delete that, then it kicks out on the next line, etc.

Dim rng As Range

On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select Project Link", _
Title:="picking a hyperlink", _
Type:=8)
If rng Is Nothing Then
On Error GoTo 0
Exit Sub
End If
On Error GoTo 0
MsgBox rng.Hyperlinks(1).Address, , "selected hyperlink"

Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Worksheets("L4_Request").Activate
Range("A10:J10").Select
Selection.Copy
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Windows("Test Book2.xls").Activate
Range("B10").Select
ActiveSheet.Paste
End Sub

 

[RB Smissaert]

Try this:

Sub test()

Dim rng As Range
Dim oHyperLink As Hyperlink

On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select Project Link", _
Title:="picking a hyperlink", _
Type:=8)
If rng Is Nothing Then
On Error GoTo 0
Exit Sub
Else
Set oHyperLink = rng.Hyperlinks(1)
If oHyperLink Is Nothing Then
MsgBox "Need to pick a cell with a hyperlink!", , "picking a
hyperlink"
On Error GoTo 0
Exit Sub
End If
End If

On Error GoTo 0

oHyperLink.Follow NewWindow:=False, AddHistory:=True

End Sub


RBS

 

[Guest]

Thank You, That worked very well.

Try this:

Sub test()

Dim rng As Range
Dim oHyperLink As Hyperlink

On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select Project Link", _
Title:="picking a hyperlink", _
Type:=8)
If rng Is Nothing Then
On Error GoTo 0
Exit Sub
Else
Set oHyperLink = rng.Hyperlinks(1)
If oHyperLink Is Nothing Then
MsgBox "Need to pick a cell with a hyperlink!", , "picking a
hyperlink"
On Error GoTo 0
Exit Sub
End If
End If

On Error GoTo 0

oHyperLink.Follow NewWindow:=False, AddHistory:=True

End Sub


RBS