这是我使用的（类似的情况在这里）：

Sub test2()
    Dim columnStart As Range
    Dim columnNr As Long
    Dim wbC As Workbook
    Dim wsC As Worksheet
    Dim columnAddress As String
    
    On Error Resume Next
        Set columnStart = Application.InputBox("Select the column with the first key", Default:=Selection.Address(0, 0), Type:=8)
    On Error GoTo 0
    If columnStart Is Nothing Then
        MsgBox "Cancelled."
        Exit Sub
    End If
    Set wbC = columnStart.Parent.Parent
    Set wsC = columnStart.Parent
    columnAddress = columnStart.Address
    columnNr = columnStart.Column
    Debug.Print "Workbook: " & wbC.Name & " with worksheet: " & wsC.Name & " & column Address: " & columnAddress & " with column number : " & columnNr
End Sub

字符串
你得到的范围有一个parent = Workbook，它也有自己的父对象= Workbook。
我的代码的目的是不使它成为我的活动工作簿，而只是获得键列，以便我可以基于它的其余代码。

**编辑：**为您提供更多内容：

Sub Getsheet()

    Dim desiredSheetName As String
    Dim rng As Range
    Set rng = Application.InputBox("Select any cell inside the target sheet: ", "Prompt for selecting target sheet name", Type:=8)
    desiredSheetName = rng.Parent.Name
    Debug.Print desiredSheetName
    
    MsgBox desiredSheetName
    
    rng.Parent.Parent.Activate 'Workbook
    Range("A1") = "TEST"
    'or without needing to use Activate:
    Dim wb As Workbook, ws As Worksheet
    Set wb = rng.Parent.Parent 'isn't really needed here
    Set ws = rng.Parent
    ws.Range("A1").Value = "Test"
End Sub

型

所以我可以看到一些关于你如何提示这个问题，但要回答激活工作表的请求，你不必做任何事情.你说，你希望有人点击范围，这将把他们放在该表上。
如果您想从选定的单元格中捕获信息，则有一个选项列表，例如：

Sub test() 'I have the picture below to show the outputs
    Debug.Print Selection.Address
    Debug.Print ActiveSheet.Name
    Debug.Print ActiveWorkbook.Name
End Sub

字符串
因此，如果你想调用一个工作簿，你可以将它们存储为变量，并使用这样的：

Sub test()
    Dim rAddress As Range:  rAddress = Selection.Address
    Dim rSheet As Worksheet:  rSheet = ActiveSheet.Name
    Dim rBook As Workbook:  rBook = ActiveWorkbook.Name
End Sub

型
然后调用可以是Workbooks(rBook).Sheets(rSheet).Address(rAddress)。

的数据