Need ideas on how to approach name entry validation.

[frank_m]

Please review attached workbook sample for more clarity of my need.

I need a way to validate Customer name entry. Preferably without a userform.
(Will cell validation do what I have described below?)

All existing and correctly spelled unique Customer names are listed in Sheet1.

It seems to me that not allowing periods should simplify some of the headaches.
Also not allowing single or double quotes, no commas, no asterisks, no double spaces between words, no before and after spaces.

I've added buttons to navigate between (Sheet1), which is Customer Names and the (Data sheet)
and I have a button for adding a new Unique customer name to the (Sheet1) list.

As Customer names are entered in the Data sheet in column A, I would like the name to not be acepted if it does not exist in (Sheet1)
If a name does not exist the user should be instructed with a msgbox to navigate to (sheet1), create a new name record,
then go back and reattempt the entry in the Data sheet.

[mikerickson]

I haven't looked at your file, but

=IF(ISNUMBER(MATCH(X1,Sheet1!$A:$A,0)), "X1 value is in Sheet1!A:A", "its not")

[frank_m]

Hi Mike,

Thanks for your suggestion. May I ask you how may I convert that to VBA ?

Something like:
[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cname As String
Dim FoundA As String
Cname = Target.EntireRow.Cells(3).Value
FoundA =IF(ISNUMBER(MATCH(Cname,Sheet1!$A:$A,0)), "Found", "Not Found")'wromg syntax to demonstrate
If FoundA = "Not Found" Then MsgBox "The name you entered in Column 3, does not exist"
End Sub
[/vba]
Note:
Before it even checks sheet1 for the existence of the name, I need to first check for and make changes when needed, in accordance with what I will refer to as my rule for the use of periods

Rule Concept:
Words may not end with a period unless there are other periods in-between characters.
Example(1) E.F Hutton should be automatically changed to E.F. Hutton but EF. Hutton changed to EF HUTTON
and EF.Hutton to EF Hutton or E.F.Hutton E.F. Hutton

Example(2) Franks T.B.M would be changed to Franks T.B.M. but Franks TBM. would be changed to Franks TBM
and Franks T.BM changed to Franks T.B.M.

Obviously I will be adding other rules too, but If someone can show be how to do these two things, that covers the Majority of comman mistakes, plus from there, I'll be able to figure out some of the rest.

Thanks

[NashProjects]

validation is pretty straight forward...

you could try something like a nested if to say

if instr(1,dataToSearch, valuetofind,0)>0 'if its found it'll return a numeric value which relates to the character number the valuetofind is in the datato search

I.e. instr(1, "Name", "a",0) would return 2 as its the 2nd character


also note that chr(34) is the same as a speech mark...so you would have to put instr(1, datatoSearch, chr(34) ,0)

Hope that helps Frank

[frank_m]

Thanks Nash,

It does seems that using Instr will be involved, as suggested, combined with Len, Left, Mid, Right and Split, I'm guessing, but I'm getting dizzy and frustrated working on it, so for now I'm thinking I may just not allow any periods at all,
in which case I've come up with the code below, which does a decent job with my most important validation concerns.
[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'I'm actually not using the selection changed event, it's just easier for testing and demonstrating.
'instead when in edit mode, I have a shape created in the active row,
'attached to a macro that must be clicked, otherwise selection change code prevents leaving the Row.
Dim a As String

a = Target.EntireRow.Cells(1).Value

a = Replace(Replace(Replace(Replace(Replace(a, ".", ""), "'", ""), _
"*", ""), ",", ""), Space(2), Space(1))

Target.EntireRow.Cells(1).Value = UCase(Trim(a)) 'changes to UPPER case & Trims before/after spaces

If IsError(Application.Match(Target.EntireRow.Cells(1).Value, _
Worksheets(2).Range("A:A"), 0)) = True Then

MsgBox "The name in Column 1, does not exist"
End If

End Sub
[/vba]
Now I need to address the use of a single quotes. O'Reilly will look rather silly as OReilly, as my code above produces.

Once I get that solved, I'll have a useable system I can implement If I do later discover how to handle the periods, it's easy enough to go back later and fix the spelling in my (18,000 plus) records, using a tool "Mark" developed for me, (Member name GTO), in another recent thread.
http://www.vbaexpress.com/forum/showthread.php?t=40278

[]

[frank_m]

Well, I'm making a little progress. This version disallows periods, commas, asterisks and double spaces,
and allows a single quote only if it's in the 2nd position of a single word.
- example: Allows O'REILY, but not OREIL'Y, or OREILY'

Please help me with allowing a single quote in the second position in a multi word name such as:
Allow: MAC and O'REILLY (disallow--> MAC and OR'EILLY, or: MAC and OREILLY'

I'm not worried about the several other circumstances that it would miss, as it would cover the most common mstakes,
getting me to where I start the use a decent validation system.
[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim a As String

a = Trim(Target.EntireRow.Cells(1).Value)

a = Trim(Replace(Replace(Replace(Replace(a, ".", ""), "*", ""), _
",", ""), Space(2), Space(1)))

If Not InStr(a, "'") = 2 Then a = Replace(a, "'", "")

Target.EntireRow.Cells(1).Value = Trim(UCase(a))

If IsError(Application.Match(Target.EntireRow.Cells(1).Value, _
Worksheets(2).Range("A:A"), 0)) = True Then

MsgBox "The name you entered in Column 1, does not exist"

End If

End Sub

[/vba]

[frank_m]

Not an eloquent piece of code I know, but thru some googling and trial and error I pieced togeter this,
it does the minimum of what I specifically was seeking with my questions here.

I think I'm going to be able to get by now, pretty well until I get some additional hints. (hint, hint )
[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As String, a As String, b As String, c As String
x = Trim(Target.EntireRow.Cells(1).Value)

x = Trim(Replace(Replace(Replace(Replace(Replace(Replace(x, ".", ""), "*", ""), _
",", ""), Space(2), Space(1)), Space(2), Space(1)), Space(2), Space(1)))
'above looks odd I know, but a series of reducing 2 spaces to 1
'was the only way I saw to reduce 2, 3 & 4 spaces,
'and combinations, all to become 1, before the Split function seperates on single spaces

'On Error Resume Next
a = Trim(ExtractElement(x, 1, " "))

b = Trim(ExtractElement(x, 2, " "))

c = Trim(ExtractElement(x, 3, " "))

If Not InStr(a, "'") = 2 Then a = Replace(a, "'", "")

If Not InStr(b, "'") = 2 Then b = Replace(b, "'", "")

If Not InStr(c, "'") = 2 Then c = Replace(c, "'", "")
'On Error GoTo 0
'MsgBox a & " " & b & " " & c
'exit sub
Target.EntireRow.Cells(1).Value = Trim(UCase(a & " " & b & " " & c))

If IsError(Application.Match(Target.EntireRow.Cells(1).Value, _
Worksheets(2).Range("A:A"), 0)) = True Then
MsgBox "The name you entered in Column 1, does not exist"

End If

End Sub

Private Function ExtractElement(str, n, sepChar)
'http://spreadsheetpage.com/index.php/tip/the_versatile_split_function/
'Returns the nth element from a string,
'using a specified separator character
Dim x As Variant

x = Split(str, sepChar)

If n > 0 And n - 1 <= UBound(x) Then
ExtractElement = x(n - 1)

Else

ExtractElement = ""
End If

End Function
[/vba]

[shrivallabha]

You have already done the hard part. Listing down the names of companies. Take a look at named ranges option.

1. On Sheet1, Range("A2:A84") is named as CUSTOMER_NAME.
2. And then on Data Entry Sheet Range("A4") >> Data Validation >> List >> =CUSTOMER_NAME
3. You can customize message as per your requirements.
4. The validation part can be extended by copying the cell having validation and then Paste >> PasteSpecial >> Values.

On VBA part, I feel Worksheet_Change event [That too limited by If Target.Column = 1] is better suited than SelectionChange as this will slowdown the sheet maneuvering considerably.

[frank_m]

Hi shrivallabha,

Thanks for your ideas and for your time to put together an example.
As for using the change event, I agree with you, it's just that I'm lame with my knowledge of how to use it with the code I have now.
- For instance, if a name is misspelled, the msgbox goes into an infinite loop when trying to move to another cell.

Learning to use it with the change event, would only be out of curiosity at this point; as I'm not actually using the selection change event.. Just used it with my sample here, so that I did not have to add all the shape related and protection structure.
- The small shape I use, resides within the row being edited. It is assigned to a macro that makes corrections and gives validation warnings if necessary.. It is only clicked once, when all of the data entry for that row is complete..

I like the validation example you set up, but how can I first remove unwanted characters before cell validation checks against the customers list?
With my system, the user can make mistakes and my validation button shape corrects the common mistakes before checking against the Customer list. - Contrary to the cell validation that in my opinion is slower if the user often makes mistakes, or often needs to scroll through the dropdown.

[mikerickson]

You might want to use Data Validation feature of Excel. No VBA.
Define the Name

Name: ExistingNames
RefersTo: =INDEX(Sheet1!$A:$A,2,1):INDEX(Sheet1!$A:$A,MATCH("zzzz",Sheet1!$A:$A),1)

and then set Validation to List type with the source =ExistingNames

VBA is not needed.

[frank_m]

Hi shrivallabha,

Your sample workbook is functioning well, but I'm lost.
When I try to set validation to refer to my defined name in my actual workbook, I get an error that I cannot refer to a different sheet.
The sheet name that my defined name refers to is "Customer Names". The sheet code name is Sheet6. My Data Entry sheets code name is Sheet2

I appreciate your time in creating the sample and giving advise.
-----------------------
Edit: Sorry Mike and others, I edited in more questions while you were posting an answer. I will check out your workbook now - Thanks

Hi Mike

I'm very appreciative of your time and help as well.

I do not understand your directions, would you be so kind as to clarify my questions below?
I think you wrote that in the RefersTo: box in the defined names, I should enter =INDEX(Sheet1!$A:$A,2,1):INDEX(Sheet1!$A:$A,MATCH("zzzz",Sheet1!$A:$A),1)

Or, am I misunderstanding something ?

If the above formula is correct, what should I put in place of "zzzz" ?

Also:
Is there a way that I can change $A:$A to $A16:$A & LastRow?

Also, I want to add the Validation using VBA, so that it only is there if my other code determines that the name does not exist after my code induced spelling corrections are performed. How do I use the defined name in place of xlBetween, Formula1:="=A:A"
-or- if I cannot do that, is there a way to change ="=A:A" to something like :="=A:A" & LastRow ?
[vba],
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=A:A"
.IgnoreBlank = True
.InCellDropdown = True
.ErrorMessage = "No Match Found"
.ShowError = True
End With
[/vba]

[mikerickson]

The attached file has the validation set on DataEntry!A4:A100.

[frank_m]

Thanks a million Mike

Your sample workbook allowed me to figure out all the answers to my questions. (well almost all, as the use of zzzz in the defined name refers to formula, still confuse me, but all I care about right now is that it works

I used the macro recording with the information I gained from your workbooked to determine that I can use the defined name like this in VBA:
[vba]
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="CustomerList"
[/vba]
Edit: I just determined that the defined name range does in fact increase, when I insert new Customer Names to Sheet1. (Awesome)

[mikerickson]

Match(someValue, someRange)
Looks down oneRange and seeks the first cell such that every cell after it is either > someValue or blank.

"zzzz" is shorthand for REPT("z",255) which is the highest string that a cell can hold.

MATCH(REPT("z",255), A:A) returns the row number of the last text entry in column A.

(MATCH("zzzz", A:A) is easier to type.)