UserSelections.bas

Attribute VB_Name = "UserSelections"
Sub PickWorkbookAndSheet_OneClick()
    Dim picked As Range
    Dim wb As Workbook
    Dim ws As Worksheet

    MsgBox "Click any cell on the sheet you want to use.", vbInformation

    On Error Resume Next
    Set picked = Application.InputBox("Click a cell on the target sheet:", Type:=8)
    On Error GoTo 0

    If picked Is Nothing Then
        MsgBox "No selection made."
        Exit Sub
    End If

    Set ws = picked.Parent
    Set wb = ws.Parent

    MsgBox "You selected:" & vbCrLf & _
           "Workbook: " & wb.Name & vbCrLf & _
           "Sheet: " & ws.Name

    ' Continue your macro here
End Sub

Function PickSheetOneClick() As Worksheet
    Dim picked As Range

    MsgBox "Click any cell on the sheet you want to use.", vbInformation

    ' Allow clicking in other workbooks
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    On Error Resume Next
    Set picked = Application.InputBox("Click a cell on the target sheet:", Type:=8)
    On Error GoTo 0

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    If picked Is Nothing Then
        MsgBox "No selection made."
        Exit Function
    End If

    Set PickSheetOneClick = picked.Parent
End Function


Sub USELIKE_AskUserForWorkbookAndSheet()

Dim ws As Worksheet
Dim wb As Workbook

Set ws = PickSheetOneClick
If ws Is Nothing Then Exit Sub

Set wb = ws.Parent

    MsgBox "Workbook: " & wb.Name & vbCrLf & "Sheet: " & ws.Name
    
    Set TARGETWS = ws
    
    
End Sub