MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Validating Inputbox Entry


February 07, 2002 - by Juan Pablo Gonzalez

David asks:

Hi I have to design a project and i'm stuck on a bit which is designing a macro to allow the user to input a number, but the problem i'm facing is setting a validation which would only accept numbers between 1-10. I tried to set a validation on the specific cells but the problem with the inputbox is that even if the cell has been set to only accept a number it would accept anything. Can you please help because i can't really carry on with my project until this is done.

This macro will continue to ask the user for a number between 1 and 10 or will stop if he/she cancels.

Sub Test()
Dim MyAns As Variant

	Ask:
	MyAns = Application.InputBox("Enter a number between 1 and 10", Type:=1)

	If MyAns = False Then Exit Sub
	
	If MyAns < 1 Or MyAns > 10 Then GoTo Ask:
	
	MsgBox MyAns, vbInformation, "Good Number"
End Sub