modify inputboxs so cancel = exit sub

[whiteghost]

Hi
how can I modify this code or the inputboxes sothat if "cancel" is clicked on any of the inputboxes will exit the sub.

Sub star1()
'
Dim loopcount As Integer
Dim i As Integer
loopcount = InputBox("how many rows?")
For i = 1 To loopcount

Range("a65536").Select

Dim x As Double
    
    x = Range("a525").End(xlUp).Row + 1
    Range("a" & x).Select
 

typetext = InputBox(prompt:="Enter Code.", Default:="PCode")
codetext = InputBox(prompt:="Enter coutry Code.", Default:="Country Code")
quantext = InputBox(prompt:="Enter quantity.", Default:="Qty.")
pricetext = InputBox(prompt:="Enter price.", Default:="")
ActiveCell.FormulaR1C1 = typetext
Range("a65536").End(xlUp).Offset(1, 0) = vbNullString
Range("a65536").End(xlUp).Offset(0, 0) = typetext
Range("a65536").End(xlUp).Offset(0, 2) = codetext
Range("a65536").End(xlUp).Offset(0, 4) = quantext
Range("a65536").End(xlUp).Offset(0, 5) = pricetext
    
Next i

x = Range("a525").End(xlUp).Row + 1
    Range("a" & x).Select


End Sub

the macro position the input , the loopcount input box allowser to define how many rows they want to add. What I want to be able to do is exit the sub if cancel is clicked on typetext, codetext, quantext or pricetext.

thanks

[parry]

Hi, if cancel is pressed then it will return a null string to your variable. So do an if statement testing for this.

eg
loopcount = InputBox("how many rows?")
if loopcount = vbNullString then exit sub

you could also use
if loopcount = "" then exit sub

[tactps]

You can use:
If loopcount = "" Or typetext = "" Or codetext = "" Or quantext = "" Or pricetext = "" Then Exit Sub

[parry]

Originally Posted by tactps

You can use:
If loopcount = "" Or typetext = "" Or codetext = "" Or quantext = "" Or pricetext = "" Then Exit Sub

Hi Tactps, yes you could do that but its not suited to this situation. The if test is only made once so you would have to wait until all the input boxes have been presented to the user to know whether they pressed cancel on any one of them. That means if they didnt enter anything in the first one then the others would still have to appear before you would know to exit the procedure. Its best to test each individual box and stop it at the point of the first cancel.

If you have several input boxes like this you may consider creating a userform instead.

[whiteghost]

thanks guys. when I tried this first time round I had been using

If typetext = "" then
Exit Sub

which threw up a "Next without for" or "block if without end if" dependingon where I put it. Never thought to have Eit sub on the same line :o

the best solution (using you guy's suggestion) works out like this:

Sub star1()
'
Dim loopcount As Integer
Dim i As Integer
loopcount = InputBox("how many rows?")
For i = 1 To loopcount

Range("a65536").Select

Dim x As Double
    
    x = Range("a525").End(xlUp).Row + 1
    Range("a" & x).Select
 

typetext = InputBox(prompt:="Enter Code.", Default:="PCode")
if typetet = "" then  Exit sub
codetext = InputBox(prompt:="Enter coutry Code.", Default:="Country Code")
if codetext ="" then Exit Sub
quantext = InputBox(prompt:="Enter quantity.", Default:="Qty.")
if quantext ="" then Exit Sub
pricetext = InputBox(prompt:="Enter price.", Default:="")
if pricetext ="" then Exit Sub
ActiveCell.FormulaR1C1 = typetext
Range("a65536").End(xlUp).Offset(1, 0) = vbNullString
Range("a65536").End(xlUp).Offset(0, 0) = typetext
Range("a65536").End(xlUp).Offset(0, 2) = codetext
Range("a65536").End(xlUp).Offset(0, 4) = quantext
Range("a65536").End(xlUp).Offset(0, 5) = pricetext




    
    
Next i

x = Range("a525").End(xlUp).Row + 1
    Range("a" & x).Select

'

'
End Sub

this way it exits immediately and does not show all the inputboxes before exiting. Thanks again

[Nimrod]

Public Sub InputDemo()
typetext = Application.InputBox(prompt:="Enter Code.", Default:="Code", Type:=2)
If typetext = False Then Exit Sub

codetext = Application.InputBox(prompt:="Enter coutry Code.", Default:="Country Code", Type:=2)
If codetext = False Then Exit Sub

quantext = Application.InputBox(prompt:="Enter quantity.", Default:="Qty.", Type:=1)
If quantext = False Then Exit Sub

pricetext = Application.InputBox(prompt:="Enter price.", Default:="", Type:=1)
If pricetext = False Then Exit Sub

End Sub

The InputBox method differs from the InputBox function in that it allows selective validation of the user's input, and it can be used with Microsoft Excel objects, error values, and formulas. Note that Application.InputBox calls the InputBox method; InputBox with no object qualifier calls the InputBox function.

This method will only allow numbers to be entered for price and quantity

[parry]

Hi, should you also be testing whether the user pressed cancel on the very first input box (ie how many rows?). Just a thought.