"If" Statement Not Running Even When Statement is True

[ashperson95]

Hello all,

I never thought I would have so much trouble trying to do simple user error checking. I have a little block of code that asks for a user input, and then it checks to see if the input is valid (numeric and between 1 and 7). The if statement runs when the entry is not numeric, but if the value is greater than 7 or less than 1, the if statement does not run.

Dim hoursWeek As Variant

hoursWeek = Application.InputBox("How many days a week does this client operate their lights?", "Days Per Week")


If hoursWeek > 7 Or hoursWeek < 1 Or (Not IsNumeric(hoursWeek)) Then
Do Until IsNumeric(hoursWeek) And (1 <= hoursWeek <= 7)
hoursWeek = Application.InputBox("Sorry, that's not a valid entry. How many days a week does this client operate their lights?", "Days Per Week")
Loop
End If

I have tried changing the hoursWeek variable in the If statement to Val(hoursWeek.Text), CInt(hoursWeek), and hoursWeek.Value. I've also tried declaring hoursWeek as a String and using CInt(hoursWeek) in the If statement, but none of these have worked.

What is it that I'm doing wrong?

 

[Gavin T]

The input box you're using return a string. Use the type "1" to only allow numeric entries.

    Dim hoursWeek As Variant

    hoursWeek = Application.InputBox("How many days a week does this client operate their lights?", "Days Per Week", Type:=1)

    If hoursWeek > 7 Or hoursWeek < 1 Then
        Do Until hoursWeek <= 7 And hoursWeek >= 1
            hoursWeek = Application.InputBox("Sorry, that's not a valid entry. How many days a week does this client operate their lights?", "Days Per Week", Type:=1)
        Loop
    End If

 

[ashperson95]

Ah, that did the trick! Thank you!

Also, I have another thread on a different topic that has yet to be answered (link is below). Would you mind taking a look at that one to see if you could help?

http://www.mrexcel.com/forum/excel-questions/780343-autofilling-table-has-changing-position.html