vba- loop for only numbers in inputbox

[shmouel99]

hello
i want to create inputbox that the excel user can only type only numbers and must put numbers (8 numbers like 12345678 or 22233670 and so on)
could you shoe a good code for this?

 

[Firefly2012]

Hi

If you use Application.InputBox method rather than the Inputbox function, then you can restrict the return type to numbers only. You will still need to validate the number that is typed in, however (eg to be 8 digits without fractional part etc):

Dim myNum As Long
 
myNum = Application.InputBox("Enter your number",Type:=1)

 

[shmouel99]

for diffrenet reason i want to use loop . i want to enter a message that if this is not number the user will get message that:

msgbox "please enter only numbers"


so how to do it?
and i didnt understant how to limit it to 8 digits?

 

[Firefly2012]

Then you need to use a textbox on a userform whereby you can utilise the textbox's Change event to restrict to no more than 8 characters and also to numeric digit entry only.

 

[shmouel99]

thanks , but agaih- how to restrict the textbox on the form to only numbers and 8 numbers?

 

[Firefly2012]

Well, assuming you have your textbox on your userform and it is named TextBox1 (or amend the code to suit), you could use the following event code to restrict entry to digits only and to limit number of characters to no more than 8:

Private Sub TextBox1_Change()
With Me.TextBox1
    'check if 8 characters exceeded:
    If Len(.Text) > 8 Then
        .Text = Left(.Text, 8)
        Exit Sub
    End If
End With
End Sub
 
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii < 48 Or KeyAscii > 57 Then KeyAscii = 0
End Sub