-1
    Sub Export_Click()

Dim Rng As Range
Dim WorkRng As Range
Dim vFile As Variant
Dim x As Range
On Error Resume Next

    TitleId = "Export to CSV file"
    x = WorkRng

Set WorkRng = Range("D12:I100")
Set WorkRng = Application.InputBox("Range", TitleId, WorkRng.Address, Type:=8)

    If x = "" Then Exit Sub

    Application.ActiveSheet.Copy
    Application.ActiveSheet.Cells.Clear
    WorkRng.Copy Application.ActiveSheet.Range("A1")

    vFile = Application.GetSaveAsFilename(InitialFileName:=".csv", _
        FileFilter:="CSV files (*.csv), *.csv, All files (*.*), *.*")

If vFile <> False Then ThisWorkbook.SaveAs Filename:=vFile, FileFormat:=xlCS

End Sub

I have a macro that exports a set range to CSV file. I would like the macro to Exit Sub if Cancel is selected on the InputBox but to execute the code if OK is selected.

The code I have above exits sub no matter if OK or Cancel is selected. Without the If x = "" Then Exit Sub statement the code exports to csv no matter if ok or Cancel is selected. I've tried some other options below and it looks like the issue might be with the input being a range. I'm relatively inexperienced and have run out of ideas searching this site and others.

I have tried variations of the code with reference to the link:

Handle cancellation of InputBox to select range

I have also tried incorporating the below code, however it leads to exit sub no matter if OK or cancel is selected:

sub inputbox_verification()

 text=inputbox("type the text")

 if StrPtr(text)=0 then
 'if it entenrs here then the user pressed "cancel"
 endif

 if text=""
 'if enters here the user left in blank
 end if

 if text<>""
 'if enters here the user entered some text
 end if

 end sub
Community
  • 1
  • 1
Lumsden
  • 3
  • 1
  • 1
  • 4

2 Answers2

-1

Inputbox returns False if Cancel is selected, so following code required:

Dim Text As Variant 'You can ommit "As Variant"
Text = Inputbox("Type the text")
'Check for Text type
If TypeName(Text) = "Boolean" Then
    'Check if it is False
    If Not Text Then Exit Sub
End If
AntiDrondert
  • 1,128
  • 8
  • 21
  • 1
    This doesnt work, should be like this: https://stackoverflow.com/questions/26264814/how-to-detect-if-user-select-cancel-inputbox-vba-excel/26264885 – Senor Penguin Nov 15 '19 at 21:08
-1
If WorkRng = False Then Exit Sub
Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
salman MK
  • 1
  • Your answer could be improved by adding more information on what the code does and how it helps the OP. – Tyler2P Nov 06 '22 at 19:30