Input box - end sub if Cancel

[keaveneydan]

Hi, there are lots of posts about this but I just can't get it to work. I want my macro to just end wth no further action if cancal is chosen on my input box. Please help.

Sub Auto_Open()
Dim dt As Date
dt = Application.InputBox("Enter the valuation date in the format dd/mm/yyyy")
dt = Format(dt, "dd/mmm/yyyy")
If dt = vbNullString Then
    Exit Sub
End If

 
Worksheets("Net Assets").Range("C3").Value = dt
Call UpdateValuationDates
Sheets("Net Assets").Activate
 
End Sub

 

[Peter_SSs]

Try adding these lines

Sub Auto_Open()
Dim dt As Date

dt = Application.InputBox("Enter the valuation date in the format dd/mm/yyyy")
If dt <> 0 Then
  dt = Format(dt, "dd/mmm/yyyy")
  If dt = vbNullString Then
      Exit Sub
  End If
  
   
  Worksheets("Net Assets").Range("C3").Value = dt
  'Call UpdateValuationDates
  Sheets("Net Assets").Activate
End If
 
End Sub

 

[VoG]

Perhaps

Sub Auto_Open()
Dim dt As Variant
dt = Application.InputBox("Enter the valuation date in the format dd/mm/yyyy")
If dt = False Then Exit Sub
dt = Format(dt, "dd/mmm/yyyy")
If dt = vbNullString Then
    Exit Sub
End If

 
Worksheets("Net Assets").Range("C3").Value = dt
Call UpdateValuationDates
Sheets("Net Assets").Activate
 
End Sub

 

[Andrew Poulsom]

Application.InputBox returns False if Cancel is pressed. Use just InputBox which returns "".

 

[keaveneydan]

All I get as my input is 30/Dec/1899

.....presumably this is zero? or blank?

If I don't have Application then my sub won't start on open

 

[Andrew Poulsom]

What do you mean by "If I don't have Application then my sub won't start on open"? Do you get a compile error? If so check for References marked as MISSING and uncheck them (Tools|References in the VBE).

 

[keaveneydan]

Sorry, what I mean is the macro opens when I open my file, or the file is opened, and if I remove the Application from before Input box then nothing happens, the macro doesn't seem to do anything.

 

[Peter_SSs]

Hard to tell what the first two lines in post #5 refers to?
Did you try either of the first two methods suggested?