Prevent users from entering alphabet!

**se7en_hills** · 10-24-2008, 06:26 AM

Hi,

I have a doubt, how could i prevent the users from entering a alphabet in a cell ,like people do in java script.

example : there is a field called Quantity, since the quantity field should contain only numbers, i have done this by using data validation (i.e) the user can enter alphabets and it shows the error only after the user enters it.
but i dont want anything to be entered except numbers.

Please help!!!

Thanks

**Stuie** · 10-24-2008, 06:34 AM

Hi se7en_hills,

As far as i know you cant do that but you could make it so that it will take it back out as soon as the user changes the value in a cell eg.

if i put the letter A in cell E10 i could then use a piece of code to take it out once i have entered it. would that help at all

**chergh** · 10-24-2008, 07:00 AM

You could do this with the onkey method

If you put the following in the worksheet change event:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Application.OnKey "a", ""
Application.OnKey "A", ""
Application.OnKey "b", ""
Application.OnKey "B", ""
Application.OnKey "c", ""
Application.OnKey "C", ""
'etc


End Sub

This isn't 100% as they can still double click on a cell or start with a number and then put alphabetic characters in but it stops them from just starting to type text into a cell.

**Stuie** · 10-24-2008, 07:16 AM

wouldnt that stop them from using them anywhere in that workbook or worksheet

**chergh** · 10-24-2008, 07:21 AM

It would in its current form but you could restrict it to a particular range easily enough. Probably also want to tag on a isnumeric(target.value) to make sure the final output is a number as well.

**DonkeyOte** · 10-24-2008, 07:29 AM

This would seem to be a x-post ...

http://www.mrexcel.com/forum/showthread.php?t=349064

**royUK** · 10-24-2008, 07:41 AM

se7en-hills- please do not cross post without adding a link

Why not use Data validation.

**DonkeyOte** · 10-24-2008, 07:44 AM

royuk, I believe (interpret) OP to be using validation already -- I think perhaps there is just confusion as to whether or not an invalid entry is "committed".

**royUK** · 10-24-2008, 08:45 AM

I can't see what the problem is, if the user enters a non numeric number, data validation will allow the user to retry, or delete the entry if Cancel is selected

**DonkeyOte** · 10-24-2008, 08:50 AM

Yes, exactly.

**jasoncw** · 10-24-2008, 09:53 AM

Maybe he has the error alert style set to Warning or Info, rather than Stop, allowing the use of alpha characters after overriding the warning.

**okriskao** · 10-24-2008, 11:56 AM

Hi I dont know if this will help but i search online before for my own project but this code applies to textbox... maybe you can change it a little? if it doesn't help im sorry!

Private Sub Textbox1_Change()

    OnlyNumbers

End Sub


Private Sub OnlyNumbers()

    If TypeName(Me.ActiveControl) = "TextBox" Then

        With Me.ActiveControl

            If Not IsNumeric(.Value) And .Value <> vbNullString Then

                MsgBox "Please enter a valid number."

                .Value = vbNullString

            End If

        End With

    End If

    
End Sub

**se7en_hills** · 10-24-2008, 01:59 PM

Hi,
Thanks for ur replies. i'm sorry for the cross post. i'm new and i dont know whether it comes under the rules. can't i post it in more than one forums???

@DonkeyOte royUK - I have used Data Validation, what u said was exactly correct i.e. the user can't enter alphabets and error message will be shown. But this doesn't restrict the user from copying and pasting.
Thats y i went for asking like , it shouln't allow the alpha itself.

expecting a solution here

Thanks

**DonkeyOte** · 10-24-2008, 02:06 PM

see a similar thread which in turn has a link to John Walkenbach's solution:

http://www.excelforum.com/excel-misc...alidation.html

this will require VBA to handle the value change though

**VBA Noob** · 10-24-2008, 02:27 PM

Or maybe this change event macro

Right click sheet tab and select view code. Paste this in.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = myClean(Target.Value)

End Sub

Then add this to a normal module

Function myClean(txt As String)
With CreateObject("VBScript.RegExp")
    .Pattern = "[a-zA-Z]"
    .Global = True
    myClean = Trim(.Replace(txt, ""))
End With
End Function

Link explains more about VBScript with Regular Expressions
http://msdn.microsoft.com/en-us/library/ms974570.aspx

VBA Noob