• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to put a message into this macro?

akinkaraman

Member
I want to get a message like which says "Nothing is happened !" when I click to cancel at the below the macro. Please tell me how to modify this macro..

Thanks..

Code:
Sub Swap_Ranges()
'Updateby20131120
Dim Rng1 As Range, Rng2 As Range
Dim arr1 As Variant, arr2 As Variant
xTitleId = "Swap Ranges"
Set Rng1 = Application.Selection
Set Rng1 = Application.InputBox("Range1:", xTitleId, Rng1.Address, Type:=8)
Set Rng2 = Application.InputBox("Range2:", xTitleId, Type:=8)
Application.ScreenUpdating = False
arr1 = Rng1.Value
arr2 = Rng2.Value
Rng1.Value = arr2
Rng2.Value = arr1
Application.ScreenUpdating = True
End Sub
 
Try this:

Code:
Sub Swap_Ranges()
'Updateby20131120Dim Rng1 As Range, Rng2 As Range
Dim arr1 As Variant, arr2 As Variant
xTitleId = "Swap Ranges"
On Error GoTo Error
Set Rng1 = Application.Selection
Set Rng1 = Application.InputBox("Range1:", xTitleId, Rng1.Address, Type:=8)
Set Rng2 = Application.InputBox("Range2:", xTitleId, Type:=8)
Application.ScreenUpdating = False
arr1 = Rng1.Value
arr2 = Rng2.Value
Rng1.Value = arr2
Rng2.Value = arr1
Application.ScreenUpdating = True
Exit Sub

Error:
MsgBox "Nothing is happened !"

End Sub

if you only want the Esc to be picked up by the second Inputbox change it to

Code:
Sub Swap_Ranges()
'Updateby20131120Dim Rng1 As Range, Rng2 As Range
Dim arr1 As Variant, arr2 As Variant
xTitleId = "Swap Ranges"
Set Rng1 = Application.Selection
Set Rng1 = Application.InputBox("Range1:", xTitleId, Rng1.Address, Type:=8)
On Error GoTo Error
Set Rng2 = Application.InputBox("Range2:", xTitleId, Type:=8)
Application.ScreenUpdating = False
arr1 = Rng1.Value
arr2 = Rng2.Value
Rng1.Value = arr2
Rng2.Value = arr1
Application.ScreenUpdating = True
Exit Sub

Error:
MsgBox "Nothing is happened !"

End Sub
 
First code is working fine.. Thanks..

Second code makes debug at =>
Set Rng1 = Application.InputBox("Range1:", xTitleId, Rng1.Address, Type:=8)
 
You may need to insert a carriage return (Press enter) at the end of that line just to make sure it is on a line of its own

I never tested it and it may not have a proper CR/LF at the end of the row if you copied/pasted the code from above

I just tested it an it worked fine as posted?
 
First code is working fine.. Thanks..

Second code makes debug at =>
Set Rng1 = Application.InputBox("Range1:", xTitleId, Rng1.Address, Type:=8)
 
Back
Top