Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Value from a Range (depends on input to determind month) | Excel Worksheet Functions | |||
List Box - For Input Range can I use named range in another workbo | Excel Worksheet Functions | |||
sheet protection - only selected range to be able to select/input data | Excel Worksheet Functions | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming | |||
Macro to input formula in range based on another range | Excel Programming |