macro to have user select a range

[longhornfan]

I am in need to have an application.inbox prompt a user to enable the user to click a particular cell. From there i need the address of the cell as well as the sheet.

Dim MyRange As Range
Set MyRange = Application.InputBox("Select the range to process", Type:=8)

If Not MyRange Is Nothing Then
MsgBox MyRange.Parent.Name
     
    Else: Exit Sub
    End If

If the user presses cancel, exit sub would need to be there.
If the user presses the x, exit sub would need to be there.

I hope my ask is coming across clearly. i need be able to prompt a user to select the sheet they want to see and capture that sheet name for future use.

 

[taurean]

Try:

Sub Test()
Dim MyRange As Range
On Error GoTo ExSub
Set MyRange = Application.InputBox("Select the range to process", Type:=8)
MsgBox MyRange.Parent.Name
ExSub:
End Sub

 

[Domenic]

Try...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] MyRange [color=darkblue]As[/color] Range
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    [color=darkblue]Set[/color] MyRange = Application.InputBox("Select the range to process.", Type:=8)
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
    
    [color=darkblue]If[/color] MyRange [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    MsgBox MyRange.Parent.Name
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[AlphaFrog]

    Dim MyRange As Range
    On Error Resume Next
    Set MyRange = Application.InputBox("Select the range to process", Type:=8)
    On Error GoTo 0
    If MyRange Is Nothing Then Exit Sub
    
    MsgBox MyRange.Parent.Name & vbCr & MyRange.Address

 

[longhornfan]

    Dim MyRange As Range
    On Error Resume Next
    Set MyRange = Application.InputBox("Select the range to process", Type:=8)
    On Error GoTo 0
    If MyRange Is Nothing Then Exit Sub
 
    MsgBox MyRange.Parent.Name & vbCr & MyRange.Address

Worked. I took out the address part and did a set ws = MyRange.Parent.Name

Problem solved.