Input Box is popping too much.

[Aaron719]

Below is the code I have written. What I want it to do is perform the nested IF functions if "Auto" is selected or pop up an input box if "Manual" is selected. Instead of the input box only popping up once after I select "Manual", it also pops up when I select "Auto" and it will pop up for every cell I have this function in that is dependent on another cell that says "Manual". Essentially it forces me to answer input boxes that have already been answered and are not linked in anyway to the cell I am changing.

To clarify, I want to select "Manual" from a drop-down and I get an input box to put a number in, then I'd hit "OK" and it fills the appropriate cell with that number. If I select "Auto", I want the nested If function to preform and no input boxes.

Thank you!

Function EHours(hVal As String, iVal As String, dVal As String, xVal As String, yVal As String, wVal As String, inputVal As String) As Double


If inputVal = "Manual" Then
EHours = Application.InputBox(prompt:="Hours Worked =", Title:="Hours Worked.", Type:=1)


ElseIf inputVal = "Auto" Then
    If iVal = "Daily" Then
    EHours = hVal
    ElseIf iVal = "Weekly" Then
        If Month(dVal) = yVal And Day(dVal) = xVal Then
        EHours = hVal
        ElseIf wVal = WeekdayName(Weekday(dVal)) Then
        EHours = hVal
        Else:
        EHours = 0
        End If
    ElseIf iVal = "Bi-Weekly" Then
        If Month(dVal) = yVal And Day(dVal) = xVal Then
        EHours = hVal
        ElseIf (Month(DateAdd("d", 14, dVal)) = yVal And Day(DateAdd("d", 14, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 2, dVal)) = yVal And Day(DateAdd("d", 14 * 2, dVal)) = xVal) _
        Or (Month(DateAdd("d", 14 * 3, dVal)) = yVal And Day(DateAdd("d", 14 * 3, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 4, dVal)) = yVal And Day(DateAdd("d", 14 * 4, dVal)) = xVal) _
        Or (Month(DateAdd("d", 14 * 5, dVal)) = yVal And Day(DateAdd("d", 14 * 5, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 6, dVal)) = yVal And Day(DateAdd("d", 14 * 6, dVal)) = xVal) _
        Or (Month(DateAdd("d", 14 * 7, dVal)) = yVal And Day(DateAdd("d", 14 * 7, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 8, dVal)) = yVal And Day(DateAdd("d", 14 * 8, dVal)) = xVal) _
        Or (Month(DateAdd("d", 14 * 9, dVal)) = yVal And Day(DateAdd("d", 14 * 9, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 10, dVal)) = yVal And Day(DateAdd("d", 14 * 10, dVal)) = xVal) _
        Or (Month(DateAdd("d", 14 * 11, dVal)) = yVal And Day(DateAdd("d", 14 * 11, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 12, dVal)) = yVal And Day(DateAdd("d", 14 * 12, dVal)) = xVal) _
        Or (Month(DateAdd("d", 14 * 13, dVal)) = yVal And Day(DateAdd("d", 14 * 13, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 14, dVal)) = yVal And Day(DateAdd("d", 14 * 14, dVal)) = xVal) _
        Or (Month(DateAdd("d", 14 * 15, dVal)) = yVal And Day(DateAdd("d", 14 * 15, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 16, dVal)) = yVal And Day(DateAdd("d", 14 * 16, dVal)) = xVal) _
        Or (Month(DateAdd("d", 14 * 17, dVal)) = yVal And Day(DateAdd("d", 14 * 17, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 18, dVal)) = yVal And Day(DateAdd("d", 14 * 18, dVal)) = xVal) _
        Or (Month(DateAdd("d", 14 * 19, dVal)) = yVal And Day(DateAdd("d", 14 * 19, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 20, dVal)) = yVal And Day(DateAdd("d", 14 * 20, dVal)) = xVal) _
        Or (Month(DateAdd("d", 14 * 21, dVal)) = yVal And Day(DateAdd("d", 14 * 21, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 22, dVal)) = yVal And Day(DateAdd("d", 14 * 22, dVal)) = xVal) _
        Or (Month(DateAdd("d", 14 * 23, dVal)) = yVal And Day(DateAdd("d", 14 * 23, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 24, dVal)) = yVal And Day(DateAdd("d", 14 * 24, dVal)) = xVal) _
        Or (Month(DateAdd("d", 14 * 25, dVal)) = yVal And Day(DateAdd("d", 14 * 25, dVal)) = xVal) Then
        EHours = hVal
        Else:
        EHours = 0
        End If
    ElseIf iVal = "Monthly" Then
        If Month(dVal) = yVal And Day(dVal) = xVal Then
        EHours = hVal
        ElseIf (Month(DateAdd("m", 1, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 2, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) _
        Or (Month(DateAdd("m", 3, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 4, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) _
        Or (Month(DateAdd("m", 5, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 6, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) _
        Or (Month(DateAdd("m", 7, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 8, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) _
        Or (Month(DateAdd("m", 9, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 10, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) _
        Or (Month(DateAdd("m", 11, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 12, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Then
        EHours = hVal
        Else
        EHours = 0
        End If
    Else:
    EHours = "0"
    End If


End If
End Function

[Bob Phillips]

I don't think it throwing the inputbox for Auto, I think it is executing the UDF twice for the Manual cell. Not a good idea to put Inputbox in a UDF, better to get them to put the value in a cell, throw an error if Manual and no hours input.

[Aaron719]

Alright, that's too bad. It was an afterthought. I wanted to allow the user to also manually input data into the same cell the function was written in. Thank you though.