Get name of worksheet selected using Application.Inputbox

Question

I want to get the name of the worksheet I selected.

I tried this code from ChatGPT.

Sub GetSelectedWorksheetName()

    Dim selectedSheet As Worksheet

    Dim selectedSheetName As String
    
    On Error Resume Next
    Set selectedSheet = Application.InputBox("Select a worksheet", Type:=8).Worksheet

    On Error GoTo 0
    
    If Not selectedSheet Is Nothing Then
        selectedSheetName = selectedSheet.Name
        MsgBox "Selected worksheet name: " & selectedSheetName

    Else
        MsgBox "No worksheet selected."
    End If
End Sub

I received this error message.

Answer 1

As the inputbox expects a range (i.e. one or more selected cells), you/user need(s) to select that and not just the sheet. See documentation here for the different types and what they expect.

So to make your code work as intended, select a range in the sheet and not only the sheet and adjust your code as follows:

Sub GetSelectedWorksheetName()

    Dim selectedSheet As Worksheet
    Dim selectedSheetName As String
    
    On Error Resume Next
    Set selectedSheet = Application.InputBox("Select a worksheet", Type:=8).Parent
    
    On Error GoTo 0
    
    If Not selectedSheet Is Nothing Then
        selectedSheetName = selectedSheet.Name
        MsgBox "Selected worksheet name: " & selectedSheetName
    Else
        MsgBox "No worksheet selected."
    End If

End Sub