I am creating a form for hiring cutlery for my local village hall (a
charity). I need to ensure that hirers only order cutlery in multiples of 10.
How can I validate their input so that if they enter eg 33 it is
automatically rounded up to 40?
Thanks

Ray

=CEILING(A1,10)
--
David Biddulph

"RayC" wrote in message
...
I am creating a form for hiring cutlery for my local village hall (a
charity). I need to ensure that hirers only order cutlery in multiples of
10.
How can I validate their input so that if they enter eg 33 it is
automatically rounded up to 40?
Thanks

Ray

Thanks, but how do I apply it to the same cell to avoid a circular reference?
If the user tried to enter 33 I want that cell to show the 40.

--
Ray


"David Biddulph" wrote:

=CEILING(A1,10)
--
David Biddulph

"RayC" wrote in message
...
I am creating a form for hiring cutlery for my local village hall (a
charity). I need to ensure that hirers only order cutlery in multiples of
10.
How can I validate their input so that if they enter eg 33 it is
automatically rounded up to 40?
Thanks

Ray

Hi Ray,

Bit of a compromise on what you are asking for, but you could
set Data Validation to custom with a formula like this,
=OR(A1=10,A1=20,A1=30,A1=40,A1=50)
and then set the error alert message to say
"Input must be in multiples of 10"

HTH
Martin



"RayC" wrote in message
...
Thanks, but how do I apply it to the same cell to avoid a circular
reference?
If the user tried to enter 33 I want that cell to show the 40.

--
Ray


"David Biddulph" wrote:

=CEILING(A1,10)
--
David Biddulph

"RayC" wrote in message
...
I am creating a form for hiring cutlery for my local village hall (a
charity). I need to ensure that hirers only order cutlery in multiples
of
10.
How can I validate their input so that if they enter eg 33 it is
automatically rounded up to 40?
Thanks

Ray

To ensure, in Data Validation, that the number is a multiple of 10: If
you are working with, say, cell A1, in DV choose Custom and this
formula:

=MOD(A1,10)=0

Now, to automatically round up the entered number to the nearest
multiple of 10 you need a VBA event procedu

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then ' Change A1 to suit
If Target.Value Mod 10 < 0 Then
ceil = Application.WorksheetFunction.Ceiling(Target.Value , 10)
msg = "You should enter a number in multiple of 10" & vbCrLf
msg = msg & "Automatically increasing your amount to " & ceil
MsgBox msg
Target.Value = ceil
End If
End If
End Sub

Right-click the sheet tab. Choose View Code... The VBA IDE comes up.
Paste the above code to the window, after changing $A$1 to whatever
cell contains the input.

Does this help?
Kostis Vezerides

On Nov 23, 11:51 am, RayC wrote:
I am creating a form for hiring cutlery for my local village hall (a
charity). I need to ensure that hirers only order cutlery in multiples of 10.
How can I validate their input so that if they enter eg 33 it is
automatically rounded up to 40?
Thanks

Ray

Thanks Kostis, the MOD part works great, I don't need the complex VBA bit for
my simple application. Very much appreciated.
--
Ray


"vezerid" wrote:

To ensure, in Data Validation, that the number is a multiple of 10: If
you are working with, say, cell A1, in DV choose Custom and this
formula:

=MOD(A1,10)=0

Now, to automatically round up the entered number to the nearest
multiple of 10 you need a VBA event procedu

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then ' Change A1 to suit
If Target.Value Mod 10 < 0 Then
ceil = Application.WorksheetFunction.Ceiling(Target.Value , 10)
msg = "You should enter a number in multiple of 10" & vbCrLf
msg = msg & "Automatically increasing your amount to " & ceil
MsgBox msg
Target.Value = ceil
End If
End If
End Sub

Right-click the sheet tab. Choose View Code... The VBA IDE comes up.
Paste the above code to the window, after changing $A$1 to whatever
cell contains the input.

Does this help?
Kostis Vezerides

On Nov 23, 11:51 am, RayC wrote:
I am creating a form for hiring cutlery for my local village hall (a
charity). I need to ensure that hirers only order cutlery in multiples of 10.
How can I validate their input so that if they enter eg 33 it is
automatically rounded up to 40?
Thanks

Ray

Ray,
thanks for the feedback.

Regards

On Nov 23, 9:14 pm, RayC wrote:
Thanks Kostis, the MOD part works great, I don't need the complex VBA bit for
my simple application. Very much appreciated.
--
Ray

"vezerid" wrote:
To ensure, in Data Validation, that the number is a multiple of 10: If
you are working with, say, cell A1, in DV choose Custom and this
formula:

=MOD(A1,10)=0

Now, to automatically round up the entered number to the nearest
multiple of 10 you need a VBA event procedu

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then ' Change A1 to suit
If Target.Value Mod 10 < 0 Then
ceil = Application.WorksheetFunction.Ceiling(Target.Value , 10)
msg = "You should enter a number in multiple of 10" & vbCrLf
msg = msg & "Automatically increasing your amount to " & ceil
MsgBox msg
Target.Value = ceil
End If
End If
End Sub

Right-click the sheet tab. Choose View Code... The VBA IDE comes up.
Paste the above code to the window, after changing $A$1 to whatever
cell contains the input.

Does this help?
Kostis Vezerides

On Nov 23, 11:51 am, RayC wrote:
I am creating a form for hiring cutlery for my local village hall (a
charity). I need to ensure that hirers only order cutlery in multiples of 10.
How can I validate their input so that if they enter eg 33 it is
automatically rounded up to 40?
Thanks

Ray