2

I am running an VBA and I have problem with cancel button, I want no change of value in cell if cancel button pressed or escape button. How to proceed?

Private Sub Workbook_Open()
    Sheets("Start").Range("B1").Value = Application.InputBox(Prompt:="Insert Date", Type:=1)
    Cancel = True
End Sub
Community
  • 1
  • 1
Xaratsarhs
  • 55
  • 1
  • 6
  • Another way to accept dates [HERE](http://stackoverflow.com/questions/12012206/formatting-mm-dd-yyyy-dates-in-textbox-in-vba/12013961#12013961) – Siddharth Rout Apr 08 '14 at 09:30

2 Answers2

3

Try this one:

Private Sub Workbook_Open()
    Dim inpt
    inpt = Application.InputBox(Prompt:="Insert Date", Type:=1)
    If CStr(inpt) <> "False" Then Sheets("Start").Range("B1").Value = inpt
End Sub

the reason why I use CStr(inpt) <> "False" instead inpt <> False or just If inpt Then because user can enter 0 (it's valid input for inputbox with Type:=1)

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • Had missed this response, but still think is better to run with a numeric variable given a date is required rather than a variant – brettdj Apr 08 '14 at 08:58
  • yeah, agree, +1 to your solution. I've just used variant for the case when `0` is valid input (I know OP operates with dates `Insert Date` but..for somebody who going to use it with numerics) – Dmitry Pavliv Apr 08 '14 at 09:02
  • 1
    whoops missed the +1. added now. – brettdj Apr 08 '14 at 09:11
  • 1
    I've tried to input 0 without your new improvements and it works like cancel so I don't mind that much, thank you very much though. – Xaratsarhs Apr 09 '14 at 11:47
2

something like this (I have included today's date as the default choice)

Dim lngCnt As Long
lngCnt = Application.InputBox(Prompt:="Insert Date", Default:=Format(Now(), "dd-mmm-yy"), Type:=1)
If lngCnt = 0 Then
'user cancelled
Else
Sheets("Start").Range("B1").Value = lngCnt
End If
brettdj
  • 54,857
  • 16
  • 114
  • 177
  • Thanks mate but not very useful in this case, as the date is going to be last day of previous month and not always. Only the first time of the month that's opens the excel I need the user to set new date. Next times I want him to bypass it with cancel. It's little more complicate that I can describe now but thank you for your effort though. – Xaratsarhs Apr 09 '14 at 11:38
  • @Xaratsarhs you can always remove the default, or chnage it to the last day of prior month. It was provided as an example. The cancel check holds though. – brettdj Apr 09 '14 at 11:40