Getting cell address from input box
Hi Pieter,
When you snip the code, it would be helpful if you get all extraneous stuff
out and add any missing bits (i.e. test it) so that we can just copy and
test.
Anyway
If IsError(Xcell) Then
MsgBox "cell error"
ElseIf Xcell = "" Then
ans = MsgBox("The address you are using appears to be empty. Do you want to
continue with the selection?", _
vbYesNoCancel + vbQuestion, "Cell Selection")
If ans = vbCancel Then GoTo canceledd
If ans = vbNo Then GoTo tryagain
End If
You also don't test for cancel on the Input box.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"vandenberg p" wrote in message
...
I have the following code snippets as part of a user input routine. I Dim
Userrange as range.
This routine works fine as long as the cell that user selects has a value.
If the cell has
as error (it is usually #NA) then the routine does not return the address
and goes to
"canceledd". It is possible that the cell might an error code and yet
still be acceptable,
usually the error is temporary and goes away on the next re-calc (it is
actually an IRR calculation
and sometimes and IRR is impossible to find for a particular set of inputs
which occurs about 5%
of the time in this spreadsheet.)
While it is possible "on the error" to re-calc the sheet and try again it
is possible that the
condition could occur several times in a row so that is not a very clean
solution and hard to explain
to the user why it is necessary to keep trying, particularly when the
inputbox shows
the correct address each time.
I also tried to change the "Type:=" to 24 (which is sum of 8 (range) and
16 (error). This results in the
"if xcell" test being true every time no matter the value of the cell.
Can anyone suggest a solution.
Thank you.
Pieter Vandenberg
---------code snippet-----
tryagain:
On Error goto canceledd
Set userrange(loopc) = Application.InputBox _
(Prompt:="Input Cell: Select by clicking on the cell you wish to use",
_
Title:="Select Input Cell", _
default:="Select Cell", _
Type:=8)
...
Set Xcell = userrange(loopc)
If Xcell = "" Then
ans = MsgBox("The address you are using appears to be empty. Do you want
to continue with the selection?",
vbYesNoCancel + vbQuestion, "Cell Selection")
If ans = vbCancel Then GoTo Canceledd
If ans = vbNo Then GoTo tryagain
...
canceledd:
calculate
goto tryagain
|