Input box - end sub if Cancel

keaveneydan

Board Regular
Joined
Apr 29, 2014
Messages
144
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.
Code:
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Try adding these lines
Rich (BB code):
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
 
Upvote 0
Perhaps

Rich (BB code):
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
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
Hard to tell what the first two lines in post #5 refers to?
Did you try either of the first two methods suggested?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top