PDA

View Full Version : Input Box is popping too much.



Aaron719
01-05-2014, 12:20 PM
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
01-05-2014, 12:31 PM
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
01-05-2014, 02:55 PM
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.