Input box doesn't return sheet name with range
 

I have a function that uses the "input box" method and allows the user to
select a range on a sheet. Unfortunately this method only allows the user to
select a range on the currently active sheet.

Here is what I need:

1) The input box should allow the user to select an address from any sheet
in any currently open file and return the file name, sheet name, and range
address (in an array if necessary).

2) The range needs to be validated (i.e. confirmed that it is a valid range
address);

3) Is this possible using an input box or does some other method need to be
used and if so what, how?

4) If my current function can be altered, please fix if possible. My current
function follows (please correct for line wrapping):

Function InputBoxRangeCustom() As String

Dim rngResponse As Range
Dim strMsg As String
Dim intConfirm As Integer

On Error Resume Next
ReDim varSource(3)
Do
Set rngResponse = Application.InputBox("Type or select the range
containing the first list to be compared:", "RANGE VALIDATION",
Selection.Address, , , , , 8)
If Err.Number < 0 Then Err.Clear: End
rngResponse.Select
intConfirm = MsgBox("Is the following selection correct?" & vbCr & vbCr
& rngResponse.Address, vbQuestion + vbYesNo, "CONFIRM SELECTION")
If intConfirm = vbYes Then Exit Do
Loop
InputBoxRangeCustom = rngResponse.Address

End Function

Thanks in advance for your assistance.

Input box doesn't return sheet name with range
 

The application input box should allow you to select a range on any
currently open file. It returns a range reference from which you can get
the filename, sheetname and range address.

Function InputBoxRangeCustom() As String

Dim rngResponse As Range
Dim strMsg As String
Dim intConfirm As Integer

On Error Resume Next
ReDim varSource(3)
Do
Set rngResponse = Application.InputBox("Type or select the range
containing the first list to be compared:", "RANGE VALIDATION",
Selection.Address, , , , , 8)
If Err.Number < 0 Then Err.Clear: End sub
application.Goto rngResponse

intConfirm = MsgBox("Is the following selection correct?" & vbCr & vbCr
& rngResponse.Address(external:=True), vbQuestion + vbYesNo, "CONFIRM
SELECTION")
If intConfirm = vbYes Then Exit Do
Loop
InputBoxRangeCustom = rngResponse.Address

End Function

as long as you are using this function in VBA only and not in a worksheet
cell, then it should work.

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
I have a function that uses the "input box" method and allows the user to
select a range on a sheet. Unfortunately this method only allows the user
to
select a range on the currently active sheet.

Here is what I need:

1) The input box should allow the user to select an address from any sheet
in any currently open file and return the file name, sheet name, and range
address (in an array if necessary).

2) The range needs to be validated (i.e. confirmed that it is a valid
range
address);

3) Is this possible using an input box or does some other method need to
be
used and if so what, how?

4) If my current function can be altered, please fix if possible. My
current
function follows (please correct for line wrapping):

Function InputBoxRangeCustom() As String

Dim rngResponse As Range
Dim strMsg As String
Dim intConfirm As Integer

On Error Resume Next
ReDim varSource(3)
Do
Set rngResponse = Application.InputBox("Type or select the range
containing the first list to be compared:", "RANGE VALIDATION",
Selection.Address, , , , , 8)
If Err.Number < 0 Then Err.Clear: End
rngResponse.Select
intConfirm = MsgBox("Is the following selection correct?" & vbCr &
vbCr
& rngResponse.Address, vbQuestion + vbYesNo, "CONFIRM SELECTION")
If intConfirm = vbYes Then Exit Do
Loop
InputBoxRangeCustom = rngResponse.Address

End Function

Thanks in advance for your assistance.