I implement along similar lines as your 'temp' etc, but can't think why your
inputbox should suddenly disappear without a chance to use as normal
(SendKeys involved perhaps ?).
Forgot to mention need to cater for formula generated both by selecting
cells & manually typed, w/out the = and other quotes that creep in. Try
something like the following (this is stripped down from a much larger
function & options used in a dll, hope no errors have crept in while editing
for this post).
Function GetInputRange2(rng As Excel.Range, _
sPrompt As String, _
sTitle As String, _
Optional sDefault As String, _
Optional X, Optional Y, _
Optional bActivate As Boolean) As Boolean
Dim bGotRng As Boolean
Dim sAddr As String
Dim vReturn
On Error Resume Next
If Len(sDefault) = 0 Then
If TypeName(ActiveSheet) = "Chart" Then
sDefault = " first select a Worksheet"
ElseIf TypeName(Selection) = "Range" Then
sDefault = "=" & Selection.Address
Else
sDefault = " Select Cell(s)"
End If
End If
vReturn = Application.InputBox(sPrompt, sTitle, sDefault, X, Y, Type:=0)
If Len(vReturn) And False <> vReturn Then
sAddr = vReturn
' might be an idea to increase the 256 to 1024
If Left$(sAddr, 1) = "=" Then sAddr = Mid$(sAddr, 2, 256)
If Left$(sAddr, 1) = Chr(34) Then sAddr = Mid$(sAddr, 2, 255)
If Right$(sAddr, 1) = Chr(34) Then sAddr = Left$(sAddr, Len(sAddr) -
1)
Set rng = Range(sAddr)
If rng Is Nothing Then
sAddr = Application.ConvertFormula(sAddr, xlR1C1, xlA1)
Set rng = Range(sAddr)
bGotRng = Not rng Is Nothing
Else
bGotRng = True
End If
End If
On Error GoTo errH
If bGotRng And bActivate Then
If Not ActiveWorkbook Is rng.Parent.Parent Then
rng.Parent.Parent.Activate
End If
If Not ActiveSheet Is rng.Parent Then
rng.Parent.Activate
End If
rng.Activate
End If
done:
GetInputRange2 = bGotRng
Exit Function
errH:
bGotRng = False
Resume done
End Function
Sub test2()
Dim rInput As Range
If GetInputRange2(rInput, "Prompt", "Title", "", , , True) Then
MsgBox rInput.Address(, , , True)
Else
MsgBox "not got a range"
End If
End Sub
Try selecting a range in different sheet and/or workbook to test the
bActivate option (may want an additional option to disable events).
Regards,
Peter T