Capture InputBox vbCancel

[Rob]

Hello, I have the below code that prompts for the qty of rows to be inserted
but when the Cancel button is selected instead of providing a number and
clicking OK I get an error. I was hoping that putting the IF statement in
there would fix it but it did not. How can I fix this?

Thanks in Advance.


Sub AutoInsertRows()

'Shortcut Keystroke CTRL+Shift+A

Dim r As Integer

r = InputBox("How Many Rows?", "Auto-Insert Rows", 1)
i = 0

Do While i < r

If Not r = vbCancel Then

Selection.Insert Shift:=xlDown

i = i + 1

Else

Exit Sub

End If

Loop

End Sub

 

[Charlie]

Dim i As Long
Dim n As Long

On Error GoTo CancelInput
n = InputBox("No. of Rows")
CancelInput:

For i = 1 To n
Selection.Insert Shift:=xlDown
Next i

P.S. Throw out "Dim ... As Integer" Get in the habit of always using Long.
You won't regret it.

 

[Gary Keramidas]

i usually use application.inputbox. cancel will return 0

lookup InputBox Method in vb help.

 

[Vergel Adriano]

Hi Rob,

I believe InputBox will return a null string if user clicks on Cancel. Try
something like this:

Sub AutoInsertRows()

'Shortcut Keystroke CTRL+Shift+A

Dim r As String

r = InputBox("How Many Rows?", "Auto-Insert Rows", 1)
If r = "" Then
MsgBox "user cancelled"
Exit Sub
End If
If Not IsNumeric(r) Then
MsgBox "value must be numeric"
Exit Sub
End If
i = 0

Do While i < r

If Not r = vbCancel Then

Selection.Insert Shift:=xlDown

i = i + 1

Else

Exit Sub

End If

Loop

End Sub

 

[Rob]

Thanks A ton, it's working now.

Cheers.

Hi Rob,

I believe InputBox will return a null string if user clicks on Cancel. Try
something like this:

Sub AutoInsertRows()

'Shortcut Keystroke CTRL+Shift+A

Dim r As String

r = InputBox("How Many Rows?", "Auto-Insert Rows", 1)
If r = "" Then
MsgBox "user cancelled"
Exit Sub
End If
If Not IsNumeric(r) Then
MsgBox "value must be numeric"
Exit Sub
End If
i = 0

Do While i < r

If Not r = vbCancel Then

Selection.Insert Shift:=xlDown

i = i + 1

Else

Exit Sub

End If

Loop

End Sub