Exit Sub if user presses cancel button in inputbox or when it is empty

[ayama51]

Hi,

I have an input box that asks user to select a range of cells, then copies the cells and opens a new sheet.

I want to make sure if the user clicks on Cancel it exits the sub and if user clicks ok and the box is empty it prompts the user with a msgbox and else it continues with the macro.

any expert help is welcomed.

this is what i have;
-----------------------------------
Sub RangeSelectionPrompt()


Dim rng As Range

Set rng = Application.InputBox("Select Range, (Client Data): Column A - E only", "Obtain Range Object", Type:=8)


ActiveSheet.Range(rng.Address).Select


Call NewSheet


End Sub
-------------------------------
thanks,

 

[Domenic]

Try...

[font=Courier New][color=darkblue]Sub[/color] RangeSelectionPrompt()

    [color=darkblue]Dim[/color] rng [color=darkblue]As[/color] Range
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    [color=darkblue]Set[/color] rng = Application.InputBox("Select Range, (Client Data): Column A - E only", "Obtain Range Object", Type:=8)
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
    
    [color=darkblue]If[/color] rng [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    ActiveSheet.Range(rng.Address).Select
    
    [color=darkblue]Call[/color] NewSheet

[color=darkblue]End[/color] [color=darkblue]Sub[/color][/font]

Hope this helps!

 

[ayama51]

Thanks Domenic,

It worked but a follow up question as you now what i am dealing with.
when user clicks cancel its exiting the sub which is working fine and i will need a msgbox if the user click ok when the input box is empty.

thanks,

 

[Domenic]

As far as I know, I don't think it's possible. But since the dialog box that appears isn't very useful, and maybe confusing to the user, you can prevent it from appearing by setting the DisplayAlerts property to False at the beginning of the code...

Application.DisplayAlerts = False

In this case, nothing will hapen when pressing Ok without making a selection.

 

[Rick Rothstein]

It worked but a follow up question as you now what i am dealing with.
when user clicks cancel its exiting the sub which is working fine and i will need a msgbox if the user click ok when the input box is empty.

There does not seem to be anyway to trap the user clicking "OK" when the "Type" argument is 8 (range) and the input field is empty or contain text, but we can shut off the non-trappable error message that Application.InputBox issues when the user does that. The following code ends up requiring the user to either click "OK" when there is something in the input field or click "Cancel"... the user will have no other option as the "OK" button will do nothing when the input field is empty or the user attempts to enter a non-range.

[table="width: 500"]
[tr]
	[td]Sub RangeSelectionPrompt()
  Dim Rng As Range
  On Error Resume Next
  Application.DisplayAlerts = False
  Set Rng = Application.InputBox("Select Range, (Client Data): Column A - E only", "Obtain Range Object", Type:=8)
  Application.DisplayAlerts = True
  On Error GoTo 0
  If Rng Is Nothing Then Exit Sub
[COLOR="#008000"]  '
  '  Rest of your code goes here
  '
[/COLOR]End Sub[/td]
[/tr]
[/table]

 

[ayama51]

thanks to Rick and Dominic it is working as I wanted.