VBA for User Inputted Date less 1 day

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
Hi guys/gals,

On clicking a command button, a user is prompted to insert todays date. The VBA for this is as follows:-

Dim Message, Title, Default, MyValue
Message = "Input Yesterday's Date in ddmmyy format" ' Set prompt.
Title = "InputBox Demo" ' Set title.
Default = "dd/mm/yy" ' Set default.
' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)

I then use MyValue to open up a workbook which is say "C:\sales " & MyValue & ".xls" which works fine.

I would also like to open up the previous days sales figures. I was hoping to use Myvalue - 1 but this does not appear to work.

Ideally .... as sales will only be Monday to Friday ..... I would also like the VBA to distinguish that if Myvalue is a monday, then the previous day I need is te Friday ... i.e. Myvalue - 3 as opposed to Myvalue - 1
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Try this out

Code:
Dim MyValueDow ' Day of Week
Dim DayToOpen  ' Previous Work Day

MyValueDow = Format(Now(), "ddd")
If MyValueDow = "Mon" Then
    DayToOpen = Format(Now() - 3, "dd/mm/yy")
Else
    DayToOpen = Format(Now() - 1, "dd/mm/yy")
End If
End Sub
 
Upvote 0
See if you can adapt this:

Code:
Sub Test()
    Dim Message, Title, Default, MyValue
    Dim MyDate As Date
    Message = "Input Yesterday's Date in ddmmyy format" ' Set prompt.
    Title = "InputBox Demo" ' Set title.
    Default = "dd/mm/yy" ' Set default.
'   Display message, title, and default value.
    MyValue = InputBox(Message, Title, Default)
    MsgBox "C:\sales " & MyValue & ".xls"
    MyDate = DateSerial(Right(MyValue, 2), Mid(MyValue, 3, 2), Left(MyValue, 2))
    If Weekday(MyDate) = vbMonday Then
        MyDate = MyDate - 3
    Else
        MyDate = MyDate - 1
    End If
    MsgBox "C:\sales " & Format(MyDate, "ddmmyy") & ".xls"
End Sub

Shouldn't your default be "ddmmyy" rather than "dd/mm/yy"?
 
Upvote 0
Yes, you're right, default is ddmmyy, I just never changed it in the coding.

Thanks for the quick reply guys :)
 
Upvote 0
I've amended the VBA as it was not opening any files, just providing a message box. However .... if I input yesterday's date, being 110906 ... the file dated 110906 will open but thats it. It is not currently opening Friday's file ... this being Mydate -3 .... any advice?

Sub OPENextracts()
Dim Message, Title, Default, MyValue
Dim MyDate As Date
Message = "Input Todays Date in ddmmyy format" ' Set prompt.
Title = "InputBox Demo" ' Set title.
Default = "ddmmyy" ' Set default.
' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)
Workbooks.Open Filename:="H:/Extracts/Cure/Part Cure Rate Extraction " & MyValue & ".xls"
MyDate = DateSerial(Right(MyValue, 2), Mid(MyValue, 3, 2), Left(MyValue, 2))
If Weekday(MyDate) = vbMonday Then
MyDate = MyDate - 3
Else
MyDate = MyDate - 1
Workbooks.Open Filename:="H:/Extracts/Cure/Part Cure Rate Extraction " & MyDate & ".xls"

End Sub
 
Upvote 0
Why is the code to Open inside the If structure?

As far as I can see the code will only open the other workbook today isn't a Monday.

Unless that's a typo, because you seem to be missing an End If, and that code won't actually compile.:)
 
Upvote 0
From the code I posted I think you need:

Workbooks.Open Filename:="H:/Extracts/Cure/Part Cure Rate Extraction " & Format(MyDate, "ddmmyy") & ".xls"
 
Upvote 0
I've put back the End If and included the code that you originally posted .... now works perfectly. :)

Thank you very much.


MyDate = DateSerial(Right(MyValue, 2), Mid(MyValue, 3, 2), Left(MyValue, 2))
If Weekday(MyDate) = vbMonday Then
MyDate = MyDate - 3
Else
MyDate = MyDate - 1
End If


Workbooks.Open Filename:="H:/Extracts/Cure/Part Cure Rate Extraction " & Format(MyDate, "ddmmyy") & ".xls"

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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