input box cancel excel vba

Question

This code copies a block of excel data (Col A to Col BH), and prompts the user to select the row where the copied template needs to be pasted. The code seems to work just fine( feel free to clean up/optimize any code), my issue is whenever a user clicks cancel when they need to pick the row I get an error "run time error 13 type mismatch". Is there anyway to just end the macro if cancel is selected?

 Sub CopyTemplate()

Worksheets("HR-Calc").Activate
Dim rng As Variant
Dim trng As Range
Dim tco As String
Dim hi As String
Dim de As String
'Use the InputBox select row to insert copied cells
Set rng = Application.InputBox("select row to paste into", "Insert template location", Default:=ActiveCell.Address, Type:=8)

startrow = rng.Row
'  MsgBox "row =" & startrow
Range("Bm2") = startrow

Application.ScreenUpdating = False

'copy template block
Range("C6").End(xlDown).Select

Range("bm1") = ActiveCell.Offset(1, 0).Row

Worksheets("HR-CAlc").Activate
tco = "A6:bh" & Range("bm1")
Range(tco).Select
Selection.Copy
Range("A" & Range("bm2")).Activate
Selection.Insert Shift:=xlDown

Range("c100000").End(xlUp).Select
Selection.End(xlUp).Select

'mycell.Select

''Use the InputBox to select text to be replaced
''Set rep = Application.InputBox("select data range where text will be replaced", Default:=ActiveCell.Address, Type:=8)
'Set rep = ActiveCell
'    Told = Application.InputBox("Find the text that needs to be replaced", "Find text in Input data", Default:=ActiveCell.Value, Type:=2)
'    If Told = "" Or vbCancel Then
'    End If
'
'    Tnew = Application.InputBox("Input desired text", "Replace text in data", Default:=ActiveCell.Value, Type:=2)
'    If Tnew = "" Or vbCancel Then
'    End If
'
'        rep.Select
'        Selection.Replace What:=Told, Replacement:=Tnew, LookAt:=xlPart, _
'        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
'        ReplaceFormat:=False

Range("bm1:bm2").ClearContents
SendKeys "{F2}"
SendKeys "{BS}"
Application.ScreenUpdating = True


End Sub

FYI use of `Select`, `Activate` etc is bad practice and generally unnecassary [See This](http://stackoverflow.com/q/10714251/445425) — chris neilsen, Dec 11 '15 at 19:47
I know that I should minimize my use of those but I am still getting the hang of programming. Once the error handling is fixed through the sheet my plan was to go back and try and trim out as much of the code as possible / fix things like select and activate. — Alberto Brown, Dec 11 '15 at 21:56

chris neilsen · Answer 1 · 2015-12-11T19:41:57.770

3

You still need error handling to detect the Cancel

Dim rng As Range  '<~~~ change type so If test will work
'Use the InputBox select row to insert copied cells
Set rng = Nothing  ' in case it was previously set
On Error Resume Next
Set rng = Application.InputBox("select row to paste into", "Insert template location", Default:=ActiveCell.Address, Type:=8)
On Error GoTo 0 ' or your error handler
If rng Is Nothing Then
    ' User canceled, what now?
    Exit Sub 'maybe...
End If

edited Dec 11 '15 at 19:41

answered Dec 11 '15 at 19:37

chris neilsen

52,446
10
84
123

`rng` is defined as `Variant` and not `Range`. – ManishChristian Dec 11 '15 at 19:39
Thats why I included it in the code change, it needs to be `Range` for this to work – chris neilsen Dec 11 '15 at 19:40
Yeah I never coded in error handling. I never thought someone would back out of that window prompt. I do want to exit sub if they hit cancel. I'm guessing theres no way to have that as a persistent code running in the workbook all the time? So any time someone selects cancel on a input box it would exit sub... – Alberto Brown Dec 11 '15 at 21:51

ManishChristian · Answer 2 · 2015-12-11T20:18:09.507

1

Add these lines including error handler:

On Error Resume Next
Set rng = Application.InputBox("select row to paste into", "Insert template location", Default:=ActiveCell.Address, Type:=8)
On Error GoTo 0
If IsEmpty(rng) = True Then
   Exit Sub
End If

These lines will exit the sub if it won't find any value for rng.

edited Dec 11 '15 at 20:18

answered Dec 11 '15 at 19:20

ManishChristian

3,759
3
22
50

What if user picks a Empty cell? – chris neilsen Dec 11 '15 at 19:35
OP's question is clearly stating that `prompts the user to select the row where the copied template needs to be pasted`. – ManishChristian Dec 11 '15 at 19:36
So whats to say _row where the copied template needs to be pasted_ is not empty? Or that the user follows instructions? – chris neilsen Dec 11 '15 at 19:38
Data will be over write if the row is not empty. – ManishChristian Dec 11 '15 at 19:41
My point is that `If IsEmpty(rng)` will return true for an Empty cell, and the Sub will exit. And it doesn't solve the actual problem because OP will still get the error on `Set rng = ...` without error handling – chris neilsen Dec 11 '15 at 19:43
OP is selecting a row and not cell. – ManishChristian Dec 11 '15 at 19:47
A reasonable user response would be to select a cell in the required row, especially give that the default provided by the OP is a single cell. Why build in vulnerabilities when you don't have to? And you still haven't dealt with the root issue of throwing and error on cancel – chris neilsen Dec 11 '15 at 19:54
@chrisneilsen, I agree on Error Handling and so added it. – ManishChristian Dec 11 '15 at 20:19
user selects a cell in the row, or the row. I'm pretty sure the code grabs either and pastes the row value in a holder cell. – Alberto Brown Dec 11 '15 at 21:49

input box cancel excel vba

2 Answers2