Extract a worksheet name from the range selected

excel worksheet inputbox vba
Sam picture Sam · Feb 8, 2017 · Viewed 9.7k times · Source

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'?

Answer

Fadi picture Fadi · Feb 8, 2017

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

Related questions

How to add a named sheet at the end of all Excel sheets?

I am trying to add an Excel sheet named "Temp" at the end of all existing sheets, but this code is not working: Private Sub CreateSheet() Dim ws As Worksheet ws.Name = "Tempo" Set ws = Sheets.Add(After:=Sheets(Sheets.…

Read More
Excel VBA For Each Worksheet Loop

I am working on code to basically go through each sheet in my Workbook, and then update column widths. Below is the code I wrote; I don't receive any errors, but it also doesn't actually do anything. Any help is …

Read More
Is there a macro to conditionally copy rows to another worksheet?

Is there a macro or a way to conditionally copy rows from one worksheet to another in Excel 2003? I'm pulling a list of data from SharePoint via a web query into a blank worksheet in Excel, and then I want …

Read More