Extract a worksheet name from the range selected

Question

I have to input the result in the selected cell through InputBox function:

Set OutputStrt = Application.InputBox("Select a cell, where the output should be dropped.", "Output start cell", Type:=8)

When I ran the code in the different worksheet and want the result in the different worksheet, it drops the result in the worksheet where I initially ran the code.

How do I get the Worksheet name, which I selected through the Application.InputBox ?

For example, when I selected in the Inputbox: Definitions!$F$38 how do I get the name 'Definitions'?

`OutputStrt.Parent.Name` – Scott Holtzman Feb 09 '17 at 13:34 — Scott Holtzman, Feb 09 '17 at 13:34

score 3 · Accepted Answer · answered Feb 08 '17 at 22:16

3

Try This:

Sub test()
    Dim Ws As Worksheet
    Dim OutputStrt As Range
    Set OutputStrt = Application.InputBox("Select a cell, where the output should be dropped.", "Output start cell", Type:=8)
    Set Ws = OutputStrt.Worksheet
    MsgBox Ws.Name
End Sub

answered Feb 08 '17 at 22:16

Fadi

3,302
3
18
41

Thank you! .Name was what I needed. – Sam Feb 18 '17 at 20:38
1

Had the same problem. Thanks for the answer. It seems that if you pick another workbook you don't have to specify it. @Fadi – MrZH6 Feb 19 '19 at 16:58

score 0 · Answer 2 · answered Aug 13 '20 at 14:42

0

You can also call it with Selection object

Dim ws As Workseet
Set ws = Selection.Worksheet
Debug.Print "Sheetname: " & ws.Name

answered Aug 13 '20 at 14:42

GeoStoneMarten

533
1
7
19

Extract a worksheet name from the range selected

2 Answers2