PDA

View Full Version : Sleeper: InputBox Error



ddh
09-16-2005, 04:26 AM
When I have the InputBox open and I decide that I don't want to enter a number I just want to get out and I click on the cancel button it will error out.
I thought the code that I had enetered would work but it does not and I don't understand why.
Thank you very much for your help.




Sub InsertRowA601TA()
Application.ScreenUpdating = False
Dim Rg As Range
Dim SRg As String
Dim LRg As String
Dim Rng
Dim i As Long, RowIns As Long
RowIns = InputBox("Enter number of rows required")
If RowIns = "" Then
[C3].Select
Exit Sub
End If
For i = 1 To RowIns
Cells(ActiveCell.Row + 1, 1).Resize(1, 17).Insert _
Shift:=xlDown
Cells(ActiveCell.Row, 1).Resize(1, 2).Copy Destination:=Cells(ActiveCell.Row + 1, 1).Resize(RowIns, 1)
Next i
Cells(ActiveCell.Row + 1, 1).Select
Set Rg = Nothing
ActiveCell.Offset(0, 2).Select
End Sub

Justinlabenne
09-16-2005, 05:02 AM
What's up ddh, Try this:


Sub InsertRowA601TA()
Application.ScreenUpdating = False
Dim Rg As Range
Dim SRg As String
Dim LRg As String
Dim Rng
Dim i As Long, RowIns As String
RowIns = InputBox("Enter number of rows required", , "")
If RowIns = "" Then
[C3].Select
Exit Sub
End If
For i = 1 To CLng(RowIns)
Cells(ActiveCell.Row + 1, 1).Resize(1, 17).Insert _
Shift:=xlDown
Cells(ActiveCell.Row, 1).Resize(1, 2).Copy Destination:=Cells(ActiveCell.Row + 1, 1).Resize(RowIns, 1)
Next i
Cells(ActiveCell.Row + 1, 1).Select
Set Rg = Nothing
ActiveCell.Offset(0, 2).Select
End Sub

Inputboxes of this type take string arguments and you had RowsIn declared as a long. This method just converts the data type.

Better option would be to use Application.Inputbox with a Type:=1 argument. Look up Input box in vba help, you looking for Application.Inputbox, not the just InputBox. 2 different methods.

Bob Phillips
09-16-2005, 05:13 AM
Better option would be to use Application.Inputbox with a Type:=8 arguement.

I don't think you mean type 8, that is a range argument, but rather a type 1 (number), or type 2 (string). Type 1 with a numeric data type means that 0 is not valid input, which is probably okay her, but not in all cases. You could use a string type, and then cast the value to a number, but probably best to use Type 1 and a variant data type, then you can test for False when Cancel hit, unambiguous.

Justinlabenne
09-16-2005, 05:18 AM
I am really glad you posted that xld. Yes, type 8 was my mistake. Should be type 1 for numeric, and I really do prefer to convert the data type's and avoid using the Application method all together.

Thanks for the catch. http://vbaexpress.com/forum/images/smilies/023.gif

ddh
09-16-2005, 05:37 AM
JustinLabeene & xld

Thank you for your help.
I will write down what you both said about how to handle this problem so that when it comes up again I will know how to handle it.
Thank you both very much for your help.

ddh
09-19-2005, 07:27 AM
JustinLabeene & xld

I am sorry but I don't understand how this code should be changed.
This seems to work fine.
Thank you for your help.



Sub InsertRowA601TA()
Application.ScreenUpdating = False
Dim Rg As Range
Dim SRg As String
Dim LRg As String
Dim Rng
Dim i As Long, RowIns As String
RowIns = InputBox("Enter number of rows required", , "")
If RowIns = "" Then
[C3].Select
Exit Sub
End If
For i = 1 To CLng(RowIns)
Cells(ActiveCell.Row + 1, 1).Resize(1, 17).Insert _
Shift:=xlDown
Cells(ActiveCell.Row, 1).Resize(1, 2).Copy Destination:=Cells(ActiveCell.Row + 1, 1).Resize(RowIns, 1)
Next i
Cells(ActiveCell.Row + 1, 1).Select
Set Rg = Nothing
ActiveCell.Offset(0, 2).Select
End Sub

Justinlabenne
09-19-2005, 12:40 PM
I am sorry but I don't understand how this code should be changed.
This seems to work fine

Are you having trouble with the code, or asking how it works?

ddh
09-19-2005, 04:39 PM
Justin Labenne - The code works fine, thank you very much for all of your help.
I read the reply that xld wrote and thought I understood it then
the more I looked at the more I was sure I did not understand what
was being said. So I was not sure if I was being told to change
the code that you wrote.
Thank you both for all of your help, if you were interested I could
show you what I was using the code for.


I don't think you mean type 8, that is a range argument, but rather a type 1 (number), or type 2 (string). Type 1 with a numeric data type means that 0 is not valid input, which is probably okay her, but not in all cases. You could use a string type, and then cast the value to a number, but probably best to use Type 1 and a variant data type, then you can test for False when Cancel hit, unambiguous