Restrict entry to text onlt

[khalid79m]

With Range("Monitors_Initials")
.Validation.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1", Formula2:="5"
.Validation.IgnoreBlank = False
.Validation.ErrorTitle = "Error"
.Validation.ErrorMessage = "Maximum of 5 Characters"
.Validation.ShowError = True
.NumberFormat = "@"
.WrapText = False
.ColumnWidth = 6
End With

How can I restrict the entry to text only?

[RonMcK]

Are you defining 'text' for this purpose to be only letters in the ranges 'a..z' and 'A..Z'? No numerics even if part of an alphanumeric string? What about printable punctuation marks? (e.g. /\?><`~!@#$%^&*()_-=+|}{[];:'",.)

Thanks,

[khalid79m]

Hi Ron , in this specific scenario it is just alphabetical letters ie a to z , as the inputter is inputting there initials.

however it would be good to know how to include special charachters for future ..

can u help with both?
:friends:

[Kenneth Hobs]

I would use the worksheet Change event rather than data validation. A regular expression could be used to check for valid patterns.

e.g.
Paste this to a Module:
Function InExp(str, sPattern) As Boolean
Dim RegEx As Object, tf As Boolean
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Pattern = sPattern
tf = (.test(str))
End With
Set RegEx = Nothing
InExp = tf
End Function


Right click the sheet, View Code, and paste:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRange As Range, c As Range
Set iRange = Intersect(Target, Range("Monitors_Initials"))
If iRange Is Nothing Then Exit Sub
For Each c In iRange
Select Case True
Case Len(c) > 5
MsgBox "Maximum of 5 Characters", vbCritical, "Error"
c.Select
UndoThis
Case InExp(c, "^[a-zA-Z ]+$") = False And Not IsEmpty(c)
MsgBox "Only letters are allowed.", vbCritical, "Error"
c.Select
UndoThis
Case Else
End Select
Next c
End Sub

Private Sub UndoThis()
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End
End Sub

[RonMcK]

Kenneth,

Am I correct that I will need to re-write this for use here on my Mac since I don't believe I can use ActiveX controls.

Thanks,

[Kenneth Hobs]

I don't know but I suspect so. There are some threads on google about the Mac and RegExp.

One can also use Replace() and check the length for before and after.

[RonMcK]

Thanks, Kenneth. I'll try to look at that (time permitting) tonight after my dinner meeting.

Cheers,

[mikerickson]

Ron, I use Excel2004. This modification of Kenneth's code should work.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRange As Range, c As Range
Set iRange = Intersect(Target, Range("Monitors_Initials"))
If iRange Is Nothing Then Exit Sub
For Each c In iRange
Select Case True
Case Len(c) > 5
MsgBox "Maximum of 5 Characters", vbCritical, "Error"
c.Select
UndoThis
Case Not(AllLetters(c)) And Not IsEmpty(c)
MsgBox "Only letters are allowed.", vbCritical, "Error"
c.Select
UndoThis
Case Else
End Select
Next c
End Sub
Function AllLetters(inputString as String) as Boolean
Rem AllLetters("") = False
Dim i as long

If inputString <> vbnullString then
AllLetters = True
For i = 1 to Len(inputString)
AllLetters = AllLetters And (Mid(inputString,1,1) like "[A-Za-z]")
Next i
End If
End Function

[RonMcK]

Mike,

Doesn't the 'i' (from the For line) need to find its way into the Mid() expression? Shouldn't this:
For i = 1 To Len(inputString)
AllLetters = AllLetters And (Mid(inputString, 1, 1) Like "[A-Za-z]")
Next i
Become:
For i = 1 To Len(inputString)
AllLetters = AllLetters And (Mid(inputString, i, 1) Like "[A-Za-z]")
Next i
P.S. A big Thank You!! for showing me how to mix VBA and RegEx.

Just wondering,

[RonMcK]

Mike,

Here is the code that I got to work on my Mac (Excel 2004) after I make several corrections (wee tweaks). Thanks for your help and guidance.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRange As Range, c As Range
Set iRange = Intersect(Target, Range("Monitors_Initials"))
If iRange Is Nothing Then Exit Sub
For Each c In iRange
Select Case True
Case Len(c.Value) > 5 '// added .Value to instances of c in Case statements
MsgBox "Maximum of 5 Characters", vbCritical, "Error"
c.Select
UndoThis
Case Not AllLetters(c.Value) And Not IsEmpty(c.Value) '// added Not before AllLetters so we trap entries with numerics
MsgBox "Only letters are allowed.", vbCritical, "Error"
c.Select
UndoThis
Case Else
End Select
Next c
End Sub
Function AllLetters(inputString As String) As Boolean
Dim i As Long
If inputString <> vbNullString Then
AllLetters = True
For i = 1 To Len(inputString)
AllLetters = AllLetters And (Mid(inputString, i, 1) Like "[A-Za-z]") '// sub i for 1 in mid()
Next i
End If
End Function
Private Sub UndoThis()
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
' End '// what's this extra End for?
End Sub


Thanks,

[mikerickson]

OOPS. I was so focused on getting the Not/Or straight that I forgot about the i.

Glad it worked.
There is a path from VBA>>AppleScript>>regular expressions.
I need to investigate the second link in the chain, but folks with wider programming experience than I have might look at

http://developer.apple.com/documentation/OpenSource/Conceptual/ShellScripting/RegularExpressionsUnfettered/chapter_5_section_3.html
and
http://developer.apple.com/documentation/AppleScript/Conceptual/AppleScriptLangGuide/introduction/ASLR_intro.html

[xluser2007]

I would use the worksheet Change event rather than data validation. A regular expression could be used to check for valid patterns.

e.g.
Paste this to a Module:
Function InExp(str, sPattern) As Boolean
Dim RegEx As Object, tf As Boolean
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Pattern = sPattern
tf = (.test(str))
End With
Set RegEx = Nothing
InExp = tf
End Function

Right click the sheet, View Code, and paste:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRange As Range, c As Range
Set iRange = Intersect(Target, Range("Monitors_Initials"))
If iRange Is Nothing Then Exit Sub
For Each c In iRange
Select Case True
Case Len(c) > 5
MsgBox "Maximum of 5 Characters", vbCritical, "Error"
c.Select
UndoThis
Case InExp(c, "^[a-zA-Z ]+$") = False And Not IsEmpty(c)
MsgBox "Only letters are allowed.", vbCritical, "Error"
c.Select
UndoThis
Case Else
End Select
Next c
End Sub

Private Sub UndoThis()
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End
End Sub

Hi Kenneth/ Mike/ Ron,

I had posted a really similar question (http://www.mrexcel.com/forum/showthread.php?t=364527) to this over at MrExcel.com.

As your rigorous approach is more precisely what I'm after, I require your kind assistance in helping me with my specific query, as below:

Hi All,

In a workbook form I am building, input global named range "Section1_c" can only take the value "Y" or "N" (or a blank if the User hasn't picked a value yet).

If "Section1_c" has the value "N":

- I would like the user to be allowed to enter a 9-digit value in global named range "section1_d" i.e. only an XXXXXXXXX, where X's are single digits.

If "Section1_c" has the value "Y" or a blank:
- then I would like "section1_d" range to be reset to a blank value and not allow the user to input any value unless they change the "section1_c" back to a "N" first".

Could you please show me how to achieve this via Worksheet Event Driven code please?

The rigor of Using Reg-Exp seems Excellent as you have done above seems excellent for my purposes. If you could please help me adapt your code to my specific example would be really appreciated.

Any help appreciated.