Cancel InputBox

[LeonardSterk]

Hi,

An InputBox appears when clicking on the button, when I type a date and click on "OK" it works perfectly.
Except there's also a "Cancel" button, but when you click on Cancel, it still creates the file but without date...
How to cancel the creation of the new sheet with the cancel button?

Sub Export()

    Dim PathXLSX As String
    Dim NameXLSX As String

    PathXLSX = "G:\Companies\"
    NameXLSX = "Test" & InputBox("Enter Date") & ".xlsx"

    ThisWorkbook.Sheets("Prices").Copy
    Application.DisplayAlerts = False
    With ActiveWorkbook
        .SaveAs Filename:= _
        PathXLSX & NameXLSX, _
        FileFormat:=51, _
        CreateBackup:=False
        .Close False
    End With
    Application.DisplayAlerts = True

    MsgBox "The file has been saved at " & PathXLSX & NameXLSX

End Sub

 

[mohadin]

Hi
Try

Sub Export()

    Dim PathXLSX As String
    Dim NameXLSX As String

'    PathCSV = "G:\Companies\"
    NameCSV = "Test" & InputBox("Enter Date") & ".xlsx"
If Len(NameCSV) > 9 Then
    ThisWorkbook.Sheets("Prices").Copy
    Application.DisplayAlerts = False
    With ActiveWorkbook
        .SaveAs Filename:= _
        PathXLSX & NameXLSX, _
        FileFormat:=51, _
        CreateBackup:=False
        .Close False
    End With
    Application.DisplayAlerts = True

    MsgBox "The file has been saved at " & PathXLSX & NameXLSX
End If
End Sub

 

[LeonardSterk]

Hi
Try

Sub Export()

    Dim PathXLSX As String
    Dim NameXLSX As String

'    PathCSV = "G:\Companies\"
    NameCSV = "Test" & InputBox("Enter Date") & ".xlsx"
If Len(NameCSV) > 9 Then
    ThisWorkbook.Sheets("Prices").Copy
    Application.DisplayAlerts = False
    With ActiveWorkbook
        .SaveAs Filename:= _
        PathXLSX & NameXLSX, _
        FileFormat:=51, _
        CreateBackup:=False
        .Close False
    End With
    Application.DisplayAlerts = True

    MsgBox "The file has been saved at " & PathXLSX & NameXLSX
End If
End Sub

Hi,

It still exports the file but without date

 

[mohadin]

tested here ok
Any way
What about

Sub Export()

    Dim PathXLSX As String
    Dim NameXLSX As String

    PathCSV = "G:\Companies\"
    NameCSV = "Test" & InputBox("Enter Date") & ".xlsx"
 [B]   If Len(Trim(NameCSV)) <> 9 Then[/B]

        ThisWorkbook.Sheets("Prices").Copy
        Application.DisplayAlerts = False
        With ActiveWorkbook
            .SaveAs Filename:= _
                    PathXLSX & NameXLSX, _
                    FileFormat:=51, _
                    CreateBackup:=False
            .Close False
        End With
        Application.DisplayAlerts = True

        MsgBox "The file has been saved at " & PathXLSX & NameXLSX
[B]    End If[/B]
End Sub

 

[LeonardSterk]

tested here ok
Any way
What about

Sub Export()

    Dim PathXLSX As String
    Dim NameXLSX As String

    PathCSV = "G:\Companies\"
    NameCSV = "Test" & InputBox("Enter Date") & ".xlsx"
[B]   If Len(Trim(NameCSV)) <> 9 Then[/B]

        ThisWorkbook.Sheets("Prices").Copy
        Application.DisplayAlerts = False
        With ActiveWorkbook
            .SaveAs Filename:= _
                    PathXLSX & NameXLSX, _
                    FileFormat:=51, _
                    CreateBackup:=False
            .Close False
        End With
        Application.DisplayAlerts = True

        MsgBox "The file has been saved at " & PathXLSX & NameXLSX
[B]    End If[/B]
End Sub

Amazing!
Thank you very much

 

[mohadin]

You are welcome
And thank you for the feedback
Be happy & safe

 

[LeonardSterk]

You are welcome
And thank you for the feedback
Be happy & safe

I'm confused. It worked.. But trying again it does not work anymore..?!
It's back at still exporting when clicking cancel.

This is the code I used..

Sub Export()

    Dim PathXLSX As String
    Dim NameXLSX As String

    PathXLSX = "G:\Companies\"
    NameXLSX = "Test" & InputBox("Enter Date") & ".xlsx"
    If Len(Trim(NameXLSX)) <> 9 Then

        ThisWorkbook.Sheets("Prices").Copy
        Application.DisplayAlerts = False
        With ActiveWorkbook
            .SaveAs Filename:= _
                    PathXLSX & NameXLSX, _
                    FileFormat:=51, _
                    CreateBackup:=False
            .Close False
        End With
        Application.DisplayAlerts = True

        MsgBox "The file has been saved at " & PathXLSX & NameXLSX
    End If
End Sub

 

[mohadin]

Really strange
Replace

If Len(Trim(NameXLSX)) <> 9 Then

With

  If Trim(Join(Split(Join(Split(NameCSV, "Test")), ".xlsx"))) <> "" Then

See how it goes

 

[LeonardSterk]

Really strange
Replace

If Len(Trim(NameXLSX)) <> 9 Then

With

  If Trim(Join(Split(Join(Split(NameCSV, "Test")), ".xlsx"))) <> "" Then

See how it goes

Yes. Cancel works!
Except... It cancels everything now D:
When clicking on OK it doesn't create a file nor say the msgbox anymore, basically cancelling everything probably.

 

[mohadin]

What?
I'm testing here all the possibilities
Even If you typed nothing then hit Ok will not export
!!!!!!!!!!!