Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting cell address from input box
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting cell address from input box
Bob:
Thanks for the reply and advice but the real issue is this statement: 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) This statement generates an error if the user selects a cell that happens for the moment to have #NA in it (generated by =na()). What I am trying to achive is to have this statement accept the cell's value even if for the moment it is #NA because I need its address regardless of the value. Am I explaining my problem? Pieter Bob Phillips wrote: : 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting cell address from input box
It didn't bfor me. Have you declared userrange as a particular variable
type? -- HTH RP (remove nothere from the email address if mailing direct) "vandenberg p" wrote in message ... Bob: Thanks for the reply and advice but the real issue is this statement: 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) This statement generates an error if the user selects a cell that happens for the moment to have #NA in it (generated by =na()). What I am trying to achive is to have this statement accept the cell's value even if for the moment it is #NA because I need its address regardless of the value. Am I explaining my problem? Pieter Bob Phillips wrote: : 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting cell address from input box
Pieter,
Try this then . Note the variable declarations, they are important Dim loopc As Long Dim userrange() As Range Dim rng As Range ReDim userrange(1 To 10) On Error Resume Next Set rng = Application.InputBox _ (Prompt:="Input Cell: Select by clicking on the cell you wish to use", _ Title:="Select Input Cell", _ Default:="Select Cell", _ Type:=8) On Error GoTo 0 If IsError(rng) Then MsgBox "Cell has an error" ElseIf rng Is Nothing Then MsgBox "User cancelled" Else Set userrange(loopc) = rng End If -- HTH RP (remove nothere from the email address if mailing direct) "vandenberg p" wrote in message ... Bob: Thanks for the reply and advice but the real issue is this statement: 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) This statement generates an error if the user selects a cell that happens for the moment to have #NA in it (generated by =na()). What I am trying to achive is to have this statement accept the cell's value even if for the moment it is #NA because I need its address regardless of the value. Am I explaining my problem? Pieter Bob Phillips wrote: : 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to input pictures automatically based on cell input? | Excel Worksheet Functions | |||
How do I input address data into a table in Excel 2007? | New Users to Excel | |||
ADDRESS function - dynamic input cell | Excel Discussion (Misc queries) | |||
automate input of an address from Outlook into excel | Excel Discussion (Misc queries) | |||
Pause macro for user cell address input | Excel Programming |