Input Box

[Abvlecxe]

Hi looking for help with my input box macro below, I am looking to have the macro only allow certain numbers e.g. 10,20,30,40,50 etc but this doesn't work:

Sub runQsheet()
Dim myValue As Variant
Do
myValue = InputBox("Enter Fiscal Week Number", "Report Refresh", 1)
'cancel pressed
If StrPtr(myValue) = 0 Then Exit Sub
'inform user
If Not IsNumeric(myValue) Then MsgBox "Numeric Values Only" & Chr(10) & _
"Multiples of 10"
Loop Until Val(myValue) = 10,20,30,40,50

Range("B1").Value = CLng(myValue)

 

[mumps]

You don't need a macro to limit entry into cell B1. You can use data validation. Select cell B1 and in Data Validation, select "Custom" in the 'Allow' box and in the 'Formula' box, enter this formula: =MOD(B1,10)=0

 

[Abvlecxe]

Hi thanks for response.

The input result i am actually using for another peace of code but i need to restrict the user selection to multiples of 10, do you know the code for this?

I have since googled and have found that if i put in
Loop Until Val(myValue) = 10 or my value = 20 or my value = 30
But the range i have goes up to 500 so this can't be the most efficient way to do this

Thanks

You don't need a macro to limit entry into cell B1. You can use data validation. Select cell B1 and in Data Validation, select "Custom" in the 'Allow' box and in the 'Formula' box, enter this formula: =MOD(B1,10)=0

 

[craigexcel]

Following on from mumps' suggestion, you could use:
=OR(B1<=500,MOD(B1,10)=0)

 

[mumps]

If you are saying that you want to validate the input in the range B1:B500, select that range and use this formula: =AND(B1<=500,MOD(B1,10)=0) (slighty modified from craigexcel's suggestion)

 

[craigexcel]

Thanks for seeing that correction to be made. Definitely AND, not OR.

 

[mumps]

@craigexcel: No problem. Been there, done that!!