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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jdavis9

Active Member
Joined
Mar 8, 2002
Messages
337
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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"?
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
Yes, you're right, default is ddmmyy, I just never changed it in the coding.

Thanks for the quick reply guys :)
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176

ADVERTISEMENT

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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
From the code I posted I think you need:

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

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

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
Top