How to use a formula to generate a date as a default value in an input msgbox?-CodePudding

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.

CodePudding user response：

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)

CodePudding user response：

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