Cancel button on Input box giving error

[Chewyhairball]

Hi

This bit of code lets me move rows around in a table using an input box to select the destination.
It works fine except when i click 'cancel' in which case is give a runtime 424 object required error.

any ideas how to fix this?

thanks

Rory

Sub Move_Selected_Cells()
Dim fromrange As Range
Dim torange As Range

Set fromrange = Range(ActiveCell.Offset(0, -2), ActiveCell.Offset(0, 22))
Set torange = Application.InputBox(Title:="Select Destination", prompt:="Select where you want to item/s to be moved to then click OK", Type:=8)

ActiveSheet.Unprotect
On Error Resume Next
Application.ScreenUpdating = False

If torange Is Nothing Then GoTo errorhandler
On Error GoTo enableeventson:

Application.EnableEvents = False
Range(torange.Offset(0, -2), torange.Offset(0, 22)).Value = fromrange.Value
fromrange.ClearContents
Application.EnableEvents = True

On Error GoTo 0
Application.ScreenUpdating = True

enableeventson:
Application.EnableEvents = True
ActiveSheet.Protect
errorhandler:
ActiveSheet.Protect
End Sub

 

[dmt32]

hi,
try these changes to your code & see if resolves your issue

Sub Move_Selected_Cells()
Dim fromrange As Range
Dim torange As Range

On Error Resume Next
Set torange = Application.InputBox(Title:="Select Destination", prompt:="Select where you want to item/s to be moved to then click OK", Type:=8)
If torange Is Nothing Then GoTo errorhandler

On Error GoTo enableeventson
Set fromrange = Range(ActiveCell.Offset(0, -2), ActiveCell.Offset(0, 22))

'rest of code

Dave

 

[Chewyhairball]

hi,
try these changes to your code & see if resolves your issue

Sub Move_Selected_Cells()
Dim fromrange As Range
Dim torange As Range

On Error Resume Next
Set torange = Application.InputBox(Title:="Select Destination", prompt:="Select where you want to item/s to be moved to then click OK", Type:=8)
If torange Is Nothing Then GoTo errorhandler

On Error GoTo enableeventson
Set fromrange = Range(ActiveCell.Offset(0, -2), ActiveCell.Offset(0, 22))

'rest of code

Dave

Hi Dave

That worked. Thank you so much

Rory

 

[dmt32]

welcome - appreciate your feedback

Dave

 

[davesexcel]

SHG answer here may help.

How to trap Cancel on Application.InputBox?

The following code generates an error on the InputBox command if Cancel is Clicked. What is needed to allow the Cancel to be clicked and exit the Sub if it was clicked. I'm checking for "False" but the program fails on the Input Command just before this check. Sub NameSelectedCells() Dim...

www.mrexcel.com