下記モジュールをダイアログのOKボタンなどに登録して、
範囲選択後に、
シート名 および セル範囲 を debug.print で出力する。
-------------------------------------------
Sub シート名とセル範囲()
'ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽの[OK]ボタンに登録しています
Dim myAns As String
Dim i As Long
Dim myShe As String '別のシート名を格納する
Dim myFlag1 As Boolean '別のシートを選択したか
Dim myFlag2 As Boolean '範囲選択か
Dim Val1 As String '範囲選択の始点
Dim Val2 As String '範囲選択の終点
'初期値設定
myFlag1 = False
myFlag = False
'①範囲選択を分解する
myAns = Sheets("Dialog2").EditBoxes(1).Text
For i = 1 To Len(myAns)
'-1：アクティブシートか？別のシートを選択したか？
If Mid(myAns, i, 1) = "!" Then '
'別のシートを選択した場合は "!"がある
myFlag1 = True
myShe = Left(myAns, i - 1)
End If
'-2：範囲選択か？単一セルか？
If Mid(myAns, i, 1) = ":" Then
'範囲選択の場合→":"がある
myFlag2 = True 'フラグをtrueにする
Exit For 'ループから抜ける
'変数iは":"までの字数を意味する
End If
Next i
'②値を取得するし、イミディエイトウィンドに出力します
If myFlag1 = True And myFlag2 = True Then
'-1：別のシートを選択し、範囲選択している
Val1 = Mid(myAns, Len(myShe) + 2, i - (Len(myShe) + 2))
Val2 = Right(myAns, Len(myAns) - i)
Debug.Print myShe & "シート の" & Val1 & " ～ " & Val2 & " の範囲を取得しました"
ElseIf myFlag1 = True And myFlag2 = False Then
'-2：別のシートを選択し、単一のセルを選択している
Val1 = Right(myAns, Len(myAns) - Len(myShe))
Debug.Print myShe & "シート の" & Val1 & " を取得しました"
ElseIf myFlag1 = False And myFlag2 = True Then
'-3：アクティブシートを選択し、範囲選択している
Val1 = Left(myAns, i - 1)
Val2 = Right(myAns, Len(myAns) - i)
Debug.Print Val1 & " ～ " & Val2 & " の範囲を取得しました"
ElseIf myFlag1 = False And myFlag2 = False Then
'-4：アクティブシートを選択し、単一のセルを選択している
Val1 = Left(myAns, Len(myAns))
Debug.Print Val1 & " を取得しました"
End If
End Sub