1

I am trying to make my input message box a bit more sophisticated by using a formula to generate a default date in an input message box in excel.

Currently, it just gives a default value of "dd-mm-yyyy" to guide the user and then deposits it in cell B2. Code below:

Dim DispatchDate As Variant
Sheets("Template").Select
DispatchDate = InputBox("Enter next business day after today. Must use dd-mm-yyyy format", "Dispatch Date", "dd-mm-yyyy")
Range("B2").Value = DispatchDate

Id like the default value to be the result of the following formula =IF(WEEKDAY(NOW()*1,2)>4,NOW()+3,NOW()+1)

Essentially making the default date today + 1 day unless on a Friday, which would be be today + 3 days (to account for weekends and no Friday dispatch).

I don't even know where to start with this. Any help would be appreciated.

Jennifer Abraham
  • 11
  • 1
  • 1
    I'd just use `Now() + 1` and then use a while loop to add a day while it is not a weekday. The advantage here is that you can also check a list of public holidays and skip those days too. – Enigmativity Aug 02 '22 at 00:38

2 Answers2

1

This can be done with the IIF structure, that corresponds to the IF of the front-end:

Sub fnEvaluateDate()
    Dim DispatchDate As Date

    Sheets("Template").Select
    With Excel.WorksheetFunction
        DispatchDate = Format(IIf(.Weekday(VBA.Now() * 1, 2) > 4, VBA.Now() + 3, VBA.Now() + 1), "dd-mm-yyyy")
    End With
    
    DispatchDate = InputBox("Enter next business day after today. Must use dd-mm-yyyy format", "Dispatch Date", DispatchDate)
    Range("B2").Value = DispatchDate

End Sub
ALeXceL
  • 599
  • 5
  • 11
  • You may remove the With/End With structure by replacing the existing code to: DispatchDate = Format(IIf(Excel.WorksheetFunction.Weekday(VBA.Now() * 1, 2) > 4, VBA.Now() + 3, VBA.Now() + 1), "dd-mm-yyyy") – ALeXceL Aug 02 '22 at 00:27
1

you can use evaluate method to extract value from formula:

Dim DispatchDate As Date
DispatchDate = Evaluate("=IF(WEEKDAY(TODAY(),2)>4,TODAY()+3,TODAY()+1)")
DispatchDate = InputBox("Enter next business day after today. Must use dd-mm-yyyy format", "Dispatch Date", DispatchDate)

or you can convert sheet formula into VBA code:

Dim DispatchDate As Date
DispatchDate = IIf(Weekday(Date, 2) > 4, Date + 3, Date + 1)
DispatchDate = InputBox("Enter next business day after today. Must use dd-mm-yyyy format", "Dispatch Date", DispatchDate)
Vasily
  • 5,707
  • 3
  • 19
  • 34