ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Prompt to Select Cell (https://www.excelbanter.com/excel-programming/391177-prompt-select-cell.html)

Chris T-M
Prompt to Select Cell
 
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
Prompt to Select Cell
 
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


"Chris T-M" wrote in message
...
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.


Chris T-M
Prompt to Select Cell
 
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" wrote:

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


"Chris T-M" wrote in message
...
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
Prompt to Select Cell
 
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


"Chris T-M" wrote in message
...
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" wrote:

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


"Chris T-M" wrote in message
...
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.

Chris T-M
Prompt to Select Cell
 
Thank You, That worked very well.

"RB Smissaert" wrote:

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


"Chris T-M" wrote in message
...
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" wrote:

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


"Chris T-M" wrote in message
...
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.


All times are GMT +1. The time now is 12:37 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com